Duplicates in Primary Key

[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.

 

[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