CreateTable Script - Microsoft Access

Overview¶

When working with Microsoft Access databases in multiple environments (e.g., development versus production), if you ever need to create a table, it would be better to do it via a script rather than manually. This article outlines a means to write such a script.

Pitfalls¶

If the table exists beforehand, this script drops it and recreates. If any other tables depend on it by way of foreign keys, this could be problematic in your application.

Sample Usage¶

    Set td = CreateTableDef("Product", "ProductID")
    CreateField td, "Descrip", DataType.Text, True, "", 20, False
    . . .

Reusable Code¶

Public Function CreateTableDef(tableName As String, primaryKeyField As String) As TableDef

    Dim td As TableDef
    Dim fld As Field
    Dim pk As Index
    
    Set td = GetTableDef(tableName)

    With DBEngine.Workspaces(0).Databases(0)
    
        '--- Recreate table -----------------------------------------------------------------------
        If Not td Is Nothing Then
            .TableDefs.Delete tableName
        End If
        
        Set td = .CreateTableDef(tableName)
        
        '--- Create Primary Key Field -------------------------------------------------------------
        With td
            Set fld = .CreateField(primaryKeyField, DataTypeEnum.dbLong)
            fld.Attributes = fld.Attributes Or FieldAttributeEnum.dbAutoIncrField
            .Fields.Append fld
        End With
        
        .TableDefs.Append td
        
        '--- Create Primary Key -------------------------------------------------------------------
        With td

            Set pk = .CreateIndex("PK_" & tableName)

            With pk

                .Fields.Append .CreateField(primaryKeyField)
                .Primary = True

            End With

            .Indexes.Append pk

        End With
        
    End With
    
    Set CreateTableDef = td

End Function