Microsoft Access Database Development With VBA

ADOX Topics: Creating a Table



To create a table in the ADOX library, declare an ADOX.Table variable and initialize it. After creating the table, call the Append() method of the Tables property of the Catalog class. After creating the table, to update the items of the Catalog object, call its Refresh() method. To show the new table in the Navigation Pane, call the RefreshDatabaseWindow() method of the Application class. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim catStudents As ADOX.Catalog
    Dim tblStudents As ADOX.Table
    Dim colFirstName As ADOX.Column
    Dim colLastName As ADOX.Column
    Set catStudents = New ADOX.Catalog
    catStudents.ActiveConnection = CurrentProject.Connection
    Set tblStudents = New ADOX.Table
    tblStudents.Name = "Students"
    . . . Continue creating the table
    catStudents.Tables.Append tblStudents
    Set tblStudents = Nothing
    Set catStudents = Nothing
End Sub


Here is an example that creates a table with two columns:

Private Sub cmdTableCreation_Click()
    Dim catEmployees As ADOX.Catalog
    Dim tblEmployees As ADOX.Table
    Dim colFullName As ADOX.Column
    Dim colIsMarried As ADOX.Column
    Set catEmployees = New ADOX.Catalog
    catEmployees.ActiveConnection = CurrentProject.Connection
    Set tblEmployees = New ADOX.Table
    tblEmployees.Name = "Employees"
    Set colFullName = New ADOX.Column
    colFullName.Name = "FullName"
    colFullName.Type = adVarWChar
    colFullName.DefinedSize = 40
    tblEmployees.Columns.Append colFullName
    Set colIsMarried = New ADOX.Column
    colIsMarried.Name = "Is Married?"
    colIsMarried.Type = adBoolean
    tblEmployees.Columns.Append colIsMarried
    catEmployees.Tables.Append tblEmployees
    MsgBox "A table named Employees has been created."

    Set colFullName = Nothing
    Set colIsMarried = Nothing
    Set tblEmployees = Nothing
    Set catEmployees = Nothing
End Sub

Home Copyright © 2013-2015, FunctionX, Inc. Home