autonumber weirdness--help

[Xenophobe]

I have a table with a unique autonumber ID column. The table contains about
1000 records. Recently the autonumber column value got changed to something
less than the highest value. This generates a runtime error when attempting
to insert a new record because the ID column value must be unique.

How could this have happened? Any comments or insight would be greatly
appreciated.

Thanks.

 

[Van T. Dinh]

Apply latest Service Packs into your Access/JET installation.

See the Microsoft Knowledge Base:

http://support.microsoft.com/?id=257408

 

[Xenophobe]

Joseph,

Thanks for your reply.

The autonumbers were indeed duplicates. It was trying to insert numbers
starting with 321. The errors would continue with 322, 323 and 324. All
already exist. Success was finally achieved with 325, which didn't exist
(available mostly likely from an earlier deletion.) Bottom line is the
numbers don't need to be consecutive, just unique.

I believe the problem might be solved with the service pack indicated in
Van's message. However, I'm going to adjust the current number to something
above the highest number just to be safe.

 

[Xenophobe]

Van,

Thanks for the heads-up!

Apply latest Service Packs into your Access/JET installation.

See the Microsoft Knowledge Base:

http://support.microsoft.com/?id=257408

 

[Xenophobe]

Joseph,

I installed the Jet service pack, but unfortunately that didn't fix the
current problem. I went ahead and changed the autonumber value (using a
procedure found elsewhere) and now new records are inserted successfully.

Hopefully the service pack will prevent any future related problems.