none
AutoNumber creating duplicate numbers in Access 2010 RRS feed

  • 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

    Wednesday, November 30, 2016 5:18 PM

All replies

  • 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


    Wednesday, November 30, 2016 5:22 PM
  • 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.

    Wednesday, November 30, 2016 5:52 PM
  • 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

    Wednesday, November 30, 2016 6:09 PM
  • 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

    Wednesday, November 30, 2016 6:13 PM
  • 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. 

    Wednesday, November 30, 2016 6:56 PM
  • 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


    Wednesday, November 30, 2016 7:15 PM
  • 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

    Thursday, December 8, 2016 6:37 PM
  • 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...

    Thursday, December 8, 2016 7:47 PM
  • 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

    Friday, December 9, 2016 2:36 PM
  • 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

    Friday, December 9, 2016 6:42 PM