AutoNumber creating duplicate numbers in Access 2010

Question

I have an Access 2010 database that has a table with a column that has a data type of Autonumber. The Autonumber column occasionally creates rows with the same value, i.e. two rows with 2 in the field.  The application has multiple concurrent users who could be adding rows to the table at the same time. 

I need to find out why the duplicate values are being created and what can be done to insure a unique value is created whenever a new row is created.

---

Brad C. Parris Sr. IT Manager Custom Solutions

Answer 1

Is the field a primary key?
Did you set the Indexed property to Yes (No Duplicates)?

What is the purpose of the field in question?  Perhaps switching it to a Number field and assigning the number through the use of DMAX() could eliminate this issue.

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Hi Brad,

Just want to clarify this part:

"The application has multiple concurrent users who could be adding rows to the table at the same time."

Is your database split into a front and a back end? How exactly are the users adding "rows" to the table? Are they adding one row at a time or multiple rows at the same time?

Thanks.

Answer 3

Thank you for responding so quickly. To answer your question on one database the autonumber field is the primary key and when a duplicate number is created it causes an error.

---

Brad C. Parris Sr. IT Manager Custom Solutions

Answer 4

The table is being updated through a form so by definition they are only adding one row at a time. 

My question is more how/why an autonumber field would create duplicate values.  This only started when I converted the database to Access 2010.  These application never had this issue when they were in Access 2003.

---

Brad C. Parris Sr. IT Manager Custom Solutions

Answer 5

Hi Brad, 

Have you done compact/repair the database after the conversion? If no, please do; it will help to fix the mess on the database.

You have to make your AutoNumber field a primary key with no duplicates. 

Also, I suggest you to put a Refresh method on your form before inserting a new record. I suppose you are working with a splitted database, form with a linked table.

HTH. 

Answer 6

The table is being updated through a form so by definition they are only adding one row at a time. 

My question is more how/why an autonumber field would create duplicate values.  This only started when I converted the database to Access 2010.  These application never had this issue when they were in Access 2003.

Hi,

you're not the first one to ask this question. It is an old problem and unfortunatelly usually the cause and circumstances are not really known.

There are at least these 2 (old) confirmed bugs:

https://support.microsoft.com/kb/291162

https://support.microsoft.com/kb/884185

However in the quite many discussions and a few cases i personally saw over the years they never applied.Your "occasionally" doesn't sound good. When I treated it with ALTER COLUMN (as shown in the second kb article) + Compact AFAIK the problem didn't return.

Many years ago in a training I asked 5 students to insert into the same table at exactly the same time and we could fabricate the problem several times just by enough concurrency. This was kind of an artificial situation and I think with Access 97 but...

So in my applications I have tried to avoid high concurrent inserts with JET/ACE or at least cushion suspicious concurrent inserts by writing away newly created autonumbers quickly by code etc.

---

Karl

Answer 7

Given that I just started seeing this issue after converting two databases from Access 2003 to 2010 is it possible that the deprecation Workgroup Security code in Access 2010 could be part of why the autonumber field is getting the same number more than once.  So for example one of the users starts to put a new record in the table but before the record is saved another user starts entering in a record and the second user gets the same next number for the autonumber.

---

Brad C. Parris Sr. IT Manager Custom Solutions

Answer 8

Given that I just started seeing this issue after converting two databases from Access 2003 to 2010 is it possible that the deprecation Workgroup Security code in Access 2010 could be part of why the autonumber field is getting the same number more than once.  So for example one of the users starts to put a new record in the table but before the record is saved another user starts entering in a record and the second user gets the same next number for the autonumber.

---

Brad C. Parris Sr. IT Manager Custom Solutions

Hi Brad,

It's possible but I highly doubt it. The difference between Access and SQL Server is as soon a user enters any information to create a new record in Access, an Autonumber is automatically assigned to it. If the user decides to discard the new record before it is saved to the table, the assigned Autonumber is also discarded. So, any other user entering a separate new record while the first user is also entering a new record will "more than likely" get the next Autonumber assignment even if the first record's Autonumber has not been saved to the table yet.

Just my 2 cents...

Answer 9

Hi,

I don't think so. The problem has been reported occasionally for many versions of Access IIRC at least back to 97.

For possible reasons we would have to know the inner mechanism how Access handles autonumber fields but such details about JET/ACE are not publicly documented.

The 20 years old "Jet database engine programmer's guide" only says that in the then even older JET 2.x days the next Counter number had been stored in the table-header page of the database file. When adding a record to a table with a Counter field that page had been locked. That had provoced some issues and it would not be a problem any more with JET 3.x (i.e. A95+A97). That's it about documented details I'm aware of.

There have been other changes like with SP4 for JET 4 compact doesn't reset autonumbers any more, in exchange we've got the ALTER COLUMN for the seed and increment.

It could be a complex mechanism involving caches (table, local form/recordset), get the number into the form, increment it in the responsible cache, actually save it into the table, transfer all information between BE and FE over the network etc. Plenty of space for scary guesswork. ;-) 

