CreateField Script - Microsoft Access

Overview¶

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.

Pitfalls¶

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
    Text
    Number
    Date
    YesNo
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
    td.Fields.Refresh
    On Error Resume Next
    td.Fields.Delete fieldName
    On Error GoTo 0
    td.Fields.Refresh
    
    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