Has anyone else run into this?
In Access 2010, after Compact/Repair on an Access2007 database (.accdb), some tables’ autonumber seeds get reset incorrectly. Then when the user tries to enter new data, they get a duplicate value error.
Allen Browne has a wonderful routine to correct the seed (http://allenbrowne.com/ser-40.html), but that is a temporary fix until the next compact.
The problem seems to only occur in tables with a unique autonumber field where the primary key is not the autonumber. In those tables, the primary key is a combination of 2 fields, and the autonumber value would be used as foreign key in another table.
I don’t have this problem when the database is compacted in Access 2007.