MS Access duplicate record error

Question

I am struggling with MS Access duplicate record error. I am getting this error on some of the records while doing inserts. The exact error is "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again".

The interesting part is that while running the same data set I get errors on different records each time. I removed the indexes in a destination table but still the same issue. I read that it might be MS Access glitch, so I created identical table in SQL Server and attempted the insert - worked like a charm. So it seems it is Access issue. Is there any workaround? I have tried to find one but so far no luck. Would appreciate if someone could point me to the right direction as of now changing databases would not be an option. Thanks.

P.S. My primary key is a composite key of Name (text), Key (text) and LineNo (integer). There could be mnore than one key with the same name per each Name fields but different Line number.

Have you tried the accepted solution of [this](http://stackoverflow.com/questions/5226277/why-does-ms-access-2007-not-allow-a-row-insert-but-then-allow-it-on-the-next-in) post? — actaram, Mar 24 '15 at 15:21
Are any of the other fields in your table indexed with "Yes (No Duplicates)"? — HansUp, Mar 24 '15 at 15:24
HansUp, no. I had an index that allowed duplicates but I removed all indexes just in case — Coding Duchess, Mar 24 '15 at 15:31
BishopBarber, yes, I saw that before and I am running the app as we speak to test that out — Coding Duchess, Mar 24 '15 at 15:34
Does your db include a relationship definition which references that table? — HansUp, Mar 24 '15 at 15:41
HansUp, nope, no relationships are defined. Just one table and some queries that use that table — Coding Duchess, Mar 24 '15 at 15:52
BishopBarber, I added Dattime field to the table and modified my insert to add the datetime.Now stamp to the record but still getting the error — Coding Duchess, Mar 24 '15 at 15:53
Is your insert in the form of `INSERT ... VALUES` or `INSERT ... SELECT`? — HansUp, Mar 24 '15 at 15:53
Or if you can't share your DB as HansUp requested, can you at minimum take a few screenshots of your table structure(s) in design view and also your query? — Newd, Mar 24 '15 at 18:36
I removed constraints and it works much better now. I cannot really share either the database or any data — Coding Duchess, Mar 24 '15 at 19:40

MS Access duplicate record error

0 Answers0