AutoNumber skips 36,000,000 records

  • Thread starter Thread starter Roach
  • Start date Start date
R

Roach

I didn't see quite this problem in a NG search, so sorry if it is a
repeat.

I inherited a table with 8144 records sequentially numbered in the
auto-number field which is assigned the primary key. Recently, while
adding data, I noticed that new records have skipped to 3614193 and
then resume the sequence 3614194, 3614195, etc. This table has never
had over about 8150 records and the random/increment New Values choice
in table design is set to increment. I do not have this column linked,
and everything else is functioning properly. I'm wondering though, is
this indicative of a larger problem? Any harm done? Thank you for your
thoughts.
 
Access 2000?

There was a bug in an early release of JET 4 that caused it to jump to a
wild number, even negative numbers, and in some cases attempt to assign
duplicates (which failed if the field was primary key, but actually did
generate duplicates if it was not.) The bug was fixed many service packs
back.

Locate the file msjet40.dll on your computer (typically in
windows\system32).
Right-click, and choose Properties.
On the Version tab, you should see:
4.0.8xxx.0
If you don't see the 8, then download SP8 for Jet 4 from:
http://support.microsoft.com/kb/239114
If your SP is less than 7, this is really important: there are many crucial
issues that have been addressed in these service packs.

After applying the service pack, compacting the database should cause it to
continue from the next highest number. That means that you have the choice
to continue with the records that currenty have a number in the 36 millions,
or to delete them, compact, and re-enter them. There is no real reason why
the high numbers will cause any problem: autonumbers cope with values up to
2 billion.

If you find that the nubmers do not reset correctly after a delete and
compact, you can use the code in this link to reset the autonumbers for all
local non-system tables in your database:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
 
Thanks for your kind reply. I have Access 2002 build 10.6501.6714 SP3,
and my dll version is current at 4.00.8618.00. Should I be concerned?
Thanks for any advice.
 
That's the latest patches, so you should not be subject to that bug.

We are assuming here that there is no valid reason for the skipped numbers,
such as a routine that is often appending records and removing them or
rolling back a transaction.

Guess it could be a corruption, or something that has set the Seed property
of the AutoNumber column. It is possible (with ADOX code) to set this value.
It is also possible to reset it using ADOX code, and that's what the article
does.

If you are concerned:
1. Make a backup of the file while Access is not running.
2. Make sure Name AutoCorrect is off.
3. Compact the database.
4. Decompile the database.
5. Compact again.
6. Run the code in the web page.
7. Compact again.
 
Back
Top