Visual Basic Banner Exchange

June 1998

Return to Article Index

A Practical Introduction to ActiveX Data Objects (ADO)
By Jeffrey Hasan (Client Server Specialists, Inc.)


Introduction

ActiveX Data Objects (ADO) are the newest data access offering from Microsoft, and are designed to work with a new data access layer called an OLE DB "provider" to provide, in theory, Universal Data Access (UDA, a Microsoft acronym). OLE DB is a low-level layer that envisions providing data access to any data source, including traditional relational database sources, and less traditional sources such as e-mail systems and custom business objects.

ADO provides a familiar, high level Automation-type interface to OLE DB. For those of us who work with RDO, you can think of OLE DB as being analagous to ODBC drivers. Just as RDO objects interface to the ODBC drivers, so do ADO objects interface to OLE DB. Just as different database systems require their own ODBC driver, so do different data sources require their own OLE DB providers. At present, there are relatively few of these around, but Microsoft is actively encouraging the development of OLE DB, and in fact plans on replacing ODBC with OLE DB.

ADO provides many advantages to the VB programmer, including ease of use, a familiar interface, high speed, and very low memory overhead. Unlike the traditional data object hierarchies (DAO and RDO), ADO objects can be created independently, so that you might create a "Connection" object just once, but utilize it with multiple, separate "Recordset" objects. ADO is optimized for building both client/server and web-based applications.

The purpose of this article is to provide you with a a friendly, practical introduction to the wide world of ADO: where to get it, and how to start using it. My goal is to get you up and running with ADO so that you can start learning it - many people I’ve talked with recently are unnecessarily daunted at the prospect at learning ADO, and are not sure how to get started. Your biggest challenge (and mine as well) will be to get used to the new data access paradigm that ADO represents, and to break free from thinking of data access objects as occupying a strict hierarchy. Read on, and as usual, prepare to impress your friends!

Where do I get ADO?

As of this writing, ADO Version 1.5 is available to you as a free download from Microsoft’s web site. At present, their web site is also your best source of new information on ADO, and you should check back weekly for new releases and new articles. ADO 1.5 is available to you as part of the OLE DB SDK (Software Development Kit). To obtain it, go to:

http://www.microsoft.com/data/oledb/download.htm

Make sure you review the list of features that OLE DB SDK provides before proceeding with the download. The download file is about 15MB, and be aware that the full installation requires around 80MB. If all you are interested in right now is ADO, just select the "Minimum" installation option in the setup program, which will only take up around 15MB of hard disk space.

The main ADO web page is at:

http://www.microsoft.com/data/ado/adoinfo.htm.

You can find many useful articles and ADO code samples here, especially in the "Workshop" series of articles that regularly appear. As of this writing, the most recent workshop article is on the "Connection" object (dated April 1st). You should also plan on downloading the "ADO 1.0 Online Help" file, from:

http://www.microsoft.com/data/ado/download.htm.

Although OLE DB SDK provides its own ADO help files, you will find the ADO 1.0 online help file to be smaller in scope, and therefore much more accessible to use. It also provides a clear diagram of the ADO object model. This article assumes that you have looked at a diagram of the ADO object model, and that you have access to ADO libraries for your Visual Basic project (available through the OLE DB SDK download described above).

How is ADO organized?

Previous object models such as DAO and RDO are hierarchical in nature, that is, a lower data object such as a Recordset object, is the child of several higher data objects, such as Environment and QueryDef. You cannot create an instance of a DAO Recordset object until you have created an instance of a QueryDef object, for example. ADO on the other hand, defines a much more limited set of objects, ones that work together like partners. Please refer to the ADO 1.0 online help file for a diagram of the ADO object model.

The important ADO objects are Connection, Recordset and Command. This article will focus on the Connection and Recordset objects only. The properties of each Connection object define a connection, or "open channel" to a data source. The Recordset object holds the data passed down from the source. The Recordset object could partner with the Connection object to first establish the connection, then retrieve the data. However, the Recordset object can also be created alone, and the connection parameters can be defined in its Open property.

Study the ADO object model in detail, and use the online help files and Microsoft’s web site materials to review the objects that make up ADO. For the purposes of this article, we will be treating ADO as just another data access option, like DAO and RDO, but be aware that it is designed to be much more.

How do I use ADO?

Once you have installed the OLE DB SDK and restarted your machine, open up a new Visual Basic project and open the Project - References dialog box. You should see something like the following:

You need only check the "ActiveX Data Objects 1.5 Library" (ADODB). The "ADO Recordset 1.5 Library" below this option is a client-side version of ADO (ADOR), which defines a very limited range of data access objects, which are sufficient. ADOR is sufficient for client-side data access, where you do not need a Connection object for establishing a connection to a remote data source. ADOR presents ADO as just another alternate access option to DAO and RDO.

If you try to access more exotic data sources, you will need to install its custom OLE DB provider, just as you would need a new ODBC driver for a new database system. If it’s a really exotic data source, you may in fact need to write your own provider! The OLE DB SDK provides tools for helping you do this, but I’ll leave this task to a braver soul than myself. (All you brave souls, please drop me a line relating your experience with designing an OLE DB provider).

