Page History: CreateTable Script - Microsoft Access

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Fri, Sep 14, 2012, 2:26 PM

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.

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