Microsoft Access Tips for Serious Users

Provided by Allen Browne.  Created: August 2003.  Last Updated: January 2015.


Fixing AutoNumbers when Access assigns negatives or duplicates

In Access 2000 and later, an AutoNumber field may begin assigning extreme values, negatives, and even duplicates. The attempt to assign a duplicate fails if the field is primary key, so it may not be possible to save new records in an affected table.

Several issues can trigger this problem:

Cause Solution
An old bug If you are using the MDB file format, apply JET 4 Service Pack (7 or later) to prevent the problem recurring. Use the code below to fix your AutoNumbers.
Appending data to a linked table. Microsoft's workaround is discussed in knowledge base article 884185. Note that this issue is more generic than the kb article suggests: it occurs any time you specify the value for the AutoNumber when appending to a linked table, not just when appending from one linked table to another.
A badly-designed form, discussed below. Redesign the form. Use the code below to fix the AutoNumber.
AutoNumber defined as Random or Replication ID. Open your table in design view, select the AutoNumber field, and ensure Field Size is "Long Integer", and New Values is "Increment." (Not possible in replicated databases.)

Resetting the Seed

You may be able to solve the problem with a compact/repair:

(Note that compact/repair may not solve the problem in Access 2013. In the particular case where we first saw this, the AutoNumber was not the primary key.)

Alternatively, the code below checks all tables in your database, and offers to fix any where the Seed of the AutoNumber is less than zero or below the existing values. The function returns the number of tables that were changed - zero if none had the problem. It does not alter the values of any duplicate values in the column.

The code does not work on attached tables: if your database is split, run it in the back end data file.

It works only with Access 2000 or later, but earlier versions do not have this problem.

Instructions

  1. Back up your database.
  2. Create a new module. In Access 2007 and later, click Module (rightmost icon) on the Create ribbon.
    In older versions, go to the Database Window, choose the Modules tab, and click New.
    Access opens a new code window.
  3. Copy the function below, and paste into the code window.
  4. Choose References from the Tools menu, and check the box beside: "Microsoft ADO Ext. 2.x for DDL and Security".
  5. From the Debug menu, choose Compile to check there are no problems.
  6. Press Ctrl+G to open the Immediate Window. Enter:
        ? AutoNumFix()

How it works

Version 4 of JET supports altering the Seed for an AutoNumber (which is how they got it wrong.) The DAO library was not updated to handle the new feature, so we use ADOX.

The code performs these steps:


Function AutoNumFix() As Long
    'Purpose:   Find and optionally fix tables in current project where
    '               Autonumber is negative or below actual values.
    'Return:    Number of tables where seed was reset.
    'Reply to dialog: Yes = change table. No = skip table. Cancel = quit searching.
    'Note:    Requires reference to Microsoft ADO Ext. library.
    Dim cat As New ADOX.Catalog 'Catalog of current project.
    Dim tbl As ADOX.Table       'Each table.
    Dim col As ADOX.Column      'Each field
    Dim varMaxID As Variant     'Highest existing field value.
    Dim lngOldSeed As Long      'Seed found.
    Dim lngNewSeed As Long      'Seed after change.
    Dim strTable As String      'Name of table.
    Dim strMsg As String        'MsgBox message.
    Dim lngAnswer As Long       'Response to MsgBox.
    Dim lngKt As Long           'Count of changes.
    
    Set cat.ActiveConnection = CurrentProject.Connection
    'Loop through all tables.
    For Each tbl In cat.Tables
        lngAnswer = 0&
        If tbl.Type = "TABLE" Then  'Not views.
            strTable = tbl.Name     'Not system/temp tables.
            If Left(strTable, 4) <> "Msys" And Left(strTable, 1) <> "~" Then
                'Find the AutoNumber column.
                For Each col In tbl.Columns
                    If col.Properties("Autoincrement") Then
                        If col.Type = adInteger Then
                            'Is seed negative or below existing values?
                            lngOldSeed = col.Properties("Seed")
                            varMaxID = DMax("[" & col.Name & "]", "[" & strTable & "]")
                            If lngOldSeed < 0& Or lngOldSeed <= varMaxID Then
                                'Offer the next available value above 0.
                                lngNewSeed = Nz(varMaxID, 0) + 1&
                                If lngNewSeed < 1& Then
                                    lngNewSeed = 1&
                                End If
                                'Get confirmation before changing this table.
                                strMsg = "Table:" & vbTab & strTable & vbCrLf & _
                                    "Field:" & vbTab & col.Name & vbCrLf & _
                                    "Max:  " & vbTab & varMaxID & vbCrLf & _
                                    "Seed: " & vbTab & col.Properties("Seed") & _
                                    vbCrLf & vbCrLf & "Reset seed to " & lngNewSeed & "?"
                                lngAnswer = MsgBox(strMsg, vbYesNoCancel + vbQuestion, _
                                    "Alter the AutoNumber for this table?")
                                If lngAnswer = vbYes Then   'Set the value.
                                    col.Properties("Seed") = lngNewSeed
                                    lngKt = lngKt + 1&
                                    'Write a trail in the Immediate Window.
                                    Debug.Print strTable, col.Name, lngOldSeed, " => " & lngNewSeed
                                End If
                            End If
                        End If
                        Exit For 'Table can have only one AutoNumber.
                    End If
                Next    'Next column
            End If
        End If
        'If the user chose Cancel, no more tables.
        If lngAnswer = vbCancel Then
            Exit For
        End If
    Next    'Next table.
    
    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    AutoNumFix = lngKt
End Function

Other Causes

There are other cases where Access can be fooled into setting the Seed of the AutoNumber incorrectly.

These steps will reproduce the problem in Access 2000, 2002, or 2003, with all service packs applied:

  1. Create a table with an AutoNumber and one or more other fields. Save. Enter a half a dozen records.
  2. Create a form bound to this table.
  3. Create a subform bound to the same table, but without a text box for the AutoNumber field.
  4. Put the name of the AutoNumber field into the LinkMasterFields and LinkChildFields properties of the subform.
  5. Set the DataEntry property of the subform to Yes, so it goes to a new record.
  6. Open the main form to the first record, and try enter a record in the subform. The attempt will fail with a "Duplicate" error message.
  7. You are now unable to add any more records, even if you try to add them directly to the table.

Explanation

Subforms are designed to enter related records. The subform's foreign key field (named in LinkChildFields) therefore inherits the value of the main field's primary key (named in LinkMasterFields.) In the case above, this means that Access is trying to assign the existing primary key value from the first record to the new record being entered in the subform. Naturally that fails.

Unfortunately, the Seed of the AutoNumber also gets set to one more than the failed write. Since the Seed is below existing values, further attempt to add new records also fails, as Access tries to reassign existing values.

(Note that the Seed is reset only if the LinkChildFields contains the name of an object of type AccessField. The write still fails, but the Seed is not reset if LinkChildFields names an object of type Control, e.g. if there is a text box with that name in the subform.)

Solution

There is no valid reason for designing a main form and subform bound to the same table and linked on the AutoNumber field. Get rid of this bad design. Then use the code above to fix the problem with your AutoNumber so you can add new records to the table again.

Other scenarios?

There may also be other scenarios where JET is tricked into resetting the AutoNumber to an unusable value.

Related information

For code to examine a table and find out what the Seed of the AutoNumber is, see GetSeedAdox().

For code to reset the Seed for one table only, see: ResetSeed().


Home Index of tips Top