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

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


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.

  • 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
    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.