June 1999

Ease Data Access With ADOX

by Jeffrey P. McManus

Reprinted with permission from Visual Basic Programmer's Journal, June 1999, Volume 9, Issue 6, Copyright 1999, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange at www.devx.com.

What you need: ActiveX Data Objects (ADO) 2.1 Visual Basic 5.0 or 6.0

We've had a DDL-dialect-independent technique for accessing relational database structures for years now: Data Access Objects (DAO). DAO gives you access to the structure of the database through its Database, TableDef, and QueryDef objects. Remote Data Objects (RDO) also gives you limited object-based access to database structures. When ADO came along, developers were promised a single, unified API to all kinds of data sources. This ambitious initiative still hasn't been completely fulfilled. In its initial versions, ADO was particularly deficient in programmatically browsing and manipulating database structures.

	What's New in ADO 2.1
	The version number indicates that ADO 2.1 is a minor release, but it actually has a bunch of new features and several refinements to features that lacked polish in previous ADO releases. In addition to the ADOX library for database definition and security, discussed in the main body of this article, the ADO library gains a number of new features in version 2.1. See entire sidebar.

In previous versions of ADO, you could create tables using DDL commands and the Command object, and you still can. However, this technique isn't object-based, so it can be difficult to pull off elegantly. And what happens if you want to change database platforms, or target both Microsoft SQL Server and Oracle? Ideally, you shouldn't have to rewrite your entire application to accommodate this.

ADO: Not the Best Way to Browse
Through the OpenSchema method of its Connection object, ADO 2.0 gives you a way to browse part of the structure of a database. But OpenSchema is clunky and lightweight.

For example, say you're looking to do something simple, such as display a list of tables contained in a database. OpenSchema returns a familiar ADO Recordset object, so to use it, you create a connection to your database, then set the return value of the OpenSchema method to a Recordset object:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

' Open connection to database.
Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = "e:\data\mydata.mdb"
cn.Open
    
' Get schema recordset.
Set rs = cn.OpenSchema(adSchemaTables)

So far, so good. You now have a Recordset object containing a bunch of data. Presumably it contains schema information for the tables in the database, but what's the structure of the Recordset object itself? (Hmm … maybe you should print this bad boy out on the laser printer when nobody's looking.)

In order to decipher the schema data, you need additional information, known as metadata, that describes what data the OpenSchema method returned. OpenSchema doesn't return metadata for its own recordset, but you can inspect the structure of the Recordset object that's returned by OpenSchema by iterating through its Fields collection. You do this by running code similar to this:

Private Sub ShowFields(rs As Recordset)
Dim Field As ADODB.Field

   For Each Field In rs.Fields
      Debug.Print Field.Name
       rs.MoveNext
   Next   
End Sub

Running this code reveals that the fields in this particular OpenSchema recordset are called TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, and DATE_MODIFIED. So, if you iterate through the Recordset object returned by OpenSchema using code like this, you can finally get a list of the tables in the database:

Do Until rs.EOF
   Debug.Print rs.Fields("TABLE_NAME")
Loop

Meanwhile, your dinner has gotten cold, your children have grown up, and everyone you know has gotten old and moved to Florida.

OpenSchema is functional, more or less, and it has the advantage of working with any OLE DB data source. But if you're using ADO 2.0 and you're interested in moving beyond what OpenSchema provides, forget it. You can't use OpenSchema to create databases or alter an existing database's structure. ADO 2.0 doesn't let you browse other elements of the database, such as the security objects found in Jet databases. In fact, you can't even programmatically perform many security operations against the Jet engine using ADO 2.0. For a lot of developers, this is a serious show-stopper.

