Resetting an AutoNumber field, and starting from different values

Resetting an AutoNumber field, and starting from different values

Resetting

If you wish to reset an AutoNumber field back to the original starting value, for example you have been entering test data into your application, and wish to clean it up before deploying it, then you need to do the following:

Delete all of the data from the table;
Compact the database.

Setting the start number

If you wish to control the number at which an AutoNumber field starts (and the only good reason that I can think of is that you are deploying the application in various locations that will not be synchronized regularly), then you will need to run some code like this:

Sub sSetAutoNumber(strTableName As String, strPKField As String, lngStartNumber As Long)
    On Error GoTo E_Handle
    Dim db As Database
    Set db = DBEngine(0)(0)
    db.Execute "INSERT INTO [" & strTableName & "] ([" & strPKField & "]) VALUES(" & lngStartNumber - 1 & ");"
    db.Execute "DELETE * FROM [" & strTableName & "];"
sExit:
    On Error Resume Next
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

You could then call this procedure like:

Call sSetAutoNumber("tblCustomer","CustomerID",1000)

Which would set the AutoNumber to start at 1000.

Top

Last modified at 06/06/2006 15:01:40