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