VBA/Excel/Access/Word/Access/Autonumber

Controlling Autonumber Fields

 
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

Retrieve the last-assigned autonumber value

 
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