In this article, Russell Sinclair provides a primer on what you need to know about ADO: what it is, why you should care, ADO’s relation to OLE DB, and a tour of the major objects in ADO.
In order to explain ADO, it’s first necessary to explain OLE DB. OLE DB is a new standard that Microsoft has created for accessing persistent data stores. Basically, OLE DB is a replacement for ODBC, the Open Database Connectivity standard. What OLE DB does that ODBC didn’t is provide a standard interface to all data stores, regardless of the manufacturer or format. These data stores include database management systems and any other tool for storing or persisting data. To be used by OLE DB, a data store need only be a storage system that saves information in a common state. You can even think of the file system on Windows as a type of data store, and, in fact, there’s already an OLE DB driver for Directory Services. With the right OLE DB driver, you can access any data source you need.
OLE DB, however, doesn’t have an interface that can be easily accessed by programming languages such as Visual Basic or Java. OLE DB is meant to be used by C programmers. ActiveX Data Objects (ADO) is an object-oriented ActiveX interface that allows non-C programmers to use OLE DB. ADO is designed to be very similar to DAO, the Microsoft Jet Database Engine’s native interface. ADO gives developers the ability to access any data store without needing to know many specifics about how the data store actually handles the data.
What all of this means is that ADO is the new programming interface that you should start using in order to access any data store. However, keep in mind that in Access 95 and 97, there are no controls that make use of ADO that you can place on any of your forms. All of the objects in ADO exist entirely in code. Currently, Microsoft has created OLE DB drivers for Access, Oracle, Microsoft SQL Server, Microsoft Index Server, and Active Directory Service. Other drivers can be purchased through third parties.
OLE DB and ADO are themselves part of Microsoft’s Universal Data Access (UDA), a data management strategy designed to standardize methods of accessing information across the enterprise.
The ADO object model
Figure 1 shows a diagram of the basic ADO object model. As you can see, ADO is comprised of only three major objects: the Connection object, the Command object, and the Recordset object. Each of the objects has two collections. All three objects have a Properties collection, whose members can change depending on the OLE DB provider that’s used.
The Connection object
The Connection object is the main object in the ADO model. It’s used to create a connection to a database. If you’re used to DAO, you can think of a Connection object as a hybrid between the DAO Workspace and Database objects. Through the Connection object, you specify information about how ADO should connect to the data store. This includes what OLE DB driver to use, the name and location of the database, login credentials, and whether to use server-side or client-side cursors.
Most actions that take place against a database are housed in the Connection object. All Recordset and Command objects have a Connection object that can be accessed through their ActiveConnection property. The Errors collection of a Connection object holds all of the error messages generated by a data source when an error occurs. Errors is a collection rather than a single property, since multiple errors can be generated by a single action. For example, you might issue a SQL statement that tried to modify two fields with invalid values. In this case, the Errors collection would have two entries.
You can loop through the Errors collection as you do with many other collections: by using a For…Next loop, as in this example:
Dim cnn As New ADODB.Connection Dim errCon As ADODB.Error 'Some connection info would be added here 'and an error generated. For Each errCon In cnn.Errors Debug.Print errCon.Number, errCon.Description Next errCon
The Command object
The Command object allows you to run queries or text commands against the active Connection object. This is especially useful for queries or actions that modify data. Since many queries have input parameters, the Command object has a Parameters collection. Through this collection, you can set or retrieve values for parameters for a query (provided the data store supports parameters). If you run SQL commands or queries that return data, you can assign the data that the Command object returns to a Recordset object where you can view it at a later time.
If the data provider supports it, Command objects are compiled only the first time they’re executed. This means that the first time you issue your command, you’ll have to wait for the data provider to parse, compile, and optimize your command before executing it. On any subsequent executions of the Command in the same program, the Command will just be executed. For complex queries, this can be a real time-saver.
The Recordset object
The Recordset object is the object that holds the data returned by either the Command or the Connection object. The ADO Recordset object is similar to the DAO Recordset object. The two Recordsets share many of the same properties, and if any of you are experienced in DAO programming, then you’ll barely notice the differences. Essentially, the ADO Recordset allows you to view and modify the data retrieved from a data store. With the Recordset object, you can filter data, search for records matching your own criteria, or copy data between Recordsets or code variables.
The Fields collection of the Recordset object contains Field objects corresponding to, you guessed it, fields in the Recordset. Fields have their own properties that define the type of data contained in the field, the size of the field, the value contained within the field, and other useful information.
Events in ADO
ADO allows you to execute certain commands asynchronously. This means that you can execute a command against an object without passing control to that command. For example, if you use the Command object’s Execute method to issue a SQL statement, your program will normally stop code execution at the point that you call the method. Your program won’t continue executing until the command is finished processing. This can be impractical if the query or SQL statement that you’re running takes a long time to complete. If you specify that the function should be executed asynchronously, the lines of code following the method call will be executed without waiting for the SQL command to finish processing.
The result of this asynchronous processing is that you need some kind of notification when the method has actually completed executing. This notification is supplied through an event. If you used the Execute method, the ExecuteComplete event would fire. Any code you put in this event will be executed once the SQL command has finished processing and the data it retrieved is available for you to use.
In order to have ADO events available to you, you must declare the object variable that you use to issue the command in the declarations section of a class module (remember that forms and reports are class modules), and use the WithEvents keyword, as in this example:
Option Explicit Private WithEvents mcnn As ADODB.Connection
(For a more in-depth discussion of the WithEvents keyword, see Shamil Salakhetdinov’s article, “Using Dynamic External Event Procedures,” in the January 1999 issue of Smart Access). Once you have this code entered into a class module, the variable you’ve used for the object is treated like a control that you’ve placed on a form. When you add code to the class module, you’ll find the object listed in the object list and the events for the object listed in the procedure list. Only the Recordset and Connection objects support events.
Most of the events in the Recordset and Connection objects are paired. In any method that supports events, the events are called the WillMethod and MethodComplete events. If you have any experience programming in VBA, you’ve probably used the Before and After events of various objects (the BeforeUpdate and AfterUpdate events of the Access Form object are an example). You can think of the events in ADO in exactly the same way. One event is raised as a method begins, and the other is raised when the action completes execution. For the Command object’s Execute method, there’s a WillExecute method and an ExecuteComplete event.
You should be aware of one thing when using events in ADO. ADO allows you to cancel an asynchronous action while it’s processing. However, if you cancel an event before it’s finished, it doesn’t necessarily mean that the Complete event won’t occur. This is different from VBA, where canceling an event when it starts will prevent the completion event from begin raised. Most ADO events pass a parameter called adStatus to the event procedure. The adStatus variable lets you do the following:
- check to see whether the method completed successfully
- cancel the event by modifying the variable
- tell ADO to stop generating events in the current context
How to start using ADO
Before you can develop applications with ADO, you must download the Microsoft Data Access Components (MDAC) SDK from Microsoft (currently available at http://www.microsoft.com/data). This self-installing file includes the necessary files you’ll need to start developing applications that use ADO. If you’ll be distributing your application to other users, then you also need to ensure that you pick up the redistribution files from the same area. These files are used to install DCOM and ADO libraries on any client machines for your application.
Microsoft hasn’t yet made the installation process as simple as I’d like. Before you can use ADO on a client machine, DCOM95 or DCOM98 must be installed. If you’ve installed Internet Explorer 4.0 or later, then you have the appropriate version. Without IE4, you have at least a two-stage setup for any program you want to distribute.
At the time of this writing, Microsoft had two versions of the MDAC kit: 2.0 and 2.1. Microsoft has published information on its Web site that recommends that all users who don’t have Jet 4.0 (Office 2000 or SQL Server 7.0) installed use version 2.0. There appear to be a number of ODBC problems with MDAC 2.1 that will be addressed when Service Pack 1 for version 2.1 is released. I’d recommend that you stick with version 2.0, just to be safe, until this service pack is released.
Once you’ve installed the SDK, you must add a reference to the one of the Microsoft ADO libraries to your Access database. To add a reference to the library, open any code module, choose References from the Tools menu, and place a checkmark beside the listing for the ADO library that you’ll be using.
Picking the appropriate ADO library can be a little confusing, since there are currently three separate libraries. The Microsoft ActiveX Data Objects 2.0 library is the one you’ll most likely want to use. It includes a fully featured and up-to-date version of all of the objects that ADO has to offer. The Microsoft ActiveX Data Objects Recordset 2.0 library is a much lighter library that takes up less memory in your applications but only contains the Recordset object, along with its properties and collections. This can be very useful if you aren’t planning on managing your connections or executing commands that have parameters. The Microsoft ActiveX Data Objects (Multi-Dimensional) 1.0 library allows you to work with multi-dimensional recordsets, a concept that I’ll return to in an upcoming article. For now, it’s best to stick with the main ADO 2.1 library.
Over the next few months, I’ll be taking you step-by-step through all of ADO’s objects. I’ll do my best to ensure that you can become an expert with ADO. I’ll go in-depth and tell you about each object’s properties, methods, and events. I’ll also provide you with real-world examples of how you can use ADO in your applications. Next month, for instance, I’ll take a look at the base object in ADO: the Connection object.
Other Pages That Might Interest You
Comparing DAO and ADO Recordsets
An ADO Command Factory for Stored Procedures
Using the ADO Objects Effectively
What Every Access Developer Needs to Know About Word