Duplicates in Primary Key (1 Viewer)

[TheSearcher]

I have a table that has an autonumber field (Indexed, No Duplicates) set as the primary key.
Every now and then the table will get corrupt because Access will allow a duplicate entry. I've read the Allen Browne articles so I know how to fix this - but does anyone understand how this can happen? Shouldn't the fact that the field is a primary key prevent duplicates from getting in? These dupes occur as the result of an append query. The database is split. Any help would be appreciated!

 

[MajP]

You reseeded and that did not work? I would rebuild the table from scratch.

Error (The changes you requested to the table were not successful) when you insert a new record in a table - Microsoft 365 Apps

Describes an issue in which you receive a (The changes you requested to the table were not successful) message when you insert a new record in a table that contains an Autonumber field.

learn.microsoft.com

 

[TheSearcher]

I just did a compact and repair. Wouldn't reseeding change the autonumber of each record? That would be disastrous.

 

[theDBguy]

Wouldn't reseeding change the autonumber of each record?

Reseeding shouldn't affect existing records - only new ones.

 

[MajP]

Read the article. You reseed at your current Max. However if this continues after a CR anda reseed it may be too corrupted and needs a rebuild. If rebuilding you likely will need a field to save the old PK value. Then do some update queries to reassign FKs

 

[CJ_London]

Have you determined how you are getting the duplicates? What process are you using to insert new records?

for it to happen once is unfortunate, but to continue to happen indicates something else may be wrong

 

[TheSearcher]

MajP - I never reseeded. I only did a CR.
I tried using the code below to reseed but It's giving me the errors listed below.
CJ_London - I am using an Insert query in VBA from a front end db to a back end where the table resides.

Here's my reseeding code:

The data type of Trans_ID is Long Integer.
The msgbox returns:

This looks good to me. Any ideas as to what could be wrong?

 

[theDBguy]

The data type of Trans_ID is Long Integer.

I think the data type probably needs to be Autonumber.

 

[MajP]

Is it possible that column is not an auto number but only indexed? That is not what was said in original post.

 

[TheSearcher]

Okay. I need to slow down. The field is not autonumber. It is a Number field which is indexed with No Duplicates allowed. It is also the Primary Key. My sincerest apologies.
The issue is still the same, however. Even though no duplicates are allowed occasionally duplicates get in. They are inserted using an Insert sql statement from a front end database. The table is in the back end. I should mention that the dupe is the entire record - not just the primary key. The other fields in the record are duped as well. Again, I apologize for the misinformation and really appreciate your help.

 

[MajP]

If this field is truly indexed and no duplicates, and a duplicate trans_id is created then the only possible thing is a corrupted table. There is no way I know to run an insert into a good table causing a duplicate. You can check ahead of time and not insert a duplicate solving the symptoms. This does not solve the underlying issue. I would still be concerned.

 

[CJ_London]

So what is your code for determining a unique ID to be inserted?

and on the matter of infrastructure- you have the db split, so can you confirm

a) every user has their own copy of the front end - I.e. they do not share a front end on the server

b) all users are connecting via a wired network, I.e. no one uses a wireless connection

both of these run a high risk of causing corruption

 

[TheSearcher]

MajP: Yes, the table is corrupt. That's why a CR works. But I've rebuilt the table several times from scratch by exporting the data to a text file, creating an entirely new blank table and importing the data back in. The table then becomes clean. But a few days later the same thing happens. So, I must conclude that there is something causing the corruption and I am trying to determine what that "something" is.
CJLondon: a.) Yes. Every user has their own individual copy of the front end. They do not share a front end.
b.) I cannot confirm that everyone is accessing the program via a wired connection. I'm sure some are using wifi. This is not unusual however. It's the same way they access many other Access programs.

 

[TheSearcher]

CJLondon: Sorry. I didn't answer your first question. I grab the transaction ID (which is a unique primary key of another table) and insert that into this table.

 

[CJ_London]

Users connecting wirelessly can cause your corruption in the event of a drop in connection . If it is not happening to other apps that is just luck.

don’t know if you can determine it but see if you can find who was inserting into the table at the time and check their connection

I presume by grab you are using a sql insert query? Not vba add - assign by some means - update?

so what is the likelihood of two users grabbing the same value at the same time?

might help if we understood what you are actually doing when records are inserted since it is unusual to create what is effectively a 1 to 1 relationship - are these records inserted one at a time? Multiple records at a time, perhaps as an import?

 

