Tables: Creating an AutoNumber field from code

There are two methods to create an AutoNumber field from code.  One requires you to run a SQL DDL "Create Table" statement, and the other uses VBA to append dbAutoIncrField flag to a new field's Attributes property.

To create the field using SQL DDL statements, refer to this Knowledge Base article:

Article ID 116145 ACC: Create and Drop Tables and Relationships Using SQL DDL

To create the field using VBA and DAO,  you can use this function.

'  ********* Code Start ***********
' This code was originally written by Dev Ashish
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fCreateAutoNumberField( _
                ByVal strTableName As String, _
                ByVal strFieldName As String) _
                As Boolean
'
'   Creates an Autonumber field with name=strFieldName
'   in table strTableName.
'   Accepts
'       strTableName:   Name of table in which to create the field
'       strFieldName:    Name of the new field
'   Returns True on success, false otherwise
'

On Error GoTo ErrHandler
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef

    Set db = Application.CurrentDb
    Set tdf = db.TableDefs(strTableName)
    '   First create a field with datatype = Long Integer
    Set fld = tdf.CreateField(strFieldName, dbLong)
    With fld
        '   Appending dbAutoIncrField to Attributes
        '   tells Jet that it's an Autonumber field
        .Attributes = .Attributes Or dbAutoIncrField
    End With
    With tdf.Fields
        .Append fld
        .Refresh
    End With
    
    fCreateAutoNumberField = True
    
ExitHere:
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
ErrHandler:
    fCreateAutoNumberField = False
    With Err
        MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "CreateAutonumberField"
    End With
    Resume ExitHere
End Function
'  ********* Code End ***********