Page History: CreateField Script - Microsoft Access

Compare Page Revisions

3 2 1 0 Current 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Mon, Sep 19, 2011, 10:10 AM

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

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

    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)
            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