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

  • Thread starter Thread starter mr44
  • Start date Start date
mr44 said:
How do I change the starting value of autonumber ie other than 1(Access
2007)?
Click to expand...

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.
 
(...)
How do I change the starting value of autonumber ie other than 1(Access
2007)?
Click to expand...




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
 
Back
Top