Full Version: Re: Autonumber 2007-2010 - Duplicate Key
UtterAccess Forums > Microsoft® Access > Access Forms
We have had multiple times, approx. 2 twice a month for the last 4 months, when we are getting a duplicate key message and no users can enter new rows.
Looking at the table there is not a duplicate record.
The only table connected has a field as a primary key, autonumber field type, and index (no dups).
If I delete all the rows and reload the table, the users can add rows again.
We are running MS-2010 Access, with a AccessDB formatted as 2003. Is this a MS bug?
I had the same problem several times a few months ago. Access seems to lose the value for the autonumber field an tries to insert a new record with index 1 which has already been used.
managed to forcibly set the correct value for the autonumber field with an Alter Table Statement and after a while, the error did not occur again.
To me, this indeed looks like a bug - but it seems to have been fixed with one of the Updates.
Jerry Whittle
I remember that there use to be such a bug with an older version of Access. As your database is still in the MDB format, could anyone be using an older version of Access?
The first thing that I'd try would be to open a new MDB file and import everything from the problem database into it. Then use this new database file. That could eliminate a corruption problem.
A second solution would be to convert the database to the newer accdb file format. Don't do this if you have User Level Security enabled.
Here's Allen Browne's article that addresses the problem, including fixes:
Linq ;0)>
Thanks - This looks to be an not a easy fix. The problem is on a sub-form. The fix code on the link looks complex.
How about if I just change the the unique identifer on the rows for the table to be something different?
Ocan make it a time-date stamp, or string add the networkid to the the autonumber(and-let-the-autonumber-have dups) and put it in another field making it the unique identifer for a row.
We are planning to move to the database format of 2010 sometime soon too, from *.mdb to *.accdb. That would probably fix the problem too from what I been reading, right?
Attached is was the autonumber is defined as now.
PS - We love your laughing monkey avatar.
I had the problem with an Access 2010 .accdb Database, so: no, changing the db-format does not necessarily fix the problem.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.