Access 2000 is the first version of Access that lets developers set the start and step value of autoincrement columns. You can programmatically set the start and step value of autoincrement columns by creating the table with Jet 4.0 SQL statements. You use the CREATE TABLE command to create the overall table, and you use the IDENTITY data type in Jet SQL for the autoincrement field. The IDENTITY data type has start and step values that let you specify the initial value for the autoincrement field, as well as how much it increases with each new record. The following SetStartAndStep procedure taps this new technology.
Sub SetStartAndStep()
Dim cnn1 As Connection
Dim cmd1 As Command
Dim tbl1 As New Table
Set cnn1 = CurrentProject.Connection
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
'First create a table with two columns.
'Make one column an Identity column.
'Set its start value first, and its step value second.
.CommandType = adCmdText
.CommandText = "CREATE TABLE Contacts (ContactID " & _
"IDENTITY(2,4),ContactName Char)"
.Execute
'After creating the table with the autoincrement/identity
'column, you should add data.
.CommandText = "INSERT INTO Contacts(ContactName) " & _
"Values ('Kevin Mineweaser')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO Contacts(ContactName) " & _
"Values ('Mike Gilbert')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO Contacts(ContactName) " & _
"Values ('Neil Charney')"
.CommandType = adCmdText
.Execute
End With