Using Procedures to Change a Database Structure

Up to this point, we have been using procedures to look at data. In order to accomplish that, we have used objects from the ADODB library to connect to the database. ADODB is part of a larger library called ADO (ActiveX Database Objects). ADO will be discussed in greater detail in the course of this book. However, in order to accomplish anything, we need to touch on certain aspects of it.

Another part of ADO is the ADOX library. Briefly, objects from this library have the task of handling structural issues of the database. For instance, you would use the ADOX objects to construct and analyze the structure of a table. Let’s try a simple example to demonstrate the use of these objects.

Constructing a Table

Within the ADOX library, there are several important objects:

• The Catalog object looks at the structures of the objects within the database. An important property of this object is ActiveConnection. This will tell the Catalog object what database structure you are talking about. Think of this object as a giant container that will hold the objects (tables, reports, forms, queries, etc.) of your database. Within this container, you would have a collection of objects for Tables.

• The Table object, within the Catalog container, contains the columns, fields, indexes, keys, and so on.

• Within the Table object is the Column object, which contains the properties of name, type, attributes, size, and numeric precision.

• The Table object also contains the Index and Key objects. The Index object contains the properties of name, indexnull, primarykey, unique, and clustered. The Key object contains the properties of relatedtable, deleterule, updaterule, and type.

Let’s build a simple table using the ADOX objects.

Sub makeTable()   Dim currCat As New ADOX.Catalog   Dim newTable As New ADOX.Table   Dim newKey As New ADOX.Key     currCat.ActiveConnection = CurrentProject.Connection     With newTable   .Name = "tblTestTable"   .Columns.Append "custNumber", adInteger   .Columns("custNumber").ParentCatalog = currCat   .Columns("custNumber").Properties("AutoIncrement") = True     newKey.Name = "PrimaryKey"   newKey.Columns.Append "custNumber"   .Keys.Append newKey, adKeyPrimary     .Columns.Append "custFirstName", adWChar   .Columns.Append "custLastName", adWChar   End With     currCat.Tables.Append newTable     Set currCat = Nothing End Sub 

Notice that in this code you created a new instance of the Catalog object called currcat (the object reference). Then, within that, you created a new instance of the Table object with the reference of newTable. Finally, you created a new instance of the Key object with reference of newKey.

Past that, you use a structure that we have not examined called With. The With structure allows you to reference the object once using the With keyword. From there on, all you need to do is type the dot (the period key) and select the object’s member on a line-by-line basis. This has the potential of saving you a lot of typing as well as offering improved performance.

You would use the Name property to give the table a name.

In this little example, there is a key field. The sequence of events is interesting. You must first use the Append property to create the column, give it a name, and indicate the data type.

Since it is going to be a primary key, you need to attach it to the Catalog object using the ParentCatalog property. Finally, you set the Boolean value of the AutoIncrement property to True.

Just making the column an auto-incrementing column does not make it a primary key automatically. You now need to attach the Key object to the column by creating a reference to it and then appending that reference to the column. Finally, you indicate that it is the primary key with the adKeyPrimary constant.

The next two lines add two text columns to the table: custFirstName and custLastName. Notice that they each have a data type of adWChar. If this sounds cryptic, it is an ADO equivalent of the Text data type in Access.

Recall from the previous chapter that these are called intrinsic constants and, as such, have an identifier prefix. Since these are within ADO, their identifier is ad.

Table 9-1 gives you the conversions for these types.

Table 9-1: ADO Equivalents to Access Data Types

Microsoft Access Data Type	ADO Equivalent
Binary	adBinary
Boolean	adBoolean
Byte	adUnsignedTinyInt
Currency	adCurrency
Date	adDate
Numeric	adNumeric
Double	adDouble
Small Integer	adSmallInt
Integer	adInteger
Long Binary	adLongBinary
Memo	adLongVarWChar
Single	adSingle
Text	adWChar

Finally, after the With structure has ended, you need to append the table to the catalog that contains the structure for the database. In other words, this last part actually takes the constructed table and adds it to the database.

If you now go to the Database window, you will see the new table listed, as shown in Figure 9-1. If you do not see it, it should appear after you select the Refresh option from the View menu, or press F5.

Figure 9-1: The new table in the Database window

If you look at the structure, it should be as we defined it, as shown in Figure 9-2.

Figure 9-2: Structure of the new table