7.8. Creating Schema Objects with ADOX

So far we've been working with the ADODB library, which is the library to use when you want to work with database data. The ADOX library is the one you use when you want to work with the database schema, such as tables, views (queries), indexes, and so on.

To implement ADOX, you need to add a reference to it. Open any code module by pressing Alt+F11, and select References from the Tools menu. The References dialog box is displayed. Locate and select ADO Ext. 2.7 for DDL and Security, and click OK.

7.8.1. The ADOX Object Model

As you can see from Figure 7-6, the ADOX model contains one top-level object, the Catalog object, which contains five collections: Tables, Groups, Users, Procedures, and Views.

Each of the Table, Index, and Column objects also has a standard ADO Properties collection, as shown in Figure 7-7

7.8.2. Working with Queries (Views)

Since ADO is part of Microsoft's UDA strategy, it was felt that the term query failed to adequately describe the mechanism for retrieving data from a source that could be almost anything. As described earlier, ADO can retrieve data from many different sources, not just the Jet database engine, so the term view was adopted in ADO to more accurately describe a view, or perspective, of the data, regardless of where it came from.

To simplify things while working with the Jet database engine, Microsoft has maintained the existing terminology by referring to them externally as queries; however, this nomenclature changes to views when working with external data sources such as the SQL Server.

If this seems a little confusing, don't worry. Just remember that ADO is a different object model that simply refers to the same objects in a different way, and by (sometimes) different names. When working with queries, just replace the DAO-specific keywords query and QueryDef with the new ADO keyword view. That's all you need to do.

Whenever you work with other data access objects, such as Recordset and Command objects, you can also use a View object to specify the SQL operation that should be used. Exactly how you do that is explained in the relevant sections on Recordset and Command objects.

As with DAO QueryDefs, you can also build ADO views in code.

7.8.2.1. Creating a View

The process of creating an ADO View is the same as in DAO. To create a view in ADO:

Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command

'Open the Catalog
cat.ActiveConnection = CurrentProject.Connection

'Create the Command object that represents the View
cmd.CommandText = "SELECT * FROM tblCustomers WHERE"

'Create the View
Cat.Views.Append "AllCustomers", cmd

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set cmd = Nothing

You might recall from the section on DAO QueryDefs that you could set the value of any parameters in code. In ADO, you set the parameter values in the Command object. We'll demonstrate this in the section Command Object.

7.8.2.2. Modifying a View

To modify an existing view, you can reassign its Command object.

'Create the Command object that represents the View
cmd.CommandText = "SELECT * FROM tblCustomers WHERE City = 'Boise'"

'Create the View
Cat.Views("AllCustomers").Command = cmd

7.8.2.3. Deleting a View

Deleting a View is simple. Just issue the Delete method against the Catalog object's Views collection.

cat.Views.Delete strViewName

7.8.3. Creating Tables and Columns

Let's replicate the design of the two invoicing system tables we created in Chapter 6. The basic procedure for creating a table in ADO is as follows:

The header table stores the basic high-level information about each invoice, such as the invoice number, date, and the customer ID. The following example demonstrates how to create a new table called tblInvoice and add four fields to it.

First, let's declare all the objects needed to create the table.

Public Sub CreateInvoiceTable()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    'Create and connect a Catalog object
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    On Error Resume Next

    'If the table already exists, delete it
    cat.Tables.Delete "tblInvoice"
    On Error Goto 0

'Create the table definition in memory
    Set tbl = New ADOX.Table
    tbl.Name = "tblInvoice"

At this point, you have created a new table object, but it only exists in memory. It won't become a permanent part of the database until you add it to the catalog object's collection. Before you do that, however, you need to add one or more columns (called fields in DAO) to the table, because you can't save a table that has no columns.

'Create the new columns
    tbl.Columns.Append "InvoiceNo", adVarChar, 10

    'The InvoiceNo column could also have been specified thus:
    'Dim col As ADOX.Column
    'Set col = New ADOX.Column
    'With col
    '    .Name = "InvoiceNo"
    '    .Type = adVarChar
    '    .DefinedSize = 10
    'End With
    '
    'tbl.Columns.Append col

    'Create the remaining columns
    tbl.Columns.Append "InvoiceDate" adDBDate
    tbl.Columns.Append "CustomerID" adInteger
    tbl.Columns.Append "Comments" adVarChar, 50

The columns have now been added to the table, but the table still needs to be added to the catalog's Tables collection to make it a permanent fixture. Once you've done that, you should refresh the Tables collection to ensure it is up-to-date, because in a multiuser application, the new table may not be immediately propagated to other users' collections until you do.

