Autonumber not unique!

[Seth Darr]

Well this is the screwiest thing ever. I have a table with a
Autonumber'd primary key. When I try to insert a record into it I get a
key violation. So by going to the datasheet view and starting to enter
a new record I discover that it's REUSING a value already in the table!
So of course I do the first impulse- Compact and Repair after deleting
the contents out of the table. Then I reinsert the records via a DTS
job in SQL Server. It restarted the autonumber to 1, and went through
81011 (though it's inserting 78554 records, so I'm not sure where it's
skipping in sequence). But when I tried it again, it tried to assign a
value of 80813, which was already being used, hence another key
violation. Already checked out the Jet service pack, and I'm up to date
apparently (i tried to install it, and it told me i had a newer service
pack already installed. http://support.microsoft.com/kb/q257408/). Any
ideas?

-Seth

 

[Allen Browne]

Using ADOX, you can fix the problem by resetting the Seed of the
Autoincrement Column in the Table of the Catalog.

See:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
The page contains a function that searches all your tables, reports any that
have an AutoNumber problem, and sets the Seed (after your confirmation.)

 

[Seth Darr]

Using ADOX, you can fix the problem by resetting the Seed of the
Autoincrement Column in the Table of the Catalog.

See:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
The page contains a function that searches all your tables, reports any that
have an AutoNumber problem, and sets the Seed (after your confirmation.)

Worked beautifully. Thanks heaps!