Cannot insert duplicate key row

[barbarao]

Hi. Access front end. SQL backend. Everything has been working fine until 1.5 hours ago when I started getting a message that says" Cannot insert duplicate key row in a certain table with a unique identifier. Duplicate key value....

Just before this started to happen, I did create a few subforms for the form which is based on the referenced table. I removed those subforms but no luck.

Any ideas on what I should do? Thanks so much!

[ck9663]

Hard to say what you should do as we do not know your process. What does you form do, etc.

For the most part, get the table name being offended. That means you are inserting records in that table with a column as PK (or has a unique index on that column) that's already in the table.

Happy Coding!!!

~~ CK

[barbarao]

Hard to say what you should do as we do not know your process. What does you form do, etc.

For the most part, get the table name being offended. That means you are inserting records in that table with a column as PK (or has a unique index on that column) that's already in the table.

Happy Coding!!!

~~ CK

The form is for client demographic data. When you create a new client, the system goes to the history table, finds the highest client id, then increases it by one to create an ID for the new client. From what I can tell, somehow there is a slip in next number. The hisotry table says the highest number is 212, for example but the new id is saying 211. I didn't build this system but inherited it. I believe there is an index on the server saying this has to be a unique ID but in my rebuild of the tables I am using SS# for as ID so don't need to create a next number. Any ideas on what I can do other than rebuild the table and insert the data into it? Thanks again.

[Rabbit]

We would need to see the code that creates the ID number. Why did you decide to create the number using code rather than just letting an auto number handle it so that it's guaranteed to be unique?

The most likely cause is that a record got deleted but the history table did not get the related records deleted.

[barbarao]

We would need to see the code that creates the ID number. Why did you decide to create the number using code rather than just letting an auto number handle it so that it's guaranteed to be unique?

The most likely cause is that a record got deleted but the history table did not get the related records deleted.

Hi. I didn't create the table thus didn't create the code. I built a new table and brought the data into that as I noticed that the person who built this DB in the first place had a few more things that could case problems down the road for this table. Thanks for the replies!

[ck9663]

First you have to gather the relationship of all your tables. Find out which is the parent and which one are the children. Then you have to make a decision on what to do with those orphan records. You may need to create a backup before you make any changes. If your decision is to delete the orphan records, you should delete them. Then create a referential integrity on your parent-child tables. It should eliminate your orphan record issues.

The challenge with manually adding the next available ID is when multiple users tried to add them at the same time. Although it's not impossible, it will be coding intensive. If you can get away with it, try to look for the code that insert the new record and modify it not to insert a value on the ID column. Then enable the Identity property of that column. Be sure to check what the next available number is and if it's set to be the next one.

Good Luck!!!


~~ CK