ADO: Learn to Love It

By now, you've probably heard that ADO is Microsoft's new data access model, one that goes with OLE DB and is bound to displace DAO, RDO, and even ODBC. If you've been avoiding learning about ADO, don't delay any longer. Let Rob Macdonald help you put the slippery issue of data access techniques in perspective and show you how to use the newest data access model -- ADO.

Oh no! Not another set of data access objects! If you're one of those who think you've already had to deal with one too many data access object hierarchies for a regular lifetime, well, open wide -- here's another one. To be fair, though, Active Data Objects (ADO) might be the last one you have to learn. ADO will replace DAO, RDO, OLE Messaging, and several other object models over the next few years. Frankly, it's no overstatement to say that ADO represents a proverbial sea change in the way we think of data -- a change that's more radical than everything since (and possibly including) the appearance of ODBC (and, as Chairman of the UK ODBC User Group, that took some effort for me to say!). ADO isn't based on ODBC, nor on the Jet database engine. It's based on OLE DB, an object-oriented view of data that sees all sources of data -- not just relational databases, but e-mail, the file system, objects on a Web page, and the socks in your top drawer -- all through one standard interface. This is Microsoft's vision of Universal Data Access (dare we say UDA, or does uttering it make one think of cows' udders?). At any rate, it's fast, it's easy to use -- and it's available now.

Before addressing how ADO works and discussing how and when you should apply it, let me give you a taste of what ADO programming is actually like.

ADO has been designed to be familiar to users of existing data access models, and ADO uses the well-worn Recordset concept as its primary data manipulation tool. Don't be fooled into thinking that the ADO Recordset is the same as DAO's. It will look familiar, but it's a different beast altogether. What's distinctly (and pleasantly) surprising about ADO is how little code you have to write to get some data back. For example, here's a complete ADO program:

Set objData = CreateObject("ADODB.RecordSet")
objData.Open "select * from authors", "DSN=biblio"
While Not objData.EOF
   Print objData("author")
   objData.MoveNext
   Wend

This program will print all the authors from the "authors" table in the familiar biblio.mdb database that ships with VB. [If you didn't download and install the updated version of biblio.mdb last August, you can still download the 690K self-extracting EXE from www.microsoft.com/vbasic/download/download.htm. -- Ed.] It assumes both that you've installed ADO and that you've defined an ODBC data source called "biblio" (pointing to your copy of biblio.mdb). Yes, I did say that ADO isn't based on ODBC, and it isn't. However, so many people have ODBC already that Microsoft has created an ODBC provider for OLE DB -- but more on that later.

How do you install ADO? Well, it doesn't ship with VB5, but it does get installed with Visual InterDev or Active Server Pages (ASP), and you can also download it directly from www.microsoft.com/data.

You might notice that the short program above is also VBScript-compatible (once you've replaced the Print statement with a browser-friendly alternative), but it might offend those of you who adhere to strict typing and early binding in VB5 programs (and quite right, too). Here's the same program (you'll need to have set a reference to ADODB in your project), minus such misdemeanors. As a reward for setting the reference and using early-bound object declarations, it also runs about 15 percent faster than the initial version.

Dim objData As Recordset
Set objData = New Recordset
objData.Open "select * from authors", "DSN=biblio"
While Not objData.EOF
   Print objData("author")
   objData.MoveNext
   Wend 
objData.Close

By the way, if you want to mix ADO and DAO in the same program, be sure to prefix the object name with the server name ("ADODB.RecordSet") to avoid conflict where the same object names are used in both servers, as DAO also has a Recordset object.

The Recordset object is fully functioned and supports pretty much all the methods and properties you might expect, along with its associated collection of "Field" objects (see Figure 1).

Figure 1: The ADO object model

The preceding examples have shown how compact ADO code can be. The "Open" method on the Recordset object not only executed the SQL statement, it also opened the data source defined by the ODBC data source name. The Recordset object seems to be doing work that isn't strictly related to record sets. Surely record sets are the results of executing statements, not the executors themselves? This is indeed true. However, unlike its DAO counterpart, the ADO Recordset is clever enough to know when it needs help behind the scenes. For example, the previously mentioned Recordset object called upon the services of another key ADO object -- the Command object.

You can create your own Command object, and there are plenty of good reasons to do so. One reason is to execute statements that don't return record sets. Another is that you might want to re-execute the same statement several times in an application, possibly supplying different values each time. The Command object makes this possible by providing a parameter collection. Each time you execute the statement, you simply change the parameter without needing to recreate the statement each time. There are two ways in which you can do this -- the lazy way and the efficient way.

Here's the lazy way, working against the "Pubs" database that ships with both Microsoft and Sybase SQL Server databases (again, assuming you've defined a "pubs" ODBC data source):

Dim cSQL As Command
Set cSQL = New Command
cSQL.ActiveConnection = "DSN=pubs;UID=sa;PWD=;"
cSQL.CommandText = _
     "select * from authors where au_lname like ?"

After you create the Command object, its connection and SQL command can be set as shown above. Notice the question mark in the SQL statement, also known as the parameter "place marker." Just before executing the command, a real value is supplied in place of the marker, as shown in the following code:

Dim rData As Recordset
Set rData = cSQL.Execute(, Array("R%"))
While Not rData.EOF
   Print rData("au_lname")
   rData.MoveNext
   Wend

This code will print out all authors whose names begin with the letter R, using the previously assigned SQL statement. To run the query again with a different parameter, Execute can be called again with a different value, and it will run the same SQL statement.

Statements can contain as many parameters as can be sensibly imagined; therefore, an array of values is passed into the Execute statement, which otherwise returns a standard ADO Recordset object. RDO programmers will recognize this model as similar to (but more convenient than) the use of parameters with the rdoQuery object.

Why is this lazy? Well, only because we rely on ADO to work out for itself a whole set of things about the parameter, such as its data type and size. Even though ADO doesn't "mind" doing this, it can slow the process down. ADO will ask questions of the data source on your behalf, and the data source might not be able to answer them. And while the preceding example will work fine with SQL Server, many databases aren't yet able to answer ADO's questions about their parameters (particularly if ODBC 3.x drivers haven't been installed). Therefore, it's safer, as well as faster, to provide this information yourself when you create the Command object. To do this, create a Parameter object and then append it to the Parameters collection of the Command object. Execute can then be called with no arguments:

Dim adoParam As Parameter
Dim sValue As String
sValue = "R%"
Set adoParam = cSQL.CreateParameter _
           (, adChar, , Len(sValue), sValue)
cSQL.Parameters.Append adoParam
Set rData = cSQL.Execute

The call to CreateParameter specifies the data type (adChar) and size in bytes of the parameter value, as well as the parameter value itself. CreateParameter can also be used to supply the direction of the argument. You don't have to do this for plain SQL statements, but it's important when you use stored procedures, because parameters can be input, output, or both.

Executing this query again is just a matter of providing new parameter settings before calling Execute again:

sValue = "M%"
adoParam.Value = sValue
adoParam.Size =  Len(sValue)

Just like RDO, ADO supports "prepared statements" where the query is only compiled once, regardless of how many times it's executed. This feature, which can reduce server processing time, is enabled by setting the Prepared property of the Command object to True.

In addition to changing parameters, you can also change the SQL associated with the Command object and use it to execute completely different statements, albeit losing the benefits of prepared statements. Therefore, perfectly decent applications can be written with just a single Command object. At times, though, you might want to keep several Command objects connected to the same data source, and you can save time and resources if all these objects share one connection. By default, each Command object creates an implicit Connection object to manage the data source. You can override this behavior by explicitly creating your own Connection object, in very much the same way that the "behind-the-scenes" Command object of the first example was replaced with an explicit object later. Several Command objects can then share a single connection. The following code creates a connection and assigns it to a Command object:

Dim aCon As Connection
Dim rData As Recordset
Dim cSQL As Command

Set aCon = New Connection
aCon.Open "DSN=biblio;"
Set cSQL = New Command
cSQL.ActiveConnection = aCon
cSQL.CommandText = "select * from authors _
   where author like ?" 

Table 1 shows the major methods and properties of the ADO objects discussed so far.

Table 1. Major methods and properties associated with ADO.

While ADO does provide satisfying proof of how DAO models have evolved to a simpler, more refined, and more powerful model, I admit there's a limit to how excited you can get about yet another way to get at Access, SQL Server, or ODBC. If that was all there were to ADO, I wouldn't have gone to the trouble of writing about it. But now that I've covered the basics, let's investigate what really makes ADO new and worthwhile.

Once upon a time, before the days of Windows, software coding specifications included long lists of printers that were going to be supported by the program. You can imagine how programmers fought over the right to write scores of printer drivers. These days, we're so used to standardized printing in Windows that it's getting hard to remain thankful for the freedom we enjoy, courtesy of Windows' printer drivers, to concentrate on our programs' features rather than worrying about supporting hundreds of unique printers.

Things have gone pretty much the same way with database access. On the desktop, the various drivers that come with Jet allow the same DAO program to work successfully with Access, dBase, text, and a host of other database file formats. Similarly, the differences between client/server databases all but disappear behind the modern "magic" of ODBC and its huge family of drivers. Programmers using RDO (and especially RDO 2) are assured high-performance database access through a single interface -- thanks to the ODBC API it uses.

But if you think about today's data access methods, two key questions come to mind. The first, and most obvious, is: Why do we have to choose between DAO and RDO when writing applications? I'll specifically address this question, and how ADO comes to the rescue, in the sidebar "Design Point: Which Model Should I Use?". In many ways, though, a more interesting question is: Why does standardized data access have to stop at relational databases? Consider the following types of data:

• Traditional database applications using relational data accessed via ODBC
  
  
• Workflow applications using the OLE Messaging Object library or MAPI controls to manipulate the message store in Exchange or Notes
  
  
• A program that manipulates the file system by looking at the directory and file structure
  
  
• A desktop application that uses one of the Office object models to manipulate data in an Excel spreadsheet or a Word document
  
  
• A communications program that identifies all users currently logged onto a network

All of these applications need data that can be represented in a tabular form, yet each one provides a different interface for manipulation. Imagine what it would be like to use a generalized Recordset structure that allowed the same code to be used to interrogate each of the above structures, with the same performance as the native access method. Now imagine that you could do a join between any of the above data sources, using nothing more complicated than SQL. For example, such SQL could use a sales database to find all customers who hadn't placed an order in the past six months, locate all correspondence with those customers stored in the file system, and move the files to an archive store, and at the same time, generate a letter to each customer offering a 10 percent discount on their next order.

Well, imagine no longer. This is precisely what OLE DB has been designed to do. Conceived before Microsoft coined the ActiveX buzzword to describe many of the components of their COM object technology, the OLE label has stuck to OLE DB, while the Active label is used for the object model we use to work with OLE DB, namely ADO. Don't be confused -- the difference between OLE and ActiveX is simply one of marketing; both are used only to describe different bits of COM technology. Think of ADO and OLE DB in the same way you think of RDO and ODBC. ODBC provides open access to all types of relational data, while OLE DB provides universal access to all types of data. ADO and RDO are the wrappers we use to make them easy to program.

OLE DB itself is a set of objects that regulate access to data sources that are called providers. Programs that use OLE DB are called consumers. Providers create the Recordset, Command, and other objects that consumers make use of. Microsoft knows that OLE DB will only be successful if a wide range of OLE DB providers are written that provide high-quality access to all kinds of data. While there are literally hundreds of ODBC drivers, OLE DB providers are still few and far between. To help the transition, Microsoft has implemented an ODBC provider (see Figure 2) for OLE DB that can work with any Level 1 or Level 2 ODBC driver. This is an extremely shrewd move, because it basically provides a no-hands migration route to OLE DB. Anticipating naysayers who will roll their eyes ("ODBC was slow; now you want to add another layer?"), you can bet that Microsoft has gone to great lengths to ensure that OLE DB works efficiently with existing ODBC drivers. Code-named "Kagera," this ODBC provider exposes the appropriate COM interfaces to OLE DB, while appearing to be "pure" ODBC to underlying drivers. Kagera works directly with ODBC drivers, bypassing the ODBC Driver Manager, and is the only piece of software to do this, thus keeping the number of software layers involved to a minimum.

Figure 2: The relationship among ADO, OLE DB, and ODBC

The range of OLE DB providers is rapidly increasing. Several middleware companies such as Intersolv are using their existing database drivers as a basis for OLE DB software, and the first non-database providers -- including Microsoft's Active Directory Services for manipulating the Windows file system -- are already available.

OLE DB and ADO represent Microsoft's latest efforts to create a "universal" data access interface, and I'm convinced that both will succeed. My aim in this article has been to provide a taste of what ADO and OLE DB have to offer. A great deal more will be said on the topic over the coming months. Be absolutely clear of one thing: ADO isn't "just" Web stuff. Like all things waving the "Active" banner, it's part of Microsoft's unstoppable march into the object-oriented world of component-based software. Be there, or be left behind.

Rob Macdonald is an independent software specialist based in London and southern England. In addition to consulting and training in Windows, client/server, VB, COM, and systems design and management, he also runs the UK ODBC User Group and is author of RDO and ODBC: Client Server Database Programming with Visual Basic, published by Pinnacle. +44 1722 782 433, salterton@msn.com.

Sidebar: Design Point: Which Model Should I Use?

For the ADO Design Point, I've combined Microsoft recommendations, industry comment, technical interpretation, and pure personal bias to create a flow chart (see Figure 1a) that answers the question: Which data access model should I be using in my application today?

Figure 1a: Which data access model should I be using in my application today?

It's worth explaining some of the terms used here, and some of the choices I've made. There are three broad choices available. The old workhorse data access objects (DAO) have been around since VB3 and are now known best by the Database and Recordset objects, including the latter's table-type, snapshot-type, and dynaset-type flavors. Remote data objects (RDO) were introduced in the 32-bit version of VB4 Enterprise Edition. Active data objects (ADO) -- well, you've been reading my article, haven't you?

DAO was designed to work with what are known as indexed sequential access method (ISAM) databases, such as Access, dBase, Paradox, and their desktop equivalents. DAO has traditionally operated through the Jet data engine, and while you can use Jet to access ODBC databases, you really don't want to -- trust me. RDO was introduced, well, because you really don't want to use Jet to talk to SQL Server, Oracle, and all those other server-based databases for which ODBC is so well-suited. RDO is good, but if you already have a DAO application, porting to RDO is tiresome, even if not that difficult. To make the task easier, Microsoft implemented ODBCDirect in conjunction with DAO 3.5. [See Rick Dobson's "Direct Your Apps with ODBCDirect " in the November 1997 issue for more about ODBCDirect. -- Ed.] For VB5 programmers, ODBCDirect allows you to route your DAO programs though RDO and bypass Jet, without needing to port all your code. For some 32-bit DAO applications, moving to ODBCDirect is as simple as setting a switch. You probably wouldn't write a new application using ODBCDirect in VB5 (although you might in Access), because it's neater to code in RDO.

DAO and RDO might look similar, but they're fundamentally very different. DAO is good for desktop databases, but not for client/server. RDO is great for client/server, but performs below par with desktop databases.

If, as I've been saying, ADO is the new-fangled "bee's knees," as we say here in the U.K., why shouldn't you use ADO for everything? Well, ADO is new, and unlike ODBC, it's not installed on everyone's PC yet. So you still need a bit of pioneering spirit to adopt it, and you'll need to install OLE DB and ADO on your users' machines until everyone has Windows 98 or NT5.

However, if your application is Web-based, this won't be a problem, because IIS has ADO installed on the server, so your clients won't need upgrading. Also, if your application needs to access unstructured data for which there aren't ODBC drivers, ADO might be worth your consideration.

If your needs are short-term, then there's still something to be said for sticking with what you know -- at least for the time being. But bear in mind that, while there are some RDO features not yet in ADO, this will change in the next six months or so. Don't expect to see DAO and RDO "improved" in the future. Microsoft is placing all its new data access eggs in the ADO/OLE DB basket, and all the smart money (and along with it the smart features, tools, and enhancements) are moving that way.