Recovering from an AutoNumber Disaster

Published on
3,519 Points
Last Modified:
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.

AutoNumber Recovery.mdb

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:

  1. Create a new table with the same structure as the problem table, by copying it and pasting it "Structure Only."
  2. Add an OldID field to the new table (Long data type), to hold the old AutoNumber values.
  3. 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.
  4. 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.

1 Comment
LVL 31

Author Comment

by:Helen Feddema
Will do.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month