Questions

Access 2010 duplicate errors after compact

Tags:
+
0 Votes
Locked

Access 2010 duplicate errors after compact

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 (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.
  • +
    1 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    rw2000

    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.

    +
    0 Votes
    Tony Hopkinson

    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.

  • +
    1 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    rw2000

    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.

    +
    0 Votes
    Tony Hopkinson

    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.