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:
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.
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.