Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



CreateField Script - Microsoft Access

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


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, _
Optional isRequired As Boolean = False, Optional defVal As Variant = Empty, Optional _
maxLength As Integer = -1, Optional allowZeroLengthString As Variant = Empty)

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

    If IsEmpty(allowZeroLengthString) Then
        allowZeroLengthString = Not isRequired
    End If

    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)
            fd.AllowZeroLength = allowZeroLengthString
            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.