AutoNumbers should increment automatically, without duplicates. But sometimes something goes wrong, and the next AutoNumber value is a duplicate. This article shows how to recover from this problem.
A standard AutoNumber type field (with its NewValue property set to Increment) should start at 1 (though it is possible to start with a higher number), and each new record should be assigned the next higher number, so the AutoNumber type ID field should have values of 1, 2, 3 and so on, possibly with some gaps because of deleted records. Each record should have a unique AutoNumber ID value – that is, after all, one of the points of using an AutoNumber field as a table's key field. But sometimes the process of creating new, incremented, unique AutoNumber values fails. I have seen this happen several times over the many years I have been working with Access.
I do not know what causes AutoNumbers to fail, but when it occurs, the symptom is that when a new record is created in the table with the problem AutoNumber field, it reuses an ID value that was already used in a previous record, which of course results in an error message about the key value not being unique when the new record is saved.
The sample database contains a table (tblContactsProblem) with a broken ContactID AutoNumber field, which needs to be recreated. To see the problem, open tblContactsProblem in datasheet view, scroll down to the bottom, and start entering a new record. You will see that instead of the next number in sequence, which would be 3841, another number (3304) appears in the ContactID field. This number has already been used (you can scroll up in the table to see its record), so you will get an error on tabbing away from the new record, and you won't be able to save it.
If you are lucky, there may be a quick fix for this problem: make a copy of the problem table, and check whether it increments AutoNumbers correctly. If it does, you can delete or rename the old table, and give the copy the same name as the original table. But if this doesn't work, the recovery process is more complex. To get your AutoNumbers to increment and be unique as they should be, you need to:
- Create a new table with the same structure as the problem table, by copying it and pasting it "Structure Only."
- Add an OldID field to the new table (Long data type), to hold the old AutoNumber values.
- Run an append query to fill the new table, writing the old ID value (from the problem AutoNumber ID field) to the OldID field in the new table, and letting Access create new AutoNumber ID values for all records.
- Run one or more update queries to update the foreign key ID values linked to the new main data table.
The #3 append query is shown below, in Design view:
There are two other tables linked to tblContacts by the ID field, tblContactAddresses and tblContactLetters. The update query that replaces old ContactID values with new ones from tblContactsNew is shown below (note that the link is between OldContactID in tblContactsNew and ContactID in tblContactAddresses):
The other linked table, tblContactLetters, can be updated similarly. After running the queries, now tblContactsNew increments its new ContactID AutoNumber field correctly – though possibly with a gap in numbering, as shown below.
After verifying that the AutoNumber field in the new table works as it should, you can rename tblContactsNew to tblContacts; you may wish to leave the OldContactID field in the new table, for reference purposes, for example when checking the ContactID value on printed documents.