How to recover from an AutoNumber disaster.
Access Archon #165
Introduction
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 value – that is, after all, one of the points of using an AutoNumber field as a table’s key field. But, as I was reminded by an Access Archon reader, Wesley Ingram, sometimes the process of creating new, incremented, unique AutoNumber values fails. I have seen this happen several times over the 14 or so years I have been working with Access. This article describes how you can recover from an AutoNumber disaster.
I do not know what causes AutoNumbers to fail, but when I have seen this problem, 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 (I replaced confidential data in the name fields with sample data, and deleted the other fields). 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 (3300) appears in the ContactID field, as shown in Figure C. 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.
Figure C. A previously used (and thus not unique) value in an AutoNumber field
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, 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 new OldID field, 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 in Figure D, in Design view:
Figure D. An append query that fills the new table with old ContactID (and other) values
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 in Figure E (note that the link is between OldContactID in tblContactsNew and ContactID in tblContactAddresses):
Figure E. An update query that replaces old ContactID values with new ones in a linked table
The other linked table, tblContactLetters, can be updated similarly. After running the queries, now tblContactsNew increments its new ContactID AutoNumber field correctly, as shown in Figure F.
Figure F. The tblContactsNew table, with a working AutoNumber field
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.
References
The sample database does not need any special references.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch165.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
AutoNumber Recovery (AA 165).mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