If you have installed the OLE DB SDK, you will not need to go through any additional steps for using ADO to access a Jet database. The OLE DB SDK also comes bundled with an OLE DB provider for Microsoft SQL Server.

Show Me The Code!

OK, OK, I know you’re impatient for some good stuff, so I’m going to assume that you’ve read through some of the other materials I suggested above, and that you now have some familiarity with the ADO object model, and at least a rough idea of what Connection and Recordset objects are. Without further ado (no pun intended), let’s do some data access.

The sample code below sets up the Northwinds database as a remote data source, and then accesses it using ADO. To set up the database, open the "32bit ODBC" device manager from the Control Panel, and "Add" a new data source. Select "Microsoft Access Driver (*.mdb)" as the data source driver, as shown below:

Next, fill in the ODBC dialog box as shown below:

Select the database path for your system, by hitting the select button and browsing for the Northwinds database. You can usually find it in the DevStudio\VB\ folder and in the MSOffice\Access\Samples\ folder. Click OK when you are done, and exit the ODBC device manager.

Switch back to your VB Project and place the following code in its Form-Load event:

Private Sub Form_Load()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

‘Set Connection properties
cn.ConnectionString = "DSN=RDC Nwind;UID=;PWD=;"
cn.ConnectionTimeout = 30
cn.Open

If cn.State = adStateOpen Then _
    MsgBox "Connection to NorthWind Successful!"

cn.Close

End Sub
Run the project, and voila! You should receive a successful connection message. If not, you should check your ODBC settings again. Note that I have referenced the Connection object as "ADODB.Connection", to distinguish it from "ADOR.Connection", in the event that you have referenced both ADODB and ADOR libraries. The connection string does not look much different than an RDO connection string, nor should it.

If we were accessing a SQL server database, your Connection code would look something like this:

‘Set Connection properties
cn.Provider = "MSDASQL"
cn.ConnectionString = "driver={SQL Server};" & _
        "server=prod1;uid=bg;pwd=;database=main"
cn.Open
The "Provider" property of course refers to the OLE DB provider for Microsoft SQL Server.

Returning to our demo project, let’s use a Recordset object to access the "Orders" table, and produce a list of the first 10 unique countries from the "ShipCountry" field of the "Orders" table. Modify the previous Form-Load event code so it looks like the following:

Private Sub Form_Load()

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

Dim sSQL As String
Dim sOut As String
Dim Count As Integer
    
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Set properties of the Connection.
cn.ConnectionString = "DSN=RDC Nwind;UID=;PWD=;"
cn.ConnectionTimeout = 30
cn.Open

If cn.State = adStateOpen Then _
    MsgBox "Connection to NorthWind Successful!"

sSQL = "SELECT DISTINCT Orders.ShipCountry FROM Orders"
Set rs = cn.Execute(sSQL)

'Enumerate the recordset
sOut = ""
For Count = 1 To 10
    sOut = sOut & rs("ShipCountry") & vbCrLf
    rs.MoveNext
Next Count

MsgBox sOut, vbExclamation, "ADO Results"
cn.Close

End Sub
Your code should produce the following message box:

Unfortunately, this recordset object is limited to being read-only, forward cursor. If you need more functionality (and you almost always do), then you could define a stand-alone Recordset object that specifies its own connection properties. For example:


Private Sub Form_Load()

Dim rs As ADODB.Recordset

Dim sSQL As String
Dim sOut As String
Dim Count As Integer
    
Set rs = New ADODB.Recordset

sSQL = "SELECT DISTINCT Orders.ShipCountry FROM Orders"

rs.Open sSQL, "DSN=RDC Nwind;UID=;PWD=;", adOpenDynamic

'Report Recordset Connection information
MsgBox rs.ActiveConnection, , "Connection Info"

'Enumerate the recordset
sOut = ""
For Count = 1 To 10
    sOut = sOut & rs("ShipCountry") & vbCrLf
    rs.MoveNext
Next Count

MsgBox sOut, vbExclamation, "ADO Results"

rs.Close

End Sub
This code listing should produce the same listing of 10 countries, as before. The Recordset object is acting self-sufficient here: something its DAO and RDO cousins could never dream of! The Open method of the Recordset object opens a cursor that represents records from the results of our SQL query. Note that you may still choose to use a connection object to establish a connection to the remote data source, but that in this case, the Connection object acts as a partner to the Recordset object.

Conclusions

This article has only hinted at the powerful functionality provided by ADO. Microsoft guarantees that ADO will replace both DAO and RDO, so you need to start thinking now about how to migrate your existing data access code to ADO. The process need not be painful, especially since ADO shares much of the existing syntax. It is also possible that in the near future Microsoft or another vendor may write a migration wizard to simplify the migration process. You may want to start writing exclusively in ADO from this point on, or construct your code so that it uses either DAO/RDO or ADO. Of course, code libraries for DAO and RDO will be available for a long time to come, and the release of VB 6.0 is still a ways off, so you can rely on existing data access methods for a long time to come. But then, where’s the fun if you aren’t getting a headstart on the future?


About the Author

Copyright 1995-1998 VB Online. All rights reserved.