CODEWITHSUNDEEP
mysqldatabasems-access
lmorse
lmorse

Reputation: 135

MS Access Primary Key AutoNumber has become confused

I am working on a large database with many tables, all of which have Auto numbered primary keys. The database is stored on a network, and several people have access.

My issue is this: one user lost network connection while adding data to a table via a form. Several other people have added data to the table subsequently. This gives a situation where one primary key is missing (e.g. primary keys go from 1 - 2000, however the entry for PK 1974 is missing - the one that was being created when the user lost connection). I was asked to insert the missing data into the table, with the missing key ID at the appropriate point in the table. I used "DoCmd.RunSQL "INSERT INTO 'tablename' (PrimaryKeyID, Field1) VALUES ('1974', value1)".

This has caused issues in that Access thought the next 'newest' primary key it had to create was '1975' and we received a message about duplicated keys. A few people have since managed to add new data, however any subsequent new data is created at 1976, 1977 etc, which is overwriting the existing data.

Can anyone tell me why this is happening? Is there a way to force Access to 'look' at the largest primary key in the table to create new auto numbered keys?

Thanks

Lee

Upvotes: 0

Views: 148

Answers (1)

iDevlop
iDevlop

Reputation: 25272

Try compacting the back-end. I think it should reset the new values.

Upvotes: 5

Related Questions