Using the ADO Objects Effectively

Russell Sinclair

The ADO Connection, Command, and Recordset objects are each designed for one main purpose and fulfill that purpose well. But these objects don’t really shine until you understand how (and how not) to use them together. In this article, I’ll discuss the costs and benefits of using the Connection object with anything and how to combine the Command and Recordset objects for maximum performance.

Cooperating connections

When you create a Connection object, you usually do so to provide a connection to the database for a Recordset or Command object to issue a series of commands. To create a connection, you set the various properties of the Connection object and then call the object’s Open method. Once you’ve created the Connection object, you set the ActiveConnection property of the Command or Recordset object that you’ll be using with that Connection object. This example uses a Recordset object:

Dim conn as New ADODB.Connection
Dim rst as New ADODB.Recordset
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Properties("Data Source") _
    = "C:\MyDatabase.mdb"
conn.Open
Set rst.ActiveConnection = conn

Once you’ve set the ActiveConnection property to a Connection object, any work done with the database by the Recordset or Command object will use that connection.

When you use ADO in your applications, it might be tempting to create one global Connection object to use throughout the life of the program. While this does minimize connections to your database, it’s not without a cost. Depending on the time it takes to reconnect to the database for each command, the overhead needed to maintain the connection to the database might not be worth the loss in memory and performance you’ll experience when the object isn’t in use. Instead, what I’d suggest is that you only establish connections when you need them.

You can also free up the ActiveConnection of an object when using client-side cursors simply by setting this property to Nothing. You can set the property back to a valid, open Connection object later on and commit changes back to the database as if you’d never disconnected, as in this example:

Set rst.ActiveConnection = conn
rst.Open "Select * from Authors"
Set rst.ActiveConnection = Nothing
...recordset processing...
Set rst.ActiveConnection = conn

In the section marked "recordset processing," the Connection object could be destroyed and recreated multiple times.

Where you do use a Connection object with a Recordset object, you can still control the nature of your connection at the Recordset level. If you look at the properties of the Connection and Recordset objects, you’ll notice that they both have a CursorLocation property. Although you can set this property on a Connection object before passing the Connection to the Recordset’s ActiveConnection property, you can override the property in the Recordset itself before you open the Recordset. This can be very useful when you want to retrieve data that doesn’t need a permanent connection to the database but the Connection object that you’re using is set to use server-side cursors.

Regardless of whether you create a Connection object or not, you have to select what provider you’ll use to connect to your database. The sidebar "OLE DB Providers" discusses my experience with the three options you have with Jet.

Using parameters with Recordsets

The Command Recordset has an important limitation for returning data. Normally, you’d create a Recordset from a Command object with code like this:

Set rst = cmd.Execute()

When you do this, ADO returns a forward-only, read-only Recordset. Since the Command object is the only object that allows you to use Parameters, this can be very frustrating if you want an updatable Recordset but need to use parameters with your SQL statement. Fortunately, there’s a poorly documented feature in ADO that allows you to use a Command object with a Recordset object. This technique allows you to use a Command object but still fine-tune your Recordset.

To use this technique, create the Command object and set all of the necessary properties (including the ActiveConnection property). Then append all of the Parameter objects that the Command object requires. Next, create a Recordset object and set the properties that control the cursor type and location of the Recordset (don’t set the ActiveConnection–that’s controlled by the Command object). With both objects now configured properly, open the Recordset, passing in the Command object in place of the normal Source parameter:

Set cmd = New Command
...set Command properties and parameters...
Set rst = New Recordset
...set Recordset properties...
rst.Open cmd

The form frmCommandAndRecordset, which is available in the accompanying Download file, gives a demonstration of this technique (see Figure 1). The two labels at the bottom of the form show the CursorType and LockType for the current Recordset object. When the Command object’s Execute call is used to create the Recordset, these fields get set to ForwardOnly, ReadOnly. By using the Recordset Open method with a Command object, I can set these properties to any valid combination and have them honored.

