Sub ChangeAutoNumber()
Dim conn As ADODB.Connection
Dim strDb As String
Dim strConnect As String
Dim strTable As String
Dim strCol As String
Dim intSeed As Integer
On Error GoTo ErrorHandler
strDb = CurrentProject.Path & "\" & "mydb.mdb"
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDb
strTable = "myTable"
strCol = "Id"
intSeed = 1000
Set conn = New ADODB.Connection
conn.Open strConnect
conn.Execute "ALTER TABLE " & strTable & " ALTER COLUMN " & strCol & " COUNTER (" & intSeed & ");"
ExitHere:
conn.Close
Set conn = Nothing
Exit Sub
ErrorHandler:
If Err.Number = -2147467259 Then
Debug.Print "The database file cannot be located.", _
vbCritical, strDb
Exit Sub
Else
Debug.Print Err.Number & ":" & Err.Description
Resume ExitHere
End If
End Sub