Accessing ADO

Peter Vogel

ActiveX Data Objects is the successor to Data Access Objects, and with Access 2000, ADO finally goes mainstream. Here's a quick look at some neat things that Access 2000 and ADO can do together.

What really brought home to me that ADO was the future of data access came when I loaded up Access 2000. After I started Access 2000, I added a Basic module and went to the References list to see what was listed. Jet 4.0, the new version of DAO, wasn't even listed. The current version of Jet (3.5) was listed, but it wasn't checked off. What was both listed and invoked was ADO. For at least the first beta of Access, if you start writing code to get at your database, you're going to start using ADO.

You might as well hear it from me: ADO is different. If you don't write code, this isn't going to be an issue for you -- you can go right on using QueryDefs and setting Form properties. However, if you do write code, you're going to have to learn a new way of doing things. The good news is that ADO lets you use Access in ways that just weren't possible with DAO. In this article, I want to show you some of the ways that ADO integrates with Access.

The ADO objects
There are three major objects in the ADO environment: connection, command, and recordset. Unlike the DAO recordset, the ADO recordset can be created from scratch without accessing any prior objects. Here, for instance, is some sample code that creates a recordset:

 Dim rec As Recordset
 Set rec = New Recordset
 rec.Source = "Select * From Table1;"
 rec.ActiveConnection = "DSN=MyDSN;"
 rec.Open

The second line of code really brings home the difference between ADO and DAO: A recordset is created using the New command rather than through the CreateRecordset method of some other DAO object. I think of these as "empty" recordsets because they contain no records or, indeed, any association with a datasource from which records could be retrieved. In DAO, when a recordset is created, it draws information from the object from which it's created (either a Database or a QueryDef object) and thus "knows" which database it's to draw its records from.

Since this recordset doesn't have any place from which to get this information, it must have it passed to it through its properties. The Source property allows you to specify the command or parameters that are used to specify the data to be retrieved. In this case, that's a SQL statement.

The ActiveConnection property allows you to specify where the data for the recordset is to come from. Like an ODBC connect string, this can be quite a long list of parameters, including the name of the database, the userid and password to log on with, and much more. I've ducked the issue in this example by creating a DataSourceName using the ODBC manager and setting the ActiveConnection property to the name of the DSN. One of the nice things about converting to ADO is that you can continue to work with your existing ODBC databases.

The recordset's Open method uses the information specified in the properties to retrieve the specified records into the recordset. From there on, you can use the DAO methods that you're used to: MoveFirst, MoveNext, and MoveLast. There's at least one thing to be aware of, though: The RecordCount property in ADO almost always reflects the number of records to be retrieved into the recordset (unlike DAO, where the RecordCount property reflected only the records already read).

Access and ADO
The contents of a typical Connection property look like this:

 Provider=Microsoft.Jet.OLEDB.4.0;Persist Security 
 Info=False;User ID=Admin;Data Source=D:\Program
 Files\Microsoft Office\Office\1033\Mydb.mdb;Mode=Share 
 Deny None;Extended Properties="";Locale 
 Identifier=1033;Jet OLEDB:System database=D:\Program 
 Files\Microsoft Office\Office\system.mdw;Jet 
 OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet 
 OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet 
 OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk 
 Transactions=1;Jet OLEDB:New Database Password="";Jet 
 OLEDB:Create System Database=False

You don't want to type it in if you can avoid it. Access makes using ADO to get at Jet a little bit easier by providing a Connection property from the CurrentProject object. This property provides the complete ADO connection string for a Jet database. To work with the Jet database that the code is executing in, I need only change the line that sets the ActiveConnection property to this:

 rec.ActiveConnection = CurrentProject.Connection

There has always been a disconnect between Access's Rapid Application Design features and its support for coding. I can knock together a form that displays and manages data in little or no time. However, if I want retrieve my data into a recordset using VBA code and then display that data in a form, things get a little tougher. At the very least, I'd have to move the data field by field from the recordset to the text boxes on the form:

 Me!Text1 = rec("Field1")
 Me!Text2 = rec("Field2")
 etc.

