How do I change the starting value of autonumber (Access 2007)?

[mr44]

How do I change the starting value of autonumber ie other than 1(Access 2007)?

 

[Arvin Meyer [MVP]]

How do I change the starting value of autonumber ie other than 1(Access
2007)?

Write an append query and add a record with an ID one lower than the number
you wish to start with:

INSERT INTO Suppliers ( SupplierID, CompanyName )
SELECT 99 AS Expr1, "Dummy Record" AS Expr2;

The example above will insert 99 and "Dummy Record" into the suppliers table
in the NorthWind sample database.

The add your first record which will number in at 100, and delete the dummy
record.

 

[Krzysztof Pozorek [MVP]]

(...)

How do I change the starting value of autonumber ie other than 1(Access
2007)?

In VBA code (module, immediate window) You may write something like this:
DoCmd.RunSQL "alter table Table1 alter column ID counter(X,1)"

X - starting value


You can also use the query:
alter table Table1 alter column ID counter(333,5)
Or the following function:
Public Sub ResetAutonumber(TableName As String, AutoNumberFieldName _
As String, Start As Long, Step As Variant)
Dim cnn As Object ,str As String

Set cnn = CurrentProject.Connection
str = "ALTER TABLE " & TableName & " ALTER COLUMN " & _
AutoNumberFieldName & " Identity (" & Start & "," & Step & ")"
cnn.Execute str
End Sub


K.P. MVP, Poland
www.access.vis.pl