The code behind the buttons opens a stored query that requires parameters. If your query doesn’t contain parameters, you don’t need a Command object. You can just set the Recordset’s Source property to the name of the query and pass adCmdStoredProc to the Open method in its Options parameter.

There are a number of ways to use parameters in a SQL statement. First of all, you can use the standard Access SQL syntax to create your parameters. In the following example, the Parameters keyword lets ADO know that txtRegion is a parameter:

cmd.CommandText = "PARAMETERS txtRegion Text;" & _
  "SELECT * FROM tblCustomers " & _
  "WHERE (Region=[txtRegion])"

You don’t have to include the PARAMETERS section in your SQL, but there are some benefits to doing so. Making your parameters explicit can make your code a little easier to understand and allows Jet to parse and resolve your query faster.

Although ADO understands this SQL statement when used with Access, this isn’t the way you should be using dynamic parameters in ADO. One of the main purposes of ADO is to homogenize access to all types of data sources. Conceptually, it should be possible to change the Provider in a Connection object and have all other ADO-based code run successfully. Any SQL statement with the Parameters keyword, run against SQL Server, will produce an error. ADO has defined its own standard for dynamically created parameters. In place of the square brackets and a Parameter name ([txtRegion]), ADO accepts a question mark (?). Rewriting the previous statement for ADO would give this:

cmd.CommandText = "SELECT * FROM " & _
  "tblCustomers WHERE (Region=?)"
cmd.Parameters(0) = "NorthEast"

For each "?" in the statement, ADO creates a Parameter object that you can set to a value. The third option on frmCommandAndRecordset replaces the native Access parameter syntax with the ADO provider-safe version. The sidebar "Failing Wildcards" discusses some additional differences between Access SQL and ADO provider-safe SQL.

This discussion assumes that you’re creating the SQL statement from within your code. If you’re accessing a stored procedure, on the other hand, you can supply just the name of the procedure and then use the Command object’s CreateParameter method to generate the necessary Parameter objects. Explicitly creating Parameter objects lets you set their data type, size, and other properties. With more information about your parameters, ADO can process your Command more efficiently:

Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = conn
  .CommandText = "qryFlights"
  .CommandType = adCmdStoredProc
    
  'create a parameter and then append it 
  set prm = .CreateParameter("@Day1", _
        adDate, adParamInput, , Me.DeptDate)
  .Parameters.Append 
  'create the parameter as part of appending it
  .Parameters.Append .CreateParameter("@Day2", _
        adDate, adParamInput, , Me.DeptDate + 3)
  Set rst = .Execute
End With

There’s one item of note: When you create Parameter objects for a Command, you must append the Parameters to the Command in the order in which the Parameters appear in the SQL statement (reading from left to right). ADO really only cares about the ordinal position of each Parameter and ignores the names you use. The names I’ve assigned to the Parameters in the previous example are only to make it easier for me to refer to the individual objects in code.

Why ADO?

Throughout this series, you might well have been wondering why you’d want to use ADO if DAO can already do everything for you. In fact, as Michael Kaplan discussed in the August 1999 issue (see "Don’t Stick the Fork in, They’re Not Done Yet"), there are some things you can do in DAO that you can’t do in ADO. You could go for the next few years without knowing a single thing about ADO. In fact, you could do all of your coding in DAO and still create some incredible applications. But there are features in ADO that DAO will never have. One of my favorites, when working with client/server databases, is the ability to completely disconnect from the back-end database by setting the CursorLocation property.

When I first started using Access in version 2.0, we had a LAN that used NetWare 3.12 as the network operating system. In my first few weeks of working with large databases on the server, I brought the main server down four times. Needless to say, the network support personnel weren’t impressed. This was because NetWare didn’t allow record locks to exceed a value set during installation. At the time, this limit was 1,000 records. A patch for NetWare was eventually released, but some larger queries are still believed to be able to cause NetWare to fail (see the Knowledge Base article Q102422 for more details). Being able to disconnect from the database and release all of my locks until update would have eliminated this problem.

