Creating Autonumber Fields using Code

Creating Autonumber Fields using Code

If you ever want to create a table with an AutoNumber field as a Primary Key, there are two ways to go - SQL or DAO. I can't imagine why, after having created the database, there would be a need to create an extra table with an AutoNumber field, as it is usually better just to use Delete and Append queries on a table, but here's the two versions anyway:

Sub sCreate1()
    Dim db As Database
    Dim strSQL As String
    Set db = DBEngine(0)(0)
    strSQL = "CREATE TABLE [Table1New] (FieldPK COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, FieldOne TEXT, FieldTwo TEXT );"
    db.Execute strSQL
    Set db = Nothing
End Sub

Sub sCreate2()
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim idx As Index
    Set db = DBEngine(0)(0)
    Set tdf = db.CreateTableDef("Table2New")
    Set fld = tdf.CreateField("FieldPK", dbLong)
    fld.Attributes = dbAutoIncrField
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("FieldOne", dbText)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("FieldTwo", dbText)
    tdf.Fields.Append fld
    Set idx = tdf.CreateIndex("PrimaryKey")
    With idx
        .Name = "PrimaryKey"
        .Primary = True
    End With
    idx.CreateField ("FieldPK")
    Set fld = idx.CreateField("FieldPK")
    idx.Fields.Append fld
    tdf.Indexes.Append idx
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
    Set fld = Nothing
    Set idx = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Sub

Top

Last modified at 06/06/2006 15:01:16