Best Practices of ActiveX Data Object (ADO)


ADO – ActiveX Data Object is a library to manipulate data i.e. retrieving and storing records. Being a Microsoft library ADO offers great ease to help developers with their database queries. ADO is quite flexible and powerful as it doesn’t let the developer bother how the database is implemented. They can straightaway use the database if you can successfully connect to it. Although, ADO provides a great set of features to be used but here are best practices to use ADO objects efficiently to get the best out of it.

How to Instantiate Objects In ADO

Don’t use shortcuts while creating a new objects. If you use shortcuts then Visual Basic works harder to find out the object. If you use the following

The above method will create a new ADO connection and then assign to string conn. I would recommend you to use in the following manner

This will perform better than the its tempting shortcut.

Whether or Not to Qualify the Library

ADO and DAO share many of their objects so there is a chance that you won’t get the intended object if the library isn’t explicitly declared. Instead of using

use the following

Calling the library explicitly removes any chance of wrong reference.

Data Source Name (DSN) Connections Should Be Avoided

For a single connection to the database DSN will work perfectly fine but in case of large applications where you need to connect multiple times DSN will drastically slow your application down. You should avoid specifying all the connection details in code by referring to a DSN. However, there are few limitations to it i.e.

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

Don’t Forget to Release Connections

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:

Do Not Use Recordset Data To Update Your Database

Use stored procedures or SQL statement to update your database rather than Recordset. Recordset objects are very costly and requires lot of resources. Ideally, stored procedures are best for updates, deletes etc. for the following reasons:

  1. A stored procedure is faster than an equivalent action query.
  2. Stored procedures cache execution plans, so they perform better.
  3. 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.
  4. You can call a stored procedure from different applications.

OLE DB Providers Should be Preferred

Native OLE DB providers are best when it comes to database connections. As they give you access to properties and methods which others connections fails to provide.

Think About Efficient Cursors in ADO

Default cursor in ADO which is forward-only performs best and require very less overhead. When you are not using forward-only cursor, you have a choice to choose from the following cursors:

  1. 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.
  2. 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.
  3. 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.
  4. adOpenKeyset – allows you to update existing records at the time of creation, but you can’t see additions or deletions. All types of movement are enabled.

Record Count Property Should be Avoided with Forward-Only Cursor

ADO returns -1 instead of an error if you try to record count with the forward only cursor which is bit strange. You may try to count the record with other cursors too but unfortunately the results may not be correct. 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.

Connection Should Be Used Whenever Possible

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.

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

Ideally there are various tips to improve ADO performance which i might write in my next post. If you’ve got any thoughts, comments or suggestions for things we could add, leave a comment! Also please Subscribe to our RSS for latest tips, tricks and examples on cutting edge stuff.

0 I like it
0 I don't like it