Access 2010 duplicate errors after compact

By rw2000 ·
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 (, 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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Probably got nothing to do with primary key

by Tony Hopkinson In reply to Access 2010 duplicate err ...

or reseeding as such unless you have a unique index built on the autonumber column there's no reason for there not to be duplicates. Given access' propensity for making an arse of your data, you are just asking for it with a schema like that.
Make the autonumber the primary key and put a unique index on the compound key.

Collapse -


by rw2000 In reply to Probably got nothing to d ...

Tony, Thanks for replying. I did change the primary key to the autonumber and made the composite key unique. After I did that, the reseeding problem stopped.
I posted the question because I might run into a table where it would not be convenient to swap the keys. I'm hoping someone might know of a patch or a more permanent solution than a re-design. I don't want to re-design any other legacy databases that might have this issue.

Collapse -

I can't think of a single reason why the keys couldn't

by Tony Hopkinson In reply to thanks

be swapped.
Non unique autonumber isn't a possibility, and you can always add an index.

Use Access and this sort of crap comes with the territory, no magic sponge possible. If maintenance/robustness is an issue get rid of it and use sql server express as a backend
How much work that is depends on the application.

You only have to google access database corruption to see what the crack is.

Wrong tool, simple as that.

Related Discussions

Related Forums