VBA/Excel/Access/Word/Access/Autonumber

Controlling Autonumber Fields

   <source lang="vb">

Sub CreateAutonumber()

   Dim cmd As ADODB.rumand
   Set cmd = New ADODB.rumand
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.rumandText = "CREATE TABLE Customers2 " & _
       "(CustomerID AUTOINCREMENT (100000,1), " & _
       "CompanyName TEXT (50), IntroDate DATETIME, " & _
       "CreditLimit CURRENCY DEFAULT 5000)"
   cmd.Execute

End Sub

</source>
   
  

Retrieve the last-assigned autonumber value

   <source lang="vb">

Sub LastAutonumber()

   Dim cmd As ADODB.rumand
   Dim rst As ADODB.Recordset
   Set cmd = New ADODB.rumand
   Set rst = New ADODB.Recordset
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.rumandText = "INSERT INTO Customers2 " & _
       "(CompanyName, IntroDate, CreditLimit) " & _
       "VALUES ("Test Company", #1/1/2007#, 100) "
   cmd.Execute
   rst.ActiveConnection = CurrentProject.Connection
   rst.Open ("SELECT @@Identity as LastCustomer FROM tblCustomers")

End Sub

</source>