IBM

10 ADO best practices

You can work more efficiently with data when you know how to take advantage of the ActiveX data objects (ADO) library. Susan Harkins offers some pointers that will help you use ADO objects effectively and avoid potential code snafus.

You can work more efficiently with data when you know how to take advantage of the ActiveX data objects (ADO) library. Susan Harkins offers some pointers that will help you use ADO objects effectively and avoid potential code snafus.


Microsoft's ActiveX data objects (ADO) library supplies objects for retrieving and manipulating data. It's a powerful library with flexible objects and any number of properties and methods. All that power and flexibility is helpful, but it's also easy to code yourself right into a corner. The following best practices will help you use ADO objects efficient and competently.

Note: This article is also available as a PDF download.

1: Instantiate objects correctly

When you create a new object using code, you declare the object using Dim, Public, or Private. Then, you define the object using Set. At some point, you must include the New keyword and you'll be tempted to take the following shortcut:

Dim cn As New ADODB.Connection

but that shortcut won't perform as well as the following:

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

Granted, two statements where one will do, seems inefficient. But in this case, it isn't. When you include New in the declaration (Dim) statement, Visual Basic works harder. Every reference to the new but undefined object forces Visual Basic to check the object.

Don't use the New keyword until you're ready to reference the new object in code. This form performs better. Admittedly, with today's powerful systems, it hardly seems necessary, but it's still considered the right way.

2: Qualify the library

When declaring an ADO object, it's best to include the ADODB library qualifier as follows:

Dim cn As ADODB.Connection

Technically, it isn't necessary to explicitly declare the library. As long as you've referenced the library, the object will be available using the following syntax:

Dim cn As Connection

However, if there's even a remote chance that you or someone else might reference the Data Access Objects (DAO) library, you should explicitly declare the ADO library. First, referencing the library is self-documenting. Second, ADO and DAO share many objects. If you don't explicitly declare the library, Visual Basic will assign a library and you might not get the one you intended.

3: Avoid DSN connections

A Data Source Name (DSN) stores information about a data source. You can avoid specifying all the connection details in your code by referring to a DSN. However, DSNs have a few limitations:

  • You must install the DSN on each client system. Updates must be made to each client.
  • They require a hit to the Registry.
  • They often take longer to connect than OLE DB.

If you're connecting just once, a DSN may be adequate. If your application is constantly connecting and reconnecting, a DSN can slow things down.

4: Release Connection objects

OLE DB establishes a new connection every time you connect. If you fail to release those Connection objects, you could eventually run out of connections. After you close a connection be sure to set each Connection object to Nothing as follows:

connectionobject.Close
Set connectionobject = Nothing

5: Don't use a Recordset object to update data

You can use a Recordset object to insert, edit, update, and delete data, but doing so usually commands a lot of resources. A stored procedure or even a pure SQL statement will perform better (usually). In particular, stored procedures are superior for the following reasons:

  • Stored procedures can handle multiple insert, update, and delete operations, which reduces network traffic. Combine multiple tasks into one stored procedure, when possible. One trip of many tasks will (usually) perform better than several short trips.
  • A stored procedure is faster than an equivalent action query.
  • Stored procedures cache execution plans, so they perform better.
  • You can call a stored procedure from different applications.

6: Use OLD DB providers

As a general rule, the native OLE DB provider is the best performing connection. There are other connection routes, but use OLE DB when possible. Besides performing better, the native OLE DB providers give you access to properties and methods other connections don't.

7: Use the most efficient cursor

ADO supports the following four cursor types:

  • adOpenForwardOnly provides a static copy of the records (you can't see additions, changes, or deletions by other users). You can move forward only through the recordset. Forward-only is the ADO default cursor type.
  • adOpenStatic provides a static copy of the records (you can't see additions, changes, or deletions by other users), but all types of movement are enabled.
  • adOpenDynamic is a dynamic cursor that requires more overhead, because updates are immediate and all types of movement are enabled. The dynamic cursor isn't currently supported by the Microsoft Jet OLE DB Provider (Access), and therefore defaults to a keyset cursor if you specify adOpenDynamic against a Jet database.
  • adOpenDynamic is similar to the dynaset recordset type and dynamic cursor, but the resulting recordset is based on a single table. Since the dynamic cursor type isn't currently supported by the Microsoft Jet OLE DB Provider, Jet defaults to a static cursor when you apply the adCmdTableDirect option.
  • adOpenKeyset allows you to updating existing records at time of creation, but you can't see additions or deletions. All types of movement are enabled.

ADO's default cursor type is the forward-only cursor. It requires the least overhead and performs the best. The others have specialized behaviors, so don't use them when a forward-only cursor will do.

8: Don't use the RecordCount property with a forward-only cursor

ADO's forward-only cursor doesn't support the RecordCount property, but you might not realize it. Instead of returning an error, the property used against a forward-only cursor returns -1.

To get a record count, use a cursor other than forward-only. Even then, the count might not be right. Usually, ADO executes an implicit MoveLast method to populate the cursor when it encounters the RecordCount property. When the RecordCount property doesn't return the right number, add a MoveLast method. Either way, moving through the records can take awhile, so avoid counting records during heavy processing times.

9: Reuse the connection

You can pass a connection string to a Command, Recordset, or Record object and in doing so, implicitly create a Connection object each time. A more efficient solution is to use one Connection object and pass it to each object when an active connection is required.

10: Read the connection string

An open Command, Recordset, or Record object can return its connection string using the following statement:

obj.ActiveConnection.ConnectionString

Use a MsgBox() function or Debug.Print statement to display the statement's results. Or use the above statement to specify the connection string in other statements or to return information about the connection.


Finally: 10 Things... the newsletter!

Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic's 10 Things newsletter, delivered every Friday. Automatically sign up today.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
angelzoo
angelzoo

Just a question, I have found the recordset object has a 255 fields limitation. I have 3 thousand fields long records (it's an array) and I have to cut the query into 255 fields long pieces. Is there any way to expand this container? I have tried to increase the CacheSize property, but it isn?t. thank you

jefferyp2100
jefferyp2100

This is great advice -- for 1999. If you're still using ADO, you are a decade out of date.

hmertens
hmertens

Some typos? re: #6: "OLD" vs "OLE" re: #7: an almost verbatim copy from the MSDN article by you but you dropped the explicit pairing of "adCmdTableDirect" against adOpenDynamic (original table at http://msdn.microsoft.com/en-us/library/aa140098(office.10).aspx is clearer; just drop the enumeration values and DAO refs?)

Editor's Picks