This is a painful process and expensive when it comes to maintaining the application. If one field is added or removed from the recordset, I'd have to track down the code that shuffled the data between the recordset and the form and add (or remove) the offending field.

With Access 2000, that disconnect disappears. Access 2000 forms have a new Recordset property that can be set to a recordset generated in code:

 Set Me.Recordset = rec

When designing your form, you can bind your controls to any field by setting the field's ControlSource property as usual. You don't need to set the form's RecordSource property at all -- the Recordset will be set at runtime when you pass a recordset object to the form's Recordset property.

There are some restrictions on this magic: The recordset must be a Keyset recordset, which corresponds most closely to the Jet dynaset. You control the recordset type by setting the CursorType property to one of a set of predefined constants. Here's some code that creates a recordset object of the appropriate type and then passes it to a form to display:

 Dim rec As Recordset
 Set rec = New Recordset
 rec.Source = "Select * From Table1;"
 rec.ActiveConnection = CurrentProject.Connection
 rec.CursorType = adOpenKeyset
 rec.Open
 Set Me.Recordset = rec

ADO recordsets
As long as I've brought in the CursorType property, I should mention that the ADO recordset doesn't have quite the same distinctions between Recordset/Snapshot/Table that DAO had. In DAO, you could retrieve either the keys for a set of records when you created the recordset (a Dynaset) or all of the field for a set of records (a Snapshot). In some situations, a Snapshot recordset was more efficient than a Dynaset because no further trips to the database were required to retrieve the actual data. With a Dynaset, as you did a MoveFirst to each record, DAO used the key it had retrieved to get the record's data. More importantly, a Snapshot was essential when you wanted to ensure that all the records you processed reflected the state of the database at a specific point in time (hence, the name Snapshot). However, Snapshot was also a read-only set of records.

In ADO, those two attributes are separated. You can create a read-only Dynaset or an updateable Snapshot. The CursorType property allows you to specify whether all of the data is to be retrieved or just the keys (setting the CursorType to adOpenStatic will retrieve all the data for the records). The LockType property allows you to specify, independent of the recordset type, whether the recordset is updateable (setting LockType to adLockReadOnly makes the recordset read-only). Again, here's a difference between DAO and ADO: The default setting for the LockType property is read-only.

When you move to ADO, you really have to become aware of how objects work, and the recordset object demonstrates this need. Take a quick look at this code and tell me whether you think the form will still be displaying the records from table1 when the routine finishes:

 Set Me.Recordset = rec
 Set rec = Nothing

The answer is yes. In the code earlier in this article, I used the New statement to create a recordset object and point the rec variable at that object. But the rec object variable isn't the object; rec is merely the object variable that points to the object. In the preceding code, the form's Recordset property is also aimed at the recordset object. When the rec object variable is set to Nothing in the last line in the preceding code, all that happens is that one less item is using the object. But as long as one pointer to the recordset object remains, in this case the form's Recordset property, the recordset object will continue to exist. Which means, among other things, that the rec object variable in the preceding code could easily be reused to create another ADO recordset without affecting the recordset that the form is based on.

It's coming
This has been a quick intro to using ADO with Access 2000. I've only looked at a small part of one of the ADO objects, but you can see what's coming. ADO lets you do things that you couldn't before, and Access 2000 integrates with ADO in some very exciting ways. A lot of what you know (MoveFirst, MoveLast) will continue to work for you, and some things that you know (Recordcount) will change in interesting ways. You can start using ADO right now in any Windows 95 development tool, but using ADO and Access 2000 together gives a tremendous tool for creating database applications.

Peter Vogel, MCSD, is the editor of Smart Access and a principal in PH&V Information Services (a technology management company). In addition, he teaches for Learning Tree International and wrote their Internet applications development course. Sybex recently published the MCSD: Access 95 Certification guide, which he co-wrote with Helen Feddema. peter.vogel@phvis.com.