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

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