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.