Chapter 18. ADOX: Jet Data Definition in ADO

ADOX is an acronym for ADO Extensions for Data Definition and Security. When making comparisons between ActiveX Data Objects (ADO) and Data Access Objects (DAO), proponents of DAO will point out that ADO does not include features for data definition—that is, features that can be used to create and alter databases and their components (tables, columns, indexes, etc.). This is precisely the purpose of ADOX, but not just in the context of Jet databases. ADOX is intended to be a universal data-definition object model. Of course, as with ADO, it requires support from OLE DB data providers. Our concern is with ADOX in relation to Jet.

I plan to discuss the role of ADOX in various data definition operations, such as creating a Jet database and creating and altering Jet database tables.

It is worth mentioning that ADOX is not a complete substitute for DAO’s data-definition features. For example, query creation in ADOX has a serious wrinkle (at least for Access 2000). Namely, a query created using ADOX will not appear in the Access 2000 user interface! We will revisit this issue later in this chapter.

The ADOX Object Model

The ADOX object model is shown in Figure 18-1. The model has 9 object pairs (object/collection), about 75 properties, and about 50 methods—not a very large object model as Microsoft object models go (and smaller than the ADO object model). Unfortunately, the ADOX help documentation is among Microsoft’s worst, which is saying a lot.

The ADOX object model
Figure 18-1. The ADOX object model

Let’s now look at some of the more common data-definition operations from the perspective of ADOX.

Creating a Database

To create a Jet database, use the Create method of the Catalog object. Its syntax is:

CatObject.Create(ConnectString)

where ConnectString is a connection string that must also define the database to be created.

The following code creates a new Jet database:

Sub CreateDatabase(  )

Dim cat As New Catalog

' Must use version 3.51 of data provider in order
' to create a database compatible with Access 97.
' If this is not required, can use version 4.0.
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:	empADOXExample.mdb"

End Sub

Note that if the database already exists, an error will be generated. Incidentally, the ADOX documentation says: “The Create method creates and opens a new ADO Connection to the data source specified in ConnectString.” This seems to imply that the data source must already exist, which is, of course, not the case.

Creating Tables

A Jet table in ADOX is created as follows:

  1. Create a Table object.

  2. Give it a name by assigning a string containing the name to the Table object’s Name property.

  3. Append some columns to the Table object’s Columns collection. Do this by calling the Append method of the Column collection. Its syntax is:

    TableObject.Columns.Append Item[, Type[, DefinedSize]]

    where Item is either a Column object or the string containing the name of the column. The remaining two parameters are optional if Item is a Column object that contains all column information. Type is an optional Long or a member of the DataTypeEnum enumeration (the default is adVarWChar), and DefinedSize is an optional Long that determines the column size.

  4. Append the Table object to the Catalog object’s Tables collection. Do this by calling the Append method of the Tables collection. Its syntax is:

    TablesObject.Append Item

    where Item is the Table object added to the collection.

Here is a sample:

Sub CreateTable(  )

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

' Open catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:	empADOXExample.mdb"

' Assign table name and some columns
With tbl
   .Name = "NewTable"
   .Columns.Append "Column1", adVarWChar, 250
   .Columns.Append "Column2", adInteger
   .Columns.Append "Column3", adInteger
End With

cat.Tables.Append tbl

End Sub

To rename a column, we use the Name property of the Column object. To delete a column, we use the Delete method of the Columns collection. Its syntax is:

ColumnsObject.Delete Item

where Item is a string containing the name of the column to delete. Here is an example:

Sub ChangeColumn(  )

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:	empADOXExample.mdb"

' Assign table name and some columns
Set tbl = cat.Tables("Newtable")

' Rename a column
tbl.Columns("Column2").Name = "Column2X"

' Delete a column
tbl.Columns.Delete "Column3"

End Sub

You may have noticed the use of the data type constant adVarWChar to create a string column. Table 18-1 compares the field data type constants of DAO and ADOX.

Table 18-1. DAO/ADOX field data type constants

DAO data type

ADOX data type

dbBinary

adBinary

dbBoolean

adBoolean

dbByte

adUnsignedTinyInt

dbCurrency

adCurrency

dbDate

adDate

dbDecimal

adNumeric

dbDouble

adDouble

dbGUID

adGUID

dbInteger

adSmallInt

dbLong

adInteger

dbLongBinary

adLongVarBinary

dbMemo

adLongVarWChar

dbSingle

adSingle

dbText

adVarWChar

The Tables Collection

Unlike DAO, the ADO Tables collection may contain objects other than Jet tables. For example, the Tables collection contains row-returning, nonparameterized queries (which are considered Views by ADO).

To determine the actual type of a Table object, we can use the Type property. Table 18-2 lists the possible values for the Type property (in the context of the Jet Data Provider). Note that the Type property is read-only and returns a string.

Table 18-2. Return values of the Table object’s Type property

Type property returns

Description

ACCESS TABLE

A Microsoft Access system table

LINK

A linked table from a non-ODBC data source

PASS-THROUGH

A linked table from an ODBC data source

SYSTEM TABLE

A Microsoft Jet system table

TABLE

A Jet table

VIEW

A row-returning, nonparameterized query

For instance, the following code:

Sub ListTables(  )

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:	empADOXExample.mdb;"

For Each tbl In cat.Tables
   Debug.Print tbl.Name, tbl.Type