[theDBguy]

CJLondon: Sorry. I didn't answer your first question. I grab the transaction ID (which is a unique primary key of another table) and insert that into this table.

So, you take the value from a primary key and then insert into another primary key field? Just trying to clarify...

 

[MajP]

MajP: Yes, the table is corrupt. That's why a CR works. But I've rebuilt the table several times from scratch by exporting the data to a text file, creating an entirely new blank table and importing the data back in. The table then becomes clean. But a few days later the same thing happens. So, I must conclude that there is something causing the corruption and I am trying to determine what that "something" is

What I am saying is that the table is corrupt and allowing this to happen. A good table would not have a problem if you tried to insert a duplicate. It would basically "drop it on the floor" and deny the change. The CR is fixing the symptoms, but not the underlying condition. So it is capable of causing this problem after a CR.

 

[Pat Hartman]

Create a new database with all the tables but NO data. Make sure that the PK's are defined correctly as well as the indexes and relationships. Make sure you don't have spurious indexes created by Access. The default setting of Access automatically creates an index on table fields with certain suffixes. You want to always turn this feature OFF. You need to be incontrol over what indexes get created. It is almost worse to create unnecessary indexes as it is to not create enough.

Export each table from the database to a .csv file. Then open the new database with just the tables/relationships and load them in a logical order. link to the exported files and use append queries. When you define relationships, you need to load the high level tables first. Make a procedure to run the append queries because you are likely going to need to do this more than once. Load the lookup tables first. Then load the parent tables and then the child tables and the grandchild tables.

Compact and zip. Then test thoroughly.

I don't know how an ordinary number field gets corrupted but they can. You need to exorcise the demons though and that can be tedious.

I do know that there were two bugs in older versions of Access that caused corruption to autonumbers. One was if you defined an autonumber and made a different field the PK. When Access reloads tables as part of a compact, it sorts all the tables on PK so they get loaded in PK order (this is what fools people into thinking that tables are always sorted but that's a different issue). However since the autonumbers are not the same sequence as the PK, the highest PK might be 992288 and that will be the last physical record for that table but that record may not have the highest value autonumber. Say the autonumber for that record is 10. The autonumber seed was getting set to 10 rather than the actual highest value which might be 1003345. So, the next record you add gets assigned the autonumber of 11 which probably already exists, but it might not if you deleted some old records so it could take a while before the seed increments to a value that duplicates an existing record. I'm pretty sure this bug has been fixed for a long time but old versions of Access ae still in use so the way to prevent this bug is to either get rid of the unused autonumber OR to make the autonumber the PK and the other field a unique index. The second solution changes nothing in the logic, It just keeps Access from making a mistake and the C&R will sort the reload into Autonumber sequence rather than the sequence of the other unique index. NEVER have an autonumber in a table UNLESS that autonumber is marked as the PK. The ONLY reason for an autonumber to ever exist at all is because it is needed as the PK for a table.

The other bug had to do with binding both a mainform and a subform to the same table and adding rows sometimes from the main form and other times from the subform.

 

[MajP]

Can you post the table with the corruption or a screen shot of a duplicated primary key? I scoured the internet and came across posts on the autonumber causing this problem. I have not seen a single post on other fields being corrupted in this way.

 

[arnelgp]

Public Function ResetAutoNumber(ByVal TableName As String, ByVal AutoField, Optional Start As Long = 1, Optional Increment As Long = 1)
'You can execute an Access DDL statement from ADO to reset the autonumber seed value. Here is an example Immediate window session:
'The statement must be executed from ADO. It will fail if you try it with DAO (such as CurrentDb.Execute strDdl), or from the Access query designer.
'The example succeeded because Currentproject.AccessConnection is an ADO object.
'The two values following COUNTER are seed and increment. So if I wanted the autonumber to start from 1000 and increment by 2, I could use COUNTER(1000, 2)
'If the table contains data, the seed value must be greater than the maximum stored value. If the table is empty when you execute the statement, that will not be an issue.
Dim strDdl As String
On Error Resume Next
strDdl = "ALTER TABLE " & TableName & " ALTER COLUMN " & AutoField & " COUNTER(" & Start & ", " & Increment & ");"
CurrentProject.AccessConnection.Execute strDdl

End Function

Public Sub ResetAuto()
    Dim iMaxID As Long
    iMaxID = DMax("Trans_id", "tbl_X") + 1
    Call ResetAutoNumber("tbl_X", iMaxID)
End Sub