Problem with startup MS Access

Question

My client accidentally deleted about 500 entries from the access table, which has a primary identifier field that was created as an "autonomous". By turning off the autonumber column (changing back to an integer), I was able to restore the missing 500 entries from the backup, but now, of course, autorun cannot be turned on ...

What are the possible solutions? The ID field is used as a reference to other tables, so I can’t just renumber everything without renumbering all the tables that reference this number (neck pain, but possible).

Is there any "trick" to enable autodial using max (id) as a starting point if the data already exists in the table?

Answer 1

Make newTable with the ID field as AutoNumber (all fields should be the same as in the source table, except for the ID). Copy all data from originalTable to newTable:

INSERT INTO newTable SELECT * FROM originalTable 

Once the data is full, delete the originalTable and rename newTable to originalTable.

Thus, all the "holes" in the automatic numbering are saved, and the newTable function has the Auto-Numbering function.

PS Always try to add foreign keys to your identifiers. In this case, even if some data is deleted, you will at least have a consistent state.

Answer 2

If I could add to the answers.

A little-known fact about Access autonumber fields is that the counter on them is reset when compressing and restoring the database.

I'm also sure that if you make an insert, it will use the number that will be served, and not the next number in the auto-dial counter, if it is (larger) than the internal counter stored in the automatic number field (does this make sense at all?)

In other words, you can do something similar in a completely new access table, where the counter should be set to 1 ...

 INSERT INTO myTable (myAutoNumber,myOtherField) VALUES (10000,'other data') 

The other solutions mentioned here are better because they will better fulfill the guarantee of the result, so I mention it for almost academic reasons.

Set

Answer 3

The ideal solution, although it was already too late, was to restore the missing 500 entries to the desktop. Then run the Append query into the main table. This would include the Autonumber field.

Answer 4

Agree, but you can add ORDER BY to make sure AutoNumber is in the correct order. Otherwise, your other tables will have the wrong identification association.

 INSERT INTO newTable SELECT * FROM originalTable ORDER BY ID 

You will also need to explicitly specify fields instead of using *

Answer 5

You create a new field and make it an automatic number, and then delete the id field and rename the new field to id