Beginning Access™ 2007 VBA

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

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

Figure 5.18. Figure 5-18

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