Most of the demonstrations in my sample database use disconnected Recordsets, which allow me to drop my connection to client/server databases during processing (note that, with Jet, server-side cursors give you the fastest processing of your Recordset). ADO provides me with the ability to sort and filter disconnected Recordsets without contacting the database–useful functionality when working with slow links like WANs and the Internet. Add to this the ability to switch providers with minimal code changes, and you can see why ADO is so important to me for now and for the future. Think about using ADO the next time you’re developing an Access application. You might be surprised at what you come up with.

Download ADO.exe

Sidebar: Foundation Concepts

ActiveX Data Objects are Microsoft’s long-term replacement for Data Access Objects. Like DAO, ADO provides an object-oriented way to retrieve data, organized around the Recordset object. While offering a simpler object model compared to DAO, ADO also offers some features that DAO doesn’t have.

Sidebar: OLE DB Providers

A list of the possible OLE DB providers that you can use with Access is shown in Table 1. When I first started working with ADO, it took me a little while to figure out which one was best.

Table 1. OLE DB providers for Access.

If you don’t specify a provider, you’ll get the OLE DB provider for ODBC. The ODBC provider has one benefit: It will allow you to connect to any ODBC-compliant database, even if the data source doesn’t have a native OLE DB provider. I’ve found that no matter what system you’re using, the native OLE DB provider for each database system (where it exists) is much faster than the ODBC driver. As discussed in the Access World News in the July 1999 issue of Smart Access, there’s also a bug in the ODBC provider that crops up when deleting records. With ADO version 1.5, the Jet and SQL Server providers lacked features that the ODBC driver had. ADO 2.1 has since addressed those deficiencies. For these reasons, I always use the Jet provider.

I use the Jet 4.0 driver over the 3.51 for a few reasons. One reason is that the Jet 4.0 provider is included with Microsoft Data Access Components. This is a more current implementation of the Jet engine, so it has more features. Using the 4.0 provider will save you from having to make changes to your code when you move to Access 2000. However, the main reason that I use the Jet 4.0 provider is that the Jet 3.51 provider has a lot of problems. It doesn’t support many of the features that ADO has, and some of the features that it does support have some critical bugs in them. If you’ve created an update query in your Access database, for instance, you won’t be able to execute it using the Jet 3.51 provider (though Select queries work fine).

Don’t let anyone tell you that using a non-ODBC provider will cause problems if you have to migrate to another system. I recently completed an application that went through many drastic changes as it was developed. The original back-end was an Access 97 database, but for implementation, we moved to SQL Server. Then, because of a fundamental design change, we had to move the application back to Access. In the past, each of these moves could have meant weeks in re-coding the application. With ADO, it took only a few hours. I simply changed the provider on my Connection objects, modified a few Command objects, and I was done.

Sidebar: Failing Wildcards

If you’re used to using wildcards in Access, you’ve probably noticed that ADO’s use of the question mark conflicts with the Access wildcards. ADO accepts the two ANSI SQL standard wildcard characters, the percent sign (%) and the underscore (_). The percent sign is the equivalent of the asterisk in Access SQL: It will match any combination of one or more characters in a sequence. The underscore is equivalent to Access SQL’s question mark in Access SQL: It matches any one character. The Access wildcard characters "*" and "?" are interpreted as literals. This SQL statement will only find CustomerIds that consist of the letter "A" followed by an asterisk:

rst.Source = "SELECT * FROM tblCustomers " & _
  "WHERE CustomerID Like 'A*'"

Rewritten for the Jet 4.0 ADO provider, the correct SQL statement would be as follows:

rst.Source = "SELECT * FROM tblCustomers " & _
  "WHERE CustomerID Like 'A%'"