Next
End Sub

may produce the following output:

MSysAccessObjects         ACCESS TABLE
MSysACEs                  SYSTEM TABLE
MSysObjects               SYSTEM TABLE
MSysQueries               SYSTEM TABLE
MSysRelationships         SYSTEM TABLE
NewQuery                  VIEW
NewTable                  TABLE

Creating Indexes

The process for creating a table index is the same in ADOX as it is in DAO:

  1. Create the index by creating an Index object and assigning a name to it.

  2. Append columns one by one to the Index object’s Columns collection. Call the Columns collection’s Append method; its syntax was discussed in Section 18.1.3 earlier in this chapter.

  3. Append the index to the Table object’s Indexes collection. The syntax of the Append method is:

    TableObject.Indexes.Append Index[, Columns]

    where Index is the Index object to be appended or a string containing the name of the index to create, and Columns is an optional variant specifying the columns to be indexed.

Here is an example:

Sub ADOCreateIndex(  )

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim idx As New ADOX.Index

' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:	empADOXExample.mdb;"

Set tbl = cat.Tables("Newtable")

' Create Index object and append table column to it
idx.Name = "Newindex"
idx.Columns.Append "Column1"

' Allow Null values
idx.IndexNulls = adIndexNullsAllow

' Append the Index object to the table's Indexes collection
tbl.Indexes.Append idx

End Sub

The DAO Index object has two properties that determine the behavior of Nulls within an index: Required and IgnoreNulls. Both of these properties are False by default, implying that Null values are allowed in the index and that an index entry is added for each row with a Null value in the index field.

On the other hand, ADO has a single property, called IndexNulls, that governs the behavior of Null values in indexes. By default, the IndexNullsproperty is set to adIndexNullsDisallow, implying that Null values are not allowed in the index and that no index entry will be added if a field contains Null. Table 18-3 compares the relevant settings in DAO and ADOX.

Table 18-3. Comparison of constants for treating nulls

DAORequired

DAO IgnoreNulls

ADOX IndexNulls

Description

True

False

adIndexNullsDisallow

Null value not allowed in index field; no index entry added

False

True

adIndexNullsIgnore

Null value allowed in index field; no index entry added

False

False

adIndexNullsAllow

Null value allowed in index field; index entry added

Creating a Primary Key

In DAO, primary keys are created by setting the Primary property of the Index object to True. In ADOX, we proceed as follows:

  1. Create a Key object.

  2. Set its Type property to Primary using the adKeyPrimary constant.

  3. Append some columns to the Key object’s Columns collection.

  4. Append the Key object to the Index object’s Keys collection.

Here is an example:

Sub ADOCreatePrimaryKey(  )

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim pk As New ADOX.Key

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:	empADOXExample.mdb;"

Set tbl = cat.Tables("Newtable")

' Create the Primary Key
pk.Name = "PrimaryKey"
pk.Type = adKeyPrimary
pk.Columns.Append "Column1"

' Append the Key object to the Keys collection of Table
tbl.Keys.Append pk

End Sub

Creating a Query

To create a query, we use the ADO Command object to create a new ADO command. This can be appended to the Views (or Procedures) collection of the catalog to create a new query. Its syntax is:

ViewsObj.Append Name, Command

where Name is a string containing the name of the object, and Command is a Command object.

Here is an example:

Sub CreateQuery(  )

Dim cat As New ADOX.Catalog
Dim cd As New ADODB.Command
Dim sSQL As String

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=d:	empADOXExample.mdb;"

sSQL = "SELECT * FROM Newtable"

cd.CommandText = sSQL

cat.Views.Append "Newquery", cd

End Sub

Now, the bad news. Here is a quotation from MSDN:

Although it is possible to create and modify a stored query in an Access database by using Microsoft ActiveX® Data Objects Extensions for Data Definition Language and Security (ADOX), if you do so your query won’t be visible in the Access Database window or in any other part of the Access user interface...

The reason behind this invisibility is explained further in MSDN:

This is so because the Microsoft Jet 4.0 database engine can run in two modes: one mode that supports the same Jet SQL commands used in previous versions of Access, a new mode that supports new Jet SQL commands and syntax that are more compliant with the ANSI SQL-92 standard.

Queries created with ADOX can support the new Jet SQL mode, and so are flagged internally to identify them as using that mode, whether the query contains the new commands or not. Access 2000 can open an Access database only while using the mode that supports the older Jet SQL commands and syntax. To prevent error messages and conflicts between the new Jet SQL commands and syntax and the Access query editing tools, Access hides queries that are flagged as containing the new Jet SQL commands and syntax.

An interesting thing happens with Access 2002. Access 2002 does see the query, probably because it can handle the newer Jet SQL syntax; however, the Design view of the query does not show the output fields of the query!

Conclusion

I wish Microsoft would continue to support DAO. It is well understood, easy to use, does what is necessary, seems quite stable, and is optimized for Jet.

Sony supports a variety of TVs; General Motors supports a variety of cars; General Electric supports a variety of refrigerators; so why can’t Microsoft support two types of data access? Imagine General Motors saying: “We make only one model of car, but it is designed to be universal. Whether you want a convertible sports car, or a car to haul around ten sheets of plywood, or a car to race on weekends, or a car to do off-roading, this is the car for you.” Ridiculous.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.82.78