You've heard the hype, you've read the documentation, and you've scoured the knowledge base and newsgroups, but you still can't use it. We're talking about ActiveX Data Objects (ADO)--DAO's leaner, meaner, new sibling. ADO is a high-level interface to OLE DB that effectively replaces DAO and RDO. Many developers view ADO as the data access model of the future. Unfortunately, if you're a normal Access user, you may find the party's over before it even begins. As you can see from the object model shown in Figure A, the familiar references and objects are gone. Instead, they've been replaced with connection strings and providers. And what is OLE DB anyway?
Figure A: ADO's object model probably seems confusing at
first.
Well, we understand your frustration. We hope to put you back on track, with a low-level, yet frank explanation of ADO. You won't learn everything you need to know, but at least you'll know where to begin.
It's the terminology!
You'll be glad to know that ADO really isn't all that hard to crack. The real culprit is the terminology. What we're seeing is the evolution of technology, and with it comes a whole new glossary of terms. Learning the language should flatten the learning curve a bit.
The middleman goes to work
Unless you're a developer, OLE DB is probably a new term for you. Simply put, OLE DB is a system-level interface. (Well, that cleared things up a lot didn't it?) In other words, OLE DB allows us to interact with all data, regardless of the data's type, the data's format, or the data's location. If you look at a typical client/server relational database setup, you'll find the DBMS engine, an SQL engine, a transaction manager, and a cursor engine--and we haven't even mentioned the data or the business rules yet. OLE DB simply delivers all these pieces in reusable components.
If ADO is still as clear as mud, don't worry. You don't need to understand architecture or be able to read blueprints in order to enter and exit a building.
There are a few more terms you might want to be familiar with, though.
Providers are the different OLE DB components--the engines and services that make it all work. Data providers are the direct interfaces between the application and the native data. Now we're getting somewhere--we're talking about Access.
Service providers interact between the application and the data providers. They deliver the value-added services and extended functionality. We're talking about SQL engines, optimizers, transaction managers--the components most users probably don't deal with. That's where the advantages of ADO really come in. And, that's why the documentation on ADO seems so convoluted to the normal user--it's overkill. Most users simply need to know how to access the data and manipulate recordsets.
I don't speak ADO
For now, all you really need to know is that ADO is the interface to OLE DB. Actually, you don't even need to know that; in user terms, ADO is an interface language between Access and your data. This definition certainly doesn't do ADO justice, but it's the simplest explanation of what ADO is to the average Access user.
The ADO object model
The ADO object model is much simpler than the DAO and RDO object models. If you compare the model shown in Figure A to DAO's model, you'll find that ADO has one top-level object--the Connection object. This object contains the Command, Error, and Recordset objects. If you're familiar with DAO, you probably notice a lack of tables and workspaces. You'll use the Connection object to create a connection to a data provider. You can think of the Connection object as a big switchboard--if you've got the right number, you can call anybody you want and the overseas operator is always going to speak your language.
The Command object points to SQL strings, stored procedures, and action queries. You'll use this object in place of DAO QueryDef Objects--which shouldn't break too many hearts.
The Recordset object creates a set of records from a query, and there's not a lot of difference between an ADO and a DAO recordset. The main difference is that ADO eliminates the Edit method, which makes editing field values much easier.
That brings us to the Errors collection, which is similar to the DAO Errors collection. You can loop through all the errors that occur using this collection.
An advantage to this new model is that you don't have to work your way down through a hierarchy of objects to create an object or access data. That means less code and less consumed memory. This may not seem important to you right now. However, if you expand your work with ADO, you'll find memory resources critical when working with Web objects.
The ADO to Access dictionary
Now let's get down to the nitty-gritty. The first step to applying ADO is to establish a connection to a data source. To do so, you'll declare a Connection object and then define the connection using the ConnectionString property's Open method. This property's syntax is
cnn.Open "Provider=constant; _
DataSource=pathtodatabase;"
If you're trying to hook up to an Access database, you'd use code similar to that shown in Listing A.
Listing A: Basic connection example
Dim cnn As New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
cnn.Close
The declaration statement creates a reference to the ADO type library. If your application also references DAO, you'll need to explicitly reference all ADO object references with the prefix ADODB.
After creating the new Connection object, the Open method specifies the Provider and the Data Source. In our example, the Provider is Microsoft Jet:
Provider=Microsoft.Jet.OLEDB.4.0
Table A: Common data providers
| Provider | Provider string |
| ODBC | MSDASQL |
| SQL Server | QLOLEDB |
| Oracle | MSDAORA |
| Index Server | MSIDXS |
| Active Directory Service | ADSDSOObject |
The second component of our Open string is the Data Source. We're opening a connection to the Northwind sample database that comes with Access. Our last statement simply executes the Close command, since we don't want to leave this connection open.
Once you've established a connection and tapped into your data source, you can work with that data by opening a recordset. There are a number of ways to accomplish this. You can use the Connection object's Execute method in the form
cnn.Execute(SQL statement, recordcount, _
textorstored)
For instance, Listing B repeats our earlier code, but it creates a recordset using the Connection object's Execute method. This method is fast if you're running bulk queries or stored procedures that don't require parameters.
Listing B: Connection.Execute recordset creation
Dim cnn As New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
cnn.Execute "SELECT * FROM Categories"
cnn.Close
A more efficient method is to use the Command object as shown in Listing C. The Command object's Execute method is somewhat more efficient and allows you to pass parameters.
Listing C: Another Execute technique
Dim cmd As New ADODB.Command
Dim strSQL As String, strConnect As String
Dim lngRecord As Long
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
strSQL = "SELECT * FROM Categories"
With cmd
.ActiveConnection = strConnect
.CommandText = strSQL
.Execute lngRecord, strSQL
End With
The final way to open a recordset is to create a Recordset object. There are a few ways to attack this task. First, you can open your recordset by setting it to the Connection or Command object. When you're running a simple SELECT statement, like the one we've been using, the latter choice is probably the best.
Your third option is to use the Recordset.Open method, as shown in Listing D. Only a recordset opened in this fashion allows you to update data and freely scroll through records.
Listing D: Recordset.Open recordset creation
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
rst.Open "SELECT * FROM Categories", cnn
rst.Close
cnn.Close
A Recordset shortcut
The code in Listing E shows a quick alternative to the code in Listing D. You don't have to create a Connection object. However, if you plan to open more than one recordset from the same data source, go ahead and create a Connection object. This way you'll reduce the amount of resources consumed and improve performance.
Listing E: Recordset.Open shortcut
Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM Categories", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
rst.Close
Finding records
Where DAO has four methods for finding records--FindFirst, FindLast, FindNext, and FindPrevious--ADO has only one method: Find. Searching with Find always begins relative to the current record. You use an offset from the current record and parameters to specify the search origin and direction. The code in Listing F puts the new ADO Find method to work finding the first record (from the current record) that contains the string Beverages in the CategoryName field.
Listing F: The new ADO Find method
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
rst.Open "Categories", cnn, adOpenKeyset, adLockOptimistic
rst.Find "CategoryName='Beverages'"
rst.Close
rst.Find "CategoryName IS Null"
rst.Find "CategoryName Null"
Updating data
After you've opened an updateable recordset, you can change or delete existing data or add new data using the Recordset object. The code in Listing G adds the string Test to the CategoryName field in the Northwind Categories table. The updating code (the With routine) is identical to the DAO code.
Listing G: Add records with rst.AddNew
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
rst.Open "Categories", cnn, adOpenKeyset, adLockOptimistic
With rst
.AddNew
!CategoryName = "Test"
.Update
End With
Another Recordset shortcut
There's an easy shortcut for adding new data to more than one field in the same record. Instead of stating each field individually in the form using
rst.AddNew
rst!CategoryName = "newdata"
rst!CategoryDescription = "newdescription"
rst.Update
rst.AddNew Array("CategoryName", "Description"), _
Array("newname", "newdescription")
rst.Update
Editing existing data
As previously mentioned, ADO eliminates the Edit method, so let's take a brief look at how to modify existing data. The code in Listing H uses the Find method to locate the Test category we added. Then, the code changes the field value to newname.
Listing H: Editing with rst.Fields
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb;"
rst.Open "Categories", cnn, adOpenKeyset, adLockOptimistic
rst.Find "CategoryName='Test'"
rst.Fields("CategoryName").Value = "newname"
rst.Update
Conclusion
At this point, we've covered a lot of information. We've shown you how to create and open a connection and a recordset. We've also reviewed how to find records, update existing data, and add new data. In ADO terms, your feet are barely wet. We haven't discussed queries, cursors, or record locking. And we've discussed only one Provider--Jet. ADO isn't a topic you can fully absorb from one article, but you now have the information you need to get started.
Copyright © 1999, ZD
Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD
Inc. Reproduction in whole or in part in any form or medium without
express written permission of ZD Inc. is prohibited. All other product
names and logos are trademarks or registered trademarks of their
respective owners.