MS Access autonumber problem

Question

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

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

Is there any "trick" to turning autonumber back on, using the max(id) as the starting point if data already exists in the table?

Answer 1

Make newTable with ID field as AutoNumber (all fields must be same as in original table - except ID). Copy all data from originalTable to newTable:

INSERT INTO newTable SELECT * FROM originalTable

Once data is filled, delete originalTable and rename newTable to originalTable.

This way all "holes" in auto-numbering are preserved and newTable has Auto-Numbering turned on.

P.S. Always try to add foreign keys to your IDs. In that case, even if some data is deleted, you will at least have consistent state.

Answer 2

The ideal solution, although it's now too late, wouuld've been to restore the missing 500 records into a working table. Then do an Append query into the main table. This would've included the Autonumber field.

Answer 3

If I could add to the answers given.

A little known fact about Access autonumber fields is that the counter on them is reset when you compact and repair the database.

I am also pretty sure that if you do an insert it WILL use the numeric that is supplied rather than the next number in the autonumber counter as long as it is > (greater than) the internal counter kept by the auto-number field (does that make sense at all?)

In other words you CAN do something like this in an brand 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 would do a better job of guaranteering the result so I mention it almost for academic reasons.

Seth