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

CreateField Script - Microsoft Access

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

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

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