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 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.
Let’s now look at some of the more common data-definition operations from the perspective of ADOX.
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.
A Jet table in ADOX is created as follows:
Create a Table object.
Give it a name by assigning a string containing the name to the Table object’s Name property.
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.AppendItem
[,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.
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
.AppendItem
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
.DeleteItem
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.
DAO data type | ADOX data type |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
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.
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
The process for creating a table index is the same in ADOX as it is in DAO:
Create the index by creating an Index object and assigning a name to it.
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.
Append the index to the Table
object’s Indexes collection. The syntax of
the Append
method is:
TableObject
.Indexes.AppendIndex
[,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.
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 |
In DAO, primary keys are created by setting the Primary
property of the Index object to True
. In ADOX, we proceed as
follows:
Create a Key object.
Set its Type property to Primary using the adKeyPrimary
constant.
Append some columns to the Key object’s Columns collection.
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
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
.AppendName
,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!
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.
18.226.82.78