Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

CreateTable Script - Microsoft Access

RSS
Modified on Fri, Sep 14, 2012, 2:28 PM by Administrator Categorized as Microsoft Office

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.