compact & repair resetting autonumber incorrectly (1 Viewer)

[Talia Young]

I have a table with an autonumber field as a unique identifier. I recently ran Compact & Repair, which, as I understand it, resets the autonumber to one greater than the highest autonumber already in the table. I have run Compact & Repair often in the past without difficulty, but this time, it appears to have reset the autonumber to something lower than the highest pre-existing autonumber in the table. (When I create new records, it starts at autonumber 5677, but the highest number is 5688.) The result is that I am not allowed to add any new records to the table because they duplicate the primary key.

This is not as much a road block as a series of speed bumps - I can keep adding records and causing errors until the autonumber is back up to where it should be - but I wanted to know if anyone had any tips on how to avoid this problem in the future.

 

[The_Doc_Man]

I don't have an idea that explains this, but I have a question that might shed enough light for someone else to have the proverbial light bulb pop up over their head...

When this happens, had you recently deleted records in the same range that your Autonumber resets to after the next compaction?

I.e. are there autonumber gaps in your table?