Autonumber field skipping

[Marva Frederick]

After running an archive routine whcih also involves
compacting the database at the end of the archiving
process, the autonumbering field in one of the tables gets
screwed up. On insertion of a new record the autonumber
field on a table will jum immensely, e.g., 9433 to 14567832
or some arbirary huge number. Can anyone tell me what
might be the cause of this?

 

[Allen Browne]

You did not mention your version of Access, but 2000 and 2002 have been
subject to weird problems with AutoNumbers. If you are using either of these
versions, go to support.microsoft.com and download and see if you have the
latest available service pack for your version. (Help | About gives your
version and service pack.)

You may also want to ensure that you have JET 4 SP7, as this addresses these
issues. Locate the file msjet40.dll on your system (typically in
windows\system32). Right-click and check the version number. You should see
4.0.7328. If you have anything less, download Jet 4 SP7.

If you have not kept the records with the crazy autonumbers, there is a new
article at:
http://allenbrowne.com/ser-40.html
titled:
Fixing AutoNumbers when Access assigns negatives or duplicates
It illustrates how you can reset the Seed of the AutoNumber Column in your
tables.