Using ADOX to Manipulate Data Objects

The ADOX library has an object model that allows you to create database objects such as tables, indexes, and keys, as well as to control security, establish referential integrity in a database, and perform cascade updates and deletions. The Catalog object is at the top of the ADOX object model, with Tables, Groups, Users, Procedures, and Views collections. Please consult the online help for the complete ADOX object model.

Just as with the ADODB library, if you want to make use of the ADOX library in your Access solutions, you must add a reference. You can add references by selecting Tools References in the Visual Basic Editor. Figure 5-18 shows a reference to the ADOX library as part of the current project.

Figure 5-18

Let’s look at a few examples of what you can do with the ADOX library.

Creating a Table with ADOX

For starters, you can create new databases, tables, and other objects using the ADOX library. The following procedure is an example of how you can create a new table in an existing database.


Sub TestCreateTable()

    Dim catCatalog As ADOX.Catalog
    Dim tblSupplier As ADOX.Table

    Set catCatalog = New ADOX.Catalog
    Set catCatalog.ActiveConnection = CurrentProject.Connection

    ' Create and name the new table
    Set tblSupplier = New ADOX.Table
    With tblSupplier
        .Name = "tblSupplier"
        .Columns.Append "CompanyName", adVarWChar, 50
        .Columns.Append "CompanyPhone", adVarWChar, 12
    End With

    'append the new table to the database
    catCatalog.Tables.Append tblSupplier

    'release memory
    Set catCatalog.ActiveConnection = Nothing
    Set catCatalog = Nothing
    Set tblSupplier = Nothing

End Sub

First, a new Catalog object is declared and is assigned to the current open connection. Then, a new table called tblSupplier is created, and a name and new columns are assigned to it. The new table is then appended to the Catalog.Tables collection, which added it to the database. After running the preceding procedure, you can see (in Figure 5-19) that a new table called tblSupplier has been added to the database.

Figure 5-19

These and many other features can be manipulated using the ADOX library. This section is not meant to be exhaustive, but just to provide you with a few examples of how you might use the ADOX library.