Access Databases With Rich New Tools
The new version of ADO changes all that. ADO 2.1 includes a new library, formally called "ActiveX Data Objects Extensions for DDL and Security," or ADOX. ADOX gives developers a rich set of tools for gaining access to the structure, security model, and procedures stored in a database. Programmers who use the Access/Jet engine might get the most use out of it, although ADOX also works with the SQL Server and ODBC OLE DB drivers. (Microsoft doesn't support the use of ADOX with its other OLE DB drivers, however.)

To use ADOX in your VB project, select "Microsoft ADO Ext. 2.1 for DDL and Security" from the Project | References menu. At that point, you'll get access to the ADOX object model (see the ADOX Object Model). (If you haven't already, you'll also need to reference Microsoft ActiveX Data Objects 2.1 Library from Project | References as well.) Make sure you've got the correct version of ADO—ADOX is only available with version 2.1 of ADO. This is the version of ADO that will ship with SQL Server 7.0 and Access 2000, which should be available by the time you read this. It's also likely that you'll be able to download ADO 2.1 as part of the Microsoft Data Access Components (MDAC) distribution, at www.microsoft.com/data/ado/.

Begin with ADOX by connecting to a data source using a Connection object, the way you normally would in ADO. Once you've opened a connection, you supply that connection to the ActiveConnection property of the ADOX Catalog object:

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn

The Catalog object represents an entire database. It contains objects that represent all the elements of the database: tables, stored procedures, views, columns of tables, and indexes (see the ADOX Object Model).

But unlike OpenSchema, ADOX enables you to do much more than simply browse the structure of an existing database. You can use ADOX to build a new database from scratch. To do this using the Jet database engine, use the Create method of the Catalog object:

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=e:\data\newdb.mdb;"

The Create method takes an OLE DB/ADO connection string as an argument. (Make sure not to pass it an ODBC connection string; they're different.)

The Create method doesn't return a Connection object, as you might expect, but it does make a Connection object available to you if the creation of the data source was successful. This Connection object is available through the ActiveConnection property of the Catalog object. There's nothing else you need to do to access the database for query and update purposes using ADO. However, at this point, having a connection to the database doesn't mean much, because there are no tables or data in the database to query.

Create a Table
To create a table in your new database, append a new Table object to the Tables collection of the Catalog object you just created:

Dim tbl As ADOX.Table
Set tbl = New ADOX.Table
tbl.Name = "tblCustomer"
cat.Tables.Append tbl

Naturally, you must provide a name for your new table, or a runtime error occurs.

This code appends a new table to the database you created previously, but the new table doesn't have any fields yet. Because client/server jargon typically refers to fields as columns, the Catalog object exposes a collection of Column objects (instead of the Fields collection provided by DAO). By creating a Column object and appending it to the Columns collection of the Table object, you build the structure of your new table one column at a time.

For example, to create a new table with FirstName and LastName columns, you could use this code:

Dim col As ADOX.Column
Set col = New ADOX.Column

' Create first name field
With col
   .Name = "FirstName"
   .DefinedSize = 50
End With
    
' Add the new column to the table.

tbl.Columns.Append col

Set col = New ADOX.Column
    
' Create last name field.
With col
   .Name = "LastName"
   .DefinedSize = 50
End With
    
' Add the new column to the table.
tbl.Columns.Append col

This works, but it's a bit tedious to code and hard to follow. However, this pattern matches exactly the tedious, hard-to-follow technique that DAO programmers must use when creating databases: Create a TableDef object, assign properties to it, then append it to the TableDefs collection of the Database to persist the new object. Fortunately, the designers of ADOX gave you a shortcut. By using arguments of the Columns collection's Append method, you can create new columns in a new table quickly without having to manipulate multiple Column objects. The shortcut code to do the same thing as the previous example looks like this:

tbl.Columns.Append "FirstName", _
   adVarWChar, 50
tbl.Columns.Append "LastName", _
   adVarWChar, 50

In this case, the most commonly used properties of the Column object (name, data type, and defined size) are flattened and appear instead as arguments of the Append method of the collection. This pattern of giving the programmer a choice between properties and arguments appears often in ADO programming. You choose which method to use: Properties are easier to read, while arguments are faster to execute and more concise. The enumerated constant adVarWChar, used as the second argument of the Append method, is a "null-terminated wide variable-length character string"—the default data type of an ADOX Column object. Access/Jet programmers know this as a Text field, and it can be up to 255 characters in length in a Jet MDB.

If you're looking to create a numeric column, such as the type that Access refers to as a "numeric long integer" field, use the constant adInteger in place of adVarWChar. This might seem confusing if you're coming from Access, especially because there's no such thing as an adLongInteger constant in ADO or ADOX. Why the discrepancy? Client/server programmers refer to 32-bit integers as just plain integers, so ADO and ADOX follow their convention, leaving out the Long qualifier. (These constants are the same in ADOX and ADO, so there's not much new to learn if you've already worked with data typing issues in ADO.)

Get Dynamic With AutoNumber Fields
What if you want to create an AutoNumber field? Although Access' GUI seems to treat AutoNumber as its own data type, AutoNumber fields are actually a special case of the numeric integer data type. Here's where things get a little tricky. AutoNumber fields are known generically in the database world as identity columns: columns that have the ability to generate their own unique identifier, typically a number, for each record. The ability to create identity columns doesn't exist in every data source, however, so it wouldn't have been appropriate for the designers of ADOX to include an AutoNumber or IdentityColumn property of the Column object. Instead, to gain access to this data-source-specific functionality, you must use dynamic properties.

With each new release, the data-access library makes more use of ADO's dynamic properties. In ADOX, the Table, Index, and Column objects all contain Properties collections—the dynamic properties of each of these objects. Dynamic properties are used for two reasons: to ensure that the set of conventional properties doesn't change radically from one version of the library to the next, and to allow for specific data providers to expose unique functionality of each back end. Access/Jet's AutoNumber field is one example of such functionality. The drawback of dynamic properties is that you don't get as much help from the development environment when you're using them—Visual Basic's IntelliSense features can't list them, and the Object Browser doesn't list dynamic properties either.

You can, however, iterate through dynamic properties in code using the same For Each…Next construct you use when accessing the contents of any collection. However, if you try doing this to a newly created Table object, the results will be exceedingly unimpressive. There aren't any dynamic properties in a newly created ADOX Table object! Why? Remember that dynamic properties are a function of the object's provider. Every OLE DB provider has the potential to expose different dynamic properties, depending on the unique capabilities of the data store it represents. But at this point, you haven't said what database the table belongs to. You're in a prickly Catch-22 here.

Fortunately, ADOX gives you a way out. You can put the new object in a meaningful data source context before actually appending it to a database by using its ParentCatalog property. (Both the Table and Column objects in ADOX have this property.) You use it to gain access to the dynamic properties of a new ADOX object before that object has been appended to the database.

To modify the previous code example so it actually works, use the ParentCatalog property (see Listing 1). Ah, that's better. Running this code displays the dynamic properties of a new Jet table in the Debug window, the way we'd originally intended. (For reference, the "Dynamic Properties" sidebar lists these properties, but bear in mind that these properties might change when you work with different versions of Jet—and the list of dynamic properties will definitely change when you're working with a data source other than a Jet database.)

Programmatically Determine Dynamic Properties
You can programmatically determine the dynamic properties of the ADOX Column object using similar code (see the "Dynamic Properties" sidebar for these dynamic properties and also for the SQL Server provider's corresponding dynamic properties). Doing this gives you the payoff you're looking for—Autoincrement. Autoincrement is a dynamic property of the ADOX Column object in a Jet database. To create an Autoincrement field, then, you need only set the property's value to True, using this code:

Set col = New ADOX.Column
Set col.ParentCatalog = cat
With col
   .Type = adInteger
   .Name = "ID"
   .Properties("Autoincrement") = True
End With
tbl.Columns.Append col

This example builds on the previous examples; col is an ADOX Column object, cat is the previously created Catalog object, and tbl is a Table object. Remember, if you forget to set the ParentCatalog property of the Column object to the Catalog object, you won't have access to the dynamic property you need to designate the column as an AutoNumber field.

Now you know how to create the most basic kinds of tables in Jet using ADOX. This is the part where SQL Server programmers get antsy. Until now, ADO has worked much better for client/server data sources than for Jet—the Jet provider didn't officially appear until ADO 2.0, and even then its functionality was limited. But ADOX was written with Jet programmers in mind. One hundred percent of its functionality works with Jet databases, including the User and Group objects, which only work with Jet, providing access to Jet's security model.

So the question is, can you use ADOX with SQL Server? The answer, unfortunately, is "kinda." For the SQL Server OLE DB Provider, the Create method of the Catalog object isn't supported at all. The same is true for a smattering of other properties and methods, and many objects (most notably the View, Procedure, and Key objects) are supported only in read-only mode. Even more capabilities are unavailable for databases you access through the Microsoft OLE DB provider for ODBC and Microsoft Oracle providers—those providers don't let you append or delete tables or indexes.

So is ADOX of any use at all to SQL Server programmers and users of other ODBC data sources? Yes, particularly when you're attempting to use functionality formerly only provided by the OpenSchema method of the ADO Connection object. You can use ADOX to add and delete tables in SQL Server as long as you have a database to begin with—this is one of the promises of having an object model in the first place. The concept of using an object layer such as ADOX to provide indirect access to a data source means that it's now possible to write code that performs the same function on both a Jet database and a SQL Server database. Before ADOX came along, you had to iron out differences in the data-definition languages of each data source, an unattractive chore on a good day. Although ADO 2.1 and ADOX have moved a step closer to simplifying data access and provided DAO programmers with one less reason to stick with DAO, the story's not finished yet.


Jeffrey P. McManus is an independent consultant, developer, and corporate trainer. He is the author of Database Access with Visual Basic 6 (Sams), a how-to guide demonstrating the database techniques available to the VB developer. Reach him by e-mail at jeffreyp@sirius.com or on the Web at www.redblazer.com.