Compact and Repair in Access 2010 resets autonumbers resulting in duplicate records.

[mguilbeault]

I recently created problems in an Access 2010 application by compacting and repairing the backend database. We started getting error messages that the application couldn't add a new record because of duplicate keys. I've been told by a developer who has encountered this probem that Access 2010 resets autonumber fields if a database is compacted and there's no fix for this bug. I have a client with a very large, complex Access 2007 application and database who wants to upgrade to 2010. All the data tables use autonumbers for table keys. I've researched this problem and there are many postings about problems with autonumbers after compacting and repairing a database, but most of these article attribute the problem to very specific combination of things.

My question is this: Does Access 2010 reset autonumber fields that are used as key fields for tables? If this is the case, is there a hotfix for this problem? My client wants to upgrade to 2010 because the application runs rather slowly in 2007. But we compact the back end on a regular basis. I've read Allen Browne's article about fixing autonumber fields but this isn't a practical solution for a large application with dozens of tables.

Is there any definitive information from Microsoft about this problem.

Thank you.

[June7]

Google: Access2010 compact and repair breaks autonumber seed

Here is one older but interesting thread on the topic http://windowssecrets.com/forums/sho...aks-AutoNumber

Also review http://allenbrowne.com/ser-40.html

I am not able to replicate the issue but did not test with a split db.

[mguilbeault]

Google: Access2010 compact and repair breaks autonumber seed

Here is one older but interesting thread on the topic http://windowssecrets.com/forums/sho...aks-AutoNumber

Also review http://allenbrowne.com/ser-40.html

I am not able to replicate the issue but did not test with a split db.

Thank you for your reply. I read this thread when I researched the problem. In the database in questions the field is not sorted descending but we have the problem anyway. I can't find anything that speaks definitely to the problem: does Access 2010 reset autonumbers that are used as key fields? If it does and Microsoft knows about the issue, is there a fix?

Thanks to anyone who has the answer.

[June7]

I still can't replicate the issue. Even if I could, I don't know if there is a fix, especially since your data is not set with the descending order which caused the issue in the referenced thread.