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

Page History: CreateTable Script - Microsoft Access

Compare Page Revisions



« 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

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