Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



Page History: CreateField Script - Microsoft Access

Compare Page Revisions

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Mon, Sep 19, 2011, 10:17 AM


When working with Microsoft Access databases in multiple environments (e.g., development versus production), if you ever need to add fields to 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.


Microsoft Access allows a maximum of 256 columns per database table. However, when a field is deleted (e.g., via this script), it still counts toward that maximum. To work around this, compact the database, which rebuilds the table column counts.

Sample Usage

    Dim td As TableDef
    Set td = GetTableDef
    CreateField td, "Field1", DataType.Text, True, "", 50
    CreateField td, "Field2", DataType.Number, True, 2
    . . .
    CreateField GetTableDef("Table2"), "Field3", DataType.Text, True, "", 50
    . . .
    Set td = GetTableDef("Table3")
    CreateField td, "Field4", DataType.Text, True, "", 50
    CreateField td, "Field5", DataType.Text, True, "", 50
    . . .

Reusable Code

Public Enum DataType
End Enum
Public Sub CreateField(td As TableDef, fieldName As String, fieldType As DataType, _
isRequired As Boolean, Optional defVal As Variant = Empty, Optional maxLength As Integer = -1)

    Debug.Print "Creating [" & fieldName & "]..."

    Dim fd As Field
    On Error Resume Next
    td.Fields.Delete fieldName
    On Error GoTo 0
    Select Case fieldType
        Case DataType.Date
            Set fd = td.CreateField(fieldName, DataTypeEnum.dbDate)
        Case DataType.Number
            Set fd = td.CreateField(fieldName, DataTypeEnum.dbLong)
        Case DataType.Text
            Set fd = td.CreateField(fieldName, DataTypeEnum.dbText, maxLength)
            defVal = """" & defVal & """"
        Case DataType.YesNo
            Set fd = td.CreateField(fieldName, DataTypeEnum.dbBoolean)
    End Select
    fd.Required = isRequired
    fd.DefaultValue = defVal
    td.Fields.Append fd

End Sub
Public Function GetTableDef(Optional tableName As String = "MyDefaultTableName") As TableDef

    Set GetTableDef = DBEngine.Workspaces(0).Databases(0).TableDefs(tableName)

End Function

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