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 - Current Revision

Page Revision: Fri, Sep 23, 2011, 7:25 PM


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.

  • As written, the script doesn't set the AllowZeroLength property of the field. Depending on your application, this could be problematic.

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-2022, Patrick Jasinski.