Zero the ID key

[ptaylor-west]

I have cleansed all records from my database and starting with fresh data but the ID key starts at 338 which is the next number after amount of records I deleted - how can I zero this?

 

[JANR]

Compact and repair will reset autonumber seed's on empty tables

JR

 

[Atomic Shrimp]

You can do it by deleting the field, then creating it again (you might need to save the table structure in between, to force it to cleanse the field definition), but ... does it really matter if the autonumber begins at 338?

 

[Galaxiom]

For the sake of completness. To restart autonumbering on an existing table without loosing the data.

Copy the table and paste. Choose structure only.
Append the records (except the autonumber field) from the old table to the new. Delete the old and rename the new.

Whatever you do don't rename the old table. This will cause Access to track the name change through queries built on the original table.

 

[allan57]

'1 - Delete Table contents first
'2 - Compact Database
'3 - Store this function in a module and call via debug window e.g.

ReSetAutoNumber "YourTableNameHere", "AutoNumberField", 0
This will restart the autonumber field at 0

ReSetAutoNumber "YourTableNameHere", "AutoNumberField", 2000
This will restart the autonumber field at 2000

Function ReSetAutoNumber(strTableName As String, strAutoNumberField As String, lngStartNumber As Long)

On Error GoTo Error_Handle

    DoCmd.RunSQL ("INSERT INTO [" & strTableName & "] ([" & strAutoNumberField & "]) VALUES(" & lngStartNumber - 1 & ");")

    DoCmd.RunSQL ("DELETE * FROM [" & strTableName & "];")

ErrorExit:
    
    On Error Resume Next
    
    Exit Function

Error_Handle:
    
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    
    Resume ErrorExit

End Function

 

[ptaylor-west]

Thanks to JANR - ideal for my empty database, although the other replies were also useful.