[AccessD] Using CREATE TABLE scripts in Access?

Hi Chris
Just went thorough this last week
I should be simple but it aint. Took me days to get this sorted.
First you create a Number field then you go back and give it the AutoIncrement property
See code below - it does it all
Hope this helps

Darren


=============================================================
Private Sub cmdCreateAutoNumber_Click()
'Assume you already have a table named tblStudents.
'Assume the New autonumber field is to be called StudentID
'This Function actually creates firstly the StudentID field as Number 
'(As a long Int actually) Then changes the field's property to AutoIncrement
Call fCreateAutoNumberField("tblStudents", "StudentID")
End sub

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