Chapter 3: Creating ADO Objects | Ado Examples and Best Practices

Team-Fly

WHILE MANY OF YOU KNOW (or think you know) how to construct ADO or other COM objects, bear with me for a minute while I walk you through some basics. I've seen too many suspect examples in publications whose technical editors should know better, to skip over this. We'll talk about setting the references, or writing the code on an ASP to do so, and about a couple of mistakes that many developers are still making.

Instantiating ADO Objects

Okay, I want to get down to the technical coding stuff, but before you can use any of the ADO objects, you have to get Visual Basic to instantiate the objects—but you knew that. If you are working in the Visual Basic IDE, this process is drop-dead easy. All you have to do is reference the ADO library—and this might already be done for you if you start a "Data" project template, or reference the ADO Data control component.

If you're working in the Visual Basic IDE, you can select an appropriate ADO library by using the Project/References menu. Simply point to the "Microsoft ActiveX Data Objects 2.x Library" (where "x" is the version of ADO 2 that you want to use) in the dialog box shown in Figure 3-1. After the library is referenced, Visual Basic examines the typelibs and fills in your application's symbol table. This means that as you code, Visual Basic fills in the objects, properties, methods, and (especially) enumerated arguments. This single feature in Visual Basic dramatically improves your performance—your code-writing performance. It can verify that you have typed the object names correctly and that the objects will exist at runtime.

click to expand
Figure 3-1: The Visual Basic IDE Project/References dialog box

Tip

What about the "Microsoft ActiveX Data Objects Recordset 2.1 Library"? Isn't this supposed to bring in a smaller, lighter, more limited version of ADO 2.1? Well, at one time it did—but no longer. It's now just a typelib that exposes a subset of the ADO 2.1 functionality, but it's really referencing the same MSADO15.DLL that's used by all versions of ADO.

Okay, so how do you create these objects in code? All too many MSDN and documentation^[1] examples show the very common syntax:

 Dim cn as New ADODB.Connection            ' This is the wrong way…

However, when you use this syntax, you cause Visual Basic to add measurable overhead to each and every reference to the object. That is, each time Visual Basic touches the object "cn", the compiler adds:

 If cn is Nothing then Set cn = New ADODB.Connection

So, how do you declare and instantiate ADO (or any COM object) correctly? Simply break down the operation into two steps:

Declare the variable with Dim, Public, or Private as needed, but do not use the New operator.
Use a separate Set statement when (and only when) you need to reference the new object in code. In this case, use the New operator to construct a new instance of the desired object.

This correct syntax looks like this:

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

If you follow this best-practice coding convention, your applications will run a little faster, and you avoid crippling some of the features provided by ADO when working with multiple result set stored procedures. I explain that later.

Tip

My students ask me if they need to use the ADODB qualifier when coding their Dim statements. I tell them that this is optional, but if there is even a remote chance that their code might invoke the Data Access Objects (DAO) library, that they had better include it to avoid name collisions. If you do include the ADODB prefix, changes that other bright minds make later won't break your golden code.

^[1]Guilty: I also used the Dim xx as New yy syntax in early revisions of the HHG. When I discovered how this syntax impacted performance, I corrected them. Sorry 'bout that.

Team-Fly