So AFAIK we can only do a few things mostly already mentioned: reset the seed value and compact/repair the table, try to find culprit machines with maybe bad connections, be defensive about concurrent inserts, for effected tables explicitely save away records ASAP, if nothing helps maybe try to recreate the table from scratch etc.

---

Karl

Answer 10

I've seen this problem occasionally, and without being able to identify the exact cause it usually seems to be some kind of internal corruption. I've repaired the database using the function below to eliminate the problem. It may return but I don't think there's any connection between the events. It usually happens to my clients, not on my own systems, and if it recurs it's usually some years later.

Public Function pjsAutonumberResetToNextNumber(ByRef strError As String) As Boolean
On Error GoTo ErrorHandler
'This routine checks all user tables in the current database. _
 If any autonumber seed values are not equal to the next available data value, _
 the seed value is reset. _
 NOTE: It does not process linked tables, only native tables in the current project.
 
    'IF the following objects are declared as ADODB and ADOX objects, _
     the project must have references for both the Microsoft ActiveX Data Objects 2.x _
     and the Microsoft ADO Ext 2.x for DDL and Security Libraries (where 2.x is 2.1 or later.)
    Dim cnn As Object       'ADODB.Connection
    Dim cat As Object       'ADOX.Catalog
    Dim tbl As Object       'ADOX.Table
    Dim col As Object       'ADOX.Column
    Dim rst As DAO.Recordset
    
    Dim fSuccess            As Boolean
    Dim fIsAutoNumber       As Boolean      'Is the current column an autonumber?
    Dim lngSeedCurrent      As Long         'Current seed value
    Dim lngSeedNew          As Long         'New seed value
    Dim lngValueMax         As Long         'Maximum value of column in the table
    Dim strSQL              As String
    
    'Set connection and catalog to current database.
    Set cnn = CurrentProject.Connection
    Set cat = CreateObject("ADOX.Catalog")  ' = New ADOX.Catalog
    
    'cat.ActiveConnection = cnn 'For front-end database
    'For backend database
    cat.ActiveConnection = Replace(cnn.ConnectionString, CurrentDb().Name, pjsGetBackendDatabase().Name)

    'Assume we succeed (will be set false if we fail)
    fSuccess = True
    strError = ""
    
    'Loop through all the tables, ignoring system tables
    For Each tbl In cat.Tables
        'Only process user tables, not system or temporary. _
         Code modified 2007.07.03 to include linked tables. _
         BUT ADOX cannot execute data definition statements on linked data sources, so it would fail. _
         In that case we simply REPORT the problem using the strError argument.
        If tbl.Type = "TABLE" Or tbl.Type = "LINK" Then
            'Loop through all the columns in this table
            For Each col In tbl.Columns
                'Is this an autonumber column?
                fIsAutoNumber = col.Properties("Autoincrement")
                If fIsAutoNumber Then
                    'Make sure the autonumber is set to increment, not random
                    If col.Properties("Increment") <> 1 Then
                        col.Properties("Increment") = 1
                    End If
                    
                    'Get the current seed value
                    lngSeedCurrent = col.Properties("Seed")
                    
                    'Get the current max column value
                    strSQL = "Select max([" & col.Name & "]) as MaxValue From [" & tbl.Name & "];"
                    Set rst = CurrentDb.OpenRecordset(strSQL)
                    lngValueMax = Nz(rst.Fields(0), 0)
                    rst.Close
                    lngSeedNew = lngValueMax + 1
                    'Is calculated seed different than the current seed?
                    If lngSeedNew <> lngSeedCurrent Then
                        'Reset the seed to the next value, but not possible for linked tables.
                        If tbl.Type = "TABLE" Then
                            col.Properties("Seed") = lngSeedNew
                            tbl.Columns.Refresh
                            'Were we successful?
                            fSuccess = fSuccess And (col.Properties("seed") = lngSeedNew)
                        Else
                            'For a linked table, we're ok as long as the seed exceeds the current value.
                            If lngSeedCurrent <= lngValueMax Then
                                fSuccess = False
                                strError = strError & tbl.Name & "." & col.Name _
                                 & " has an invalid seed value of " & lngSeedCurrent _
                                 & " which should be greater than the maximum data value of " & lngValueMax _
                                 & "." & vbCrLf
                            End If
                        End If
                    End If
                    
                    'Finished with this table since each table can only have 1 autonumber
                    Exit For
                End If
            Next col
        End If
    Next tbl
    
ExitHandler:
On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    Set cnn = Nothing
    pjsAutonumberResetToNextNumber = fSuccess
    If Len(strError) > 0 Then
        Debug.Print strError
    End If
    Exit Function

ErrorHandler:
    Set objError = New pjsError
    objError.pjsErrorCode Procedure:="pjsAutonumberResetToNextNumber", ModuleName:=mconStrModuleName
    Set objError = Nothing
    fSuccess = False
    Resume ExitHandler
    Resume
End Function

---

Paul