'Append the new table to the collection
    cat.Tables.Append tbl
    cat.Tables.Refresh

    'Clean up
    cat.ActiveConnection = Nothing
    Set tbl = Nothing
    Set cat = Nothing

Next, we need to create a table to store the invoice line items, including the product ID, the number of items sold, and their individual unit prices. Since the total invoice price and tax can be calculated at runtime, we won't violate normalization rules by creating fields for these items.

The following example creates a new table called tblInvItem, and adds five fields to it. It is based on the same basic procedure for creating tables, but includes an addition attribute definition, dbAutoIncrField, to create an AutoNumber field.

Public Sub CreateInvItemTable()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    'Create and connect the Catalog object
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    On Error Resume Next

    'If the table already exists, delete it
    cat.Tables.Delete "tblInvItem"
    On Error Goto 0

    'Create the table definition in memory
    Set tbl = New ADOX.Table
    tbl.Name = "tblInvoice"

    With tbl.Columns
        .Append "InvItemID", adInteger
        .Append "InvoiceNo", adVarChar, 10
        .Append "ProductID", adInteger
        .Append "Qty", adSmallInt
        .Append "UnitCost", adCurrency
    End With

Once you've appended a column to the table, you can set its Access-specific properties. For example, to make a column (in this case, the InvItemID column) the AutoNumber column, you must first set its ParentCatalog property, and then set its AutoIncrement property.

With tbl.Columns("InvItemID")
        .ParentCatalog = cat
        .Properties("AutoIncrement") = True
    End With

    'Append the new table to the collection
    cat.Tables.Append tbl
    cat.Tables.Refresh

    'Clean up
    cat.ActiveConnection = Nothing
    Set tbl = Nothing
    Set cat = Nothing

7.8.4. Creating Indexes

The basic procedure for creating an index is as follows:

Remember three things when creating indexes in ADO. First, not all providers support all index attributes. Check the provider's documentation for those it does support. Second, Jet databases do not support clustered indexes. Third, although you can give an index any name you like, when you create a primary key using the Access Table Designer, it will be automatically named PrimaryKey for Jetdatabases, and PK_tablename for SQL Server databases. Therefore, to maintain consistency, it is wise to give code-created primary keys the same name.

Let's create the primary key. The following sub creates a primary key index for the specified table, which can include multiple fields whose names are supplied in the ParamArray argument. In the case of our invoice tables, there will be only one field in each.

Public Sub CreatePKIndexes(strTableName As String, _
        ParamArray varPKColumns() As Variant)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim idx As ADOX.Index
    Dim varColumn As Variant

    'Create and connect the Catalog object
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    Set tbl = cat.Tables(strTableName)

    'Check if a Primary Key exists. If so, delete it.
    For Each idx In tbl.Indexes
        If idx.PrimaryKey Then
            tbl.Indexes.Delete idx.Name
        End If
    Next idx

    'Create a new primary key
    Set idx = New ADOX.Index
    With idx
        .Name = "PrimaryKey"
        .PrimaryKey = True
        .Unique = True
End With

At this point, the index exists in memory, and will remain so until it is added to the table's Indexes collection. But before you do that, you must add the columns that make up the key to the index's Columns collection and refresh the collection.

'Append the columns
    For Each varColumn In varPKColumns
        idx.Columns.Append varColumn
    Next varColumn

    'Append the index to the collection
    tbl.Indexes.Append idx
    tbl.Indexes.Refresh

    'Clean up
    Set cat.ActiveConnection = Nothing
    Set cat = Nothing
    Set tbl = Nothing
    Set idx = Nothing
End Sub

You should run the CreatePKIndexes procedure to define the indexes for both tblInvoice and tblInvItem tables.

Finally, relationships must be set up between the two tables.

7.8.5. Creating Relations

The basic procedure for creating an index is as follows:

The following code creates a foreign key relationship between the tblInvoice table and the tblProducts table.

Note that you can name a relationship any way you like, but when you create a relationship using the Relationships window, Access names the relationship according to the names of the tables involved. For example, if you were to create a relationship between tblInvoice and tblProducts, Access would name it tblInvoicetblProducts.

Dim cat As New ADOX.Catalog
Dim ky As New ADOX.Key

'Create and connect the Catalog object
cat.ActiveConnection = CurrentProject.Connection

'Define the foreign key
With ky
    .Name = "ProductID"
    .Type = adKeyForeign
    .RelatedTable = "tblProducts"
    .Columns.Append "ProductID"
    .Columns("ProductID").RelatedColumn = "ProductID"
    .UpdateRule = adRICascade
End With

'Append the foreign key
cat.Tables("tblInvoice").Keys.Append ky

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set ky = Nothing