[AccessD] Using CREATE TABLE scripts in Access?

Are you sure you had to create the Number field first? I've never had to. 

Susan H. 

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
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com