ACCESS FEED: RE: [MS_AccessPros] Auto number mystery

Senin, 27 Januari 2014

RE: [MS_AccessPros] Auto number mystery

Hi Robin

Somehow the “seed” of your table has got messed up. This is the value stored in the table which is assigned to the next record that is created. It appears that this seed has been changed to the value of an existing record, so a new record with this value cannot be saved because it is a unique/primary key.

This could happen because of some kind of corruption, or it could be that an INSERT INTO query has been executed that adds a record with an explicit value for the autonumber field that fills a gap in the sequence.

Either way, the first thing to try is a compact/repair. Then open the table and start to enter a new record. The autonumber should be one greater than the current maximum value.

If the compact/repair does not work, then more serious artillery is needed. Copy the code below and paste it into a module:
[code]
Public Function ResetAutonumber( _
    sTable As String, _
    Optional sField As String, _
    Optional lSeed As Long _
    ) As Long
'sTable = Table containing autonumber field
'sField = Name of the autonumber field
'         (default is the first Autonumber field found in the table)
'lSeed = Long integer value you want to use for next AutoNumber
'         (default is one more than the current maximum)
Dim cnn As Object 'ADODB.Connection
Dim cat As Object 'ADOX.Catalog
Dim col As Object 'ADOX.Column
Dim tbl As Object 'ADOX.Table
Dim sRemoteTable As String
On Error GoTo ProcErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTable)
If tbl.Type = "LINK" Then
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open _
      CurrentProject.Connection.ConnectionString & ";" & _
      "Data Source=" & _
          tbl.Properties("Jet OLEDB:Link Datasource")
    cat.ActiveConnection = cnn
    sRemoteTable = tbl.Properties("Jet OLEDB:Remote Table Name")
    Set tbl = cat.Tables(sRemoteTable)
End If
If Len(sField) = 0 Then
    For Each col In tbl.Columns
      If col.Properties("AutoIncrement") Then
        sField = col.Name
        Exit For
      End If
    Next
    If Len(sField) = 0 Then GoTo ProcEnd
Else
    Set col = tbl.Columns(sField)
End If
If lSeed = 0 Then
    lSeed = Nz(DMax(sField, sTable), 0) + 1
End If
col.Properties("Seed") = lSeed
ResetAutonumber = lSeed
ProcEnd:
On Error Resume Next
If Not cnn Is Nothing Then
    cnn.Close
    Set cnn = Nothing
End If
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function
[/code]

Then, in the Immediate window, type:

?ResetAutonumber(“YourTableName”) <enter>

The response should be the correct “next available” number.

Best wishes,
Graham Mandeno
[Access MVP 1996-2014]

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Monday, 27 January 2014 18:44
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Auto number mystery

I am using Access 2013 on a Win7 system.

I have a membership table with the MemberID field as an auto number.
There are 9370 records and today when attempting to add a new record
MemberID=5260 was allocated which created a record which could not be saved.

A second attempt allocated MemberID=5261 and the third attempt was
MemberID=5262.

What have I broken?

Many thanks,

Robin Chapple