Primary Key autonumber lost its position (1 Viewer)

[kevnaff]

Hello All.

I have a primary key that automatically updates when a new record is added or an existing record is duplicated. Today shortly after adding a new record, I received an unrecognised database format error message. After compacting and repairing the database, I went back in and attempted to add a new record, which quickly reminded me that the primary key value had been duplicated so it could not save the record. I went in to the back end that I had repaired and found the below entry in the MSysCompactError table.

I managed to recognise that when I was pressing the add record button. it was incrementing the autonumber primary key, but the value was around 29152, when infact the highest value autnnumber in my importequipment table was around 30921. Therefore it was incrementing to a number that already existed in the table. I managed to continuously add a record, enter some text, then undo, and add a new record. This allowed me to eventually push the autonumber up past 30921, so now everything is working OK.

Has anyone had issues like this before and may know what causes the autonumber to hop back in time?

Thanks

 

[theDBguy]

Hi. This is issue is not new and the usual fix is to do a C&R or reseed the Autonumber field. Make sure you don't do a C&R over a network connection. So, if you must C&R the BE file, download a local copy first, C&R that, and then replace the old BE with the newly compacted one.

 

[Pat Hartman]

Here''s the two relevant functions.

Both use ADO so they will require that you set a reference to the newest ADOX library you have. Go to any code module and open Tools. Choose the references item and search for the ADO library.

I never tried to convert these to DAO but if you don't want to add the ADO library, you can try it. Keep in mind that if your DAO objects are not yet disambiguated, adding this ADO reference will break all your DAO code if the Dim statements do not properly disambiguate them.

ALL database objects should be defined specifically. So:
Dim db as DAO.Database
Dim td as DAO.Tabledef
etc.

Function ResetSeed(strTable As String) As String
    'Purpose:   Reset the Seed of the AutoNumber, using ADOX.
    Dim strAutoNum As String    'Name of the autonumber column.
    Dim lngSeed As Long         'Current value of the Seed.
    Dim lngNext As Long         'Next unused value.
    Dim strSql As String
    Dim strResult As String
  
    lngSeed = GetSeedADOX(strTable, strAutoNum)
    If strAutoNum = vbNullString Then
        strResult = "AutoNumber not found."
    Else
        lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1
        If lngSeed = lngNext Then
            strResult = strAutoNum & " already correctly set to " & lngSeed & "."
        Else
            Debug.Print lngNext, lngSeed
            strSql = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"
            Debug.Print strSql
            CurrentProject.Connection.Execute strSql
            strResult = strAutoNum & " reset from " & lngSeed & " to " & lngNext
        End If
    End If
    ResetSeed = strResult
End Function

''--------------------------------------------------------------------------------
Function GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long
    'Purpose:   Read the Seed of the AutoNumber of a table.
    'Arguments: strTable the table to examine.
    '           strCol = the name of the field. If omited, the code finds it.
    'Return:    The seed value.
    Dim cat As New ADOX.Catalog 'Root object of ADOX.
    Dim tbl As ADOX.Table       'Each Table in Tables.
    Dim col As ADOX.Column      'Each Column in the Table.
  
    'Point the catalog to the current project's connection.
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
  
    'Loop through the columns to find the AutoNumber.
    For Each col In tbl.Columns
        If col.Properties("Autoincrement") Then
            strCol = "[" & col.Name & "]"
            GetSeedADOX = col.Properties("Seed")
            Exit For    'There can be only one AutoNum.
        End If
    Next
  
    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Function

I have run into two situations where the seed breaks.
1. When you have a form and a subform bound to the same table. Certain actions will reset the seed.
2. When you have an autonumber in a table but it is NOT the primary key. Certain actions will reset the seet.

It is hard to track down what makes the seed go bad. Use second function to find out what Access thinks the seed is. If it is OK, then you are probably OK but I would compact and repair and then watch it add a new record and make sure that the seed is still correct.

 

[Cotswold]

My guess is that you have some corruption and you will lose some records. Looking through your data
you may see some "data slip" where the contents of one record shift into another, or you see higher ASCII
characters in the table fields. If this is the case then you will be losing several records. You will need to delete
all bad records in order that the Autonumber field can be added without error. This may be one or two
possibly a dozen or so. After deleting, do a compact. Check if you can add new and if not repeat. It may be a
good idea to create a utility program to locate the bad records and allow them to be deleted. That would save
time in the future and would speed up returning the database for use.

I don't know if this will help but in the past I've created a utility program to check for bad records.
I used the following in a Query with the fields in the Table. It returns Zero or One. You can add this IIF() to operate on as many fields as you wish. Then create another query with all of the fields. If say you decide to check on say two fields; FieldNameErr1 and FieldNameErr2 then add those together and filter for <> 2. For instance you'd have FieldNameErr1 + FieldNameErr2 in the FIELD of the Query and <>2 in the FILTER. That should filter out and show any bad and Slipped-Records. You can then delete them, Compact and test. Repeat if necessary.

FieldNameErr: IIf(([ASCF1]>47 And [AscF1]<58) Or ([AscF1]>64 And [AscF1]<91) Or ([AscF1]>96 And [AscF1]<123) Or [AscF1]=32,1,0)

(The ASCF1 is Asc(Left([FieldfName1],1)) is in a previous Query containing the Table's Fields; if you test another field you will need to repeat for that field)

Whether you can locate which records are lost may take some time but could be possible from orphaned records.
This often doesn't have a happy ending. Maybe C&R on the BE more often and replace the FE which will also bloat,
as often as thought necessary. A RunTime version of the FE will also bloat.
If it happens again then I'd wonder about your network(s)