"duplicate values in the index, primary key, or relationship" Error. Help needed (1 Viewer)

pau · Feb 24, 2015

"duplicate values in the index, primary key, or relationship" Error. Help needed

I have a split Access 2007 database with the backend on a shared drive and copies of the frontend distributed to users. It worked perfectly when I tested it on my computer, even testing multiple copies at once. But when multiple users on different computers attempt to enter records at the same time, the second person to finish entering their record gets the message: "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." When I look at the backend tables after this, the first record is entered fine and the following row just says error.

I have a form in data entry mode that has the fields of Table A. This form has a subform with the fields of Table B. Table A's autonumber ID field is a foreign key in Table B. The autonumber fields are of course not added by the users.

I'm really stuck here and could use some help.

Thanks

MarkK · Feb 24, 2015

Welcome to the forum. Open the tables in design view and check what is indexed. It sounds like you have an index that is too restrictive, or instance, maybe you have a date field that has a default value in a form, but the index in the table doesn't allow duplicates. When the second user tries to save a record, the second instance of the same date is not permitted by the table.

Check which fields, or combinations of fields, have a unique index. Relax these constraints and see if that solves the problem.

Cheers,

pau · Feb 24, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

Thanks for the reply. I checked and there was a custom index. I haven't had a chance to test with other users yet, hopefully this solves the issue.

If the index was causing this problem, is it possible that I could never experience this issue when testing on my computer (including entering records at the same time with two copies open) and the error only occurred when the database had multiple different users from different computers? That's the part that doesn't make sense to me.

pau · Feb 24, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

I tried testing it with another user after removing the index. The same error was still occurring.

pau · Feb 24, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

Ok, so it seems that what is happening is something like this...

There are 10 records.

User A begins adding a new record and ID 11 is created for that record, but not yet saved in the table.

User B, sometime before user A finishes, begins to add another record and ID 11 is created for them as well because there are still only 10 records in the database.

User A successfully saves their record with ID 11 to the database.

User B gets the error.

This only happens with multiple computers. With two copies on a single computer, if one copy is entering ID 11, the other copy gets ID 12, etc.

Is there anyway around this while still using autonumber for the IDs?

MarkK · Feb 25, 2015

Is there an index on that ID field, both primary and unique, as well as it being autonumber? And do you connect from the FE to BE via linked tables in the FE?

Does the RecordSource of the problem form only draw data from a single table? Maybe you can post the SQL of the query driving that form.

Can you cause the same problem if you have just a main form with a single table recordsource and no subform? Create such a test form, if you haven't already, and see if the problem still occurs.

I am interested in this claim if you can think of a way to demonstrate it. It is an unlikely claim you are making, so the burden of proof is a little bit on you to show this is actually happening, sorry to say. To be able to show it occurs, as I'm suggesting, clean up the problem so we can start to rule out any other possible causes. And then if we don't find the cause, then you'll be close to being able to post the problem for others to test.

Let me know how this goes, I'll be checking back

pau · Feb 25, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

Is there an index on that ID field, both primary and unique, as well as it being autonumber? And do you connect from the FE to BE via linked tables in the FE?

Yes to all.

Does the RecordSource of the problem form only draw data from a single table? Maybe you can post the SQL of the query driving that form.

Single table. The form and subform were built with the form wizard. This is a data entry form, so I'm not using an SQL query to filter anything.

Can you cause the same problem if you have just a main form with a single table recordsource and no subform? Create such a test form, if you haven't already, and see if the problem still occurs.

I haven't tried this yet. It only happens with multiple computers and this is a work-related database, so it's difficult to get my hands on multiple computers that have proper network access unless I'm dragging a coworker away from their own work. I will probably have an opportunity to test at some point today. I'll keep this in mind.

pau · Feb 25, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

I tried a very simple test database. One table with three fields, an autonumber ID, a date field,and a text field. Two forms, one to view the date and text fields and another in data entry mode. The same error occurred.

I tried to make the ID field number rather than autonumber and added a field to the data entry form so users could enter an ID of their choice.

I tested with another user and we both entered different ID values. We were working on new records simultaneously, but the other user finished first. I could view my newly added record, but not his. He could view his newly added record, but not mine. We refreshed the links to the BE and I could see his new record, but I could no longer see mine. When I looked in the actual BE table, only his record was there.

Could this be caused by some of the advanced settings or trust settings?

MarkK · Feb 26, 2015

I don't know what would cause this. Like I say, I'd like to see it demonstrated, since it has always been my belief that a table never grants the same AutoNumber ID twice. What it's always looked like to me is that as soon as I type a single character into a new record, the Unique ID is granted by the table, even if I don't save the record. Any new records start with the next ID after that, in which case there would never be a conflict.

And I've never seen this occur in a production database.

One setting might be worth checking: "Recordset Type", the second property on the data tab of the bound form's property sheet in design view. Should be "Dynaset". Make sure it's not "Dynaset (Inconsistent Updates)"

Curious,

pau · Feb 26, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

It seems like updates from the BE are not being communicated to the various FE copies when in use at the same time by multiple computers. The autonumber doesn't generate the same number twice for one user, but for multiple users (on different computers) it does. So it seems that the problem is BE information is not being updated (or not updated fast enough) for multiple simultaneous users.

And the recordset is dynaset.

CJ_London · Feb 26, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

perhaps a bit of a radical solution and it would be nice to know why you have the problem but just so you can move on, have you considered changing autonumber from increment to random or even replicationID?

I tend to use random to discourage users from any inclination to give the ID field any meaning other than as a means of identifying the record.

pau · Feb 26, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

Yep, I tried that too. The randomly generated IDs eliminated the "duplicate values in the index..." message, but the the second record that was entered would not be saved in the BE table. Everything would to appear to work, but when you checked the table the record would not be there.

It seems to be some problem with communication between the FE and BE when multiple FE copies are open (on different computers). I have no idea how to solve it.

CJ_London · Feb 26, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

just a wild stab in the dark, no idea if this would impact adding new records - but have the client settings (advanced) been changed re record locking/refresh interval etc? I'm not too familiar with these since I have never had cause to change them so may affect front end or back end

pau · Feb 26, 2015

Re: "duplicate values in the index, primary key, or relationship" Error. Help needed

I was wondering about that too. I haven't changed those settings, but this is a work computer, so they may have changed them from the defaults before they distributed copies of Access to the workstations. I'm not familiar with those settings at all. It's something to look into.

"duplicate values in the index, primary key, or relationship" Error. Help needed (1 Viewer)

pau

New member

MarkK

bit cruncher

pau

New member

pau

New member

pau

New member

MarkK

bit cruncher

pau

New member

pau

New member

MarkK

bit cruncher

pau

New member

CJ_London

Super Moderator

pau

New member

CJ_London

Super Moderator

pau

New member

Similar threads

Users who are viewing this thread