Auto Number has lost it's position (1 Viewer)

[kevnaff]

Hi All.

I have copied a table in my database with the structure of it staying the same. I have then appended data from another table in to this new table.

Now when trying to add a new record I receive a message that this would create duplicate values in the index key.

If I was to create a new record right now, the primary key auto number would be 17005, where as the appended data has primary key values that go up to 17536.

Is there any way to set the auto number start point so that it would start at 17537?

I could manually enter a new record 500 times and then exit back out of the form. But I'm guessing there must be a simpler way.

Thanks

 

[Gasman]

A quick Google would show you how?

reset autonumber in access - Google Search

Admittedly, I have only reset it back to 1, but you should be able to select your starting number.

reset autonumber field in vba access - Google Search

I found what I used, you can amend to suit.

Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub

Do look at this code carefully, as it suited my needs. Likely you will not want all the lines of code?

 

[isladogs]

Try compacting the database. The ID for the next record should then be 1 more than the last saved record

 

[ebs17]

Is there any way to set the auto number start point so that it would start at 17537?

ALTER TABLE YourTable ALTER COLUMN YourField COUNTER(17537,1)

 

[Cotswold]

In this situation I would be concerned that there may be corruption in the table in question. Look for record-slip, where the contents of one record are mixed with another. Also, what some people call hieroglyphics in a few or many records. Which could account for the error.
Rather than relying in the system number, I'd use a field with a new number in the sequence set by your code when adding a new record.

 

[Pat Hartman]

Here is code for THREE functions. You want to use the ResetSeed() version. I've included the ChangeSeed() as an option. The GetSeedADOX() is called by ResetSeed()

When you add this code, you MUST add a reference to ADO Ext. 2.8 for DDL and Security library

And WARNING! WARNING! WARNING! If you have not disambiguated any DAO code you have in the app, do that first. ALL DAO objects need to be defined correctly otherwise there will be conflicts with objects in the ADO library.
For example
Dim db as DAO.Database
Dim td as DAO.TableDef
etc.
frequently you will see the shortcut:
Dim db as Database
This will ONLY work if there is code for ONLY ADO or ONLY DAO but not both.

Option Compare Database
Option Explicit

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean  ''' use the ResetSeed function.  it finds the new seed.
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'LngSeed = Long Integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn
Set col = cat.Tables(strTbl).Columns(strCol)
col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("Seed") = lngSeed Then
    ChangeSeed = True
Else
    ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End Function

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 = " & lngNext, "lngSeed = "; lngSeed
            'strSQL = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"
            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.
    
    ''' requires reference to ADO Ext. 2.8 for DDL and Security library
    
    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