Hello Ron,

I am a VB programmer involved in creating OLE servers for the past year. But I'm really confused when it comes to understanding the difference in creating an object using the New keyword during the declaration of the object variable and using New with the Set keyword.

Let me show you a small piece of code:

1. Dim con as new ADODB.Connection
2. Dim rs as new ADODB.Recordset
3. con.open "DSN","paa","paa"
4. rs.open "Select user_id from users"
5. set rs = nothing
6. rs.open "Select menu_id from menu_items"

When I run this routine it works perfectly.

But when I create the recordset object as

set rs = new Adodb.Recordset

instead of

Dim rs as new ADODB.Recordset

the routine fails in the last line (line 6).

Now the purpose of writing this code was to use only one recordset object variable for n number of recordsets opened. How does the object creation technique differ in case of the New keyword for the above code?

Hello Aravind,

In VB 6 (as well as in versions 4 and 5), using the New keyword on the same line as a variable is declared, as in

Dim rs As New ADODB.Recordset

allocates memory for the variable, but it does not actually instantiate it until the variable is first referenced in code. In contrast, the statement

Dim rs As ADODB.Recordset

...

Set rs = New ADODB.Recordset

allocates memory for the object reference when the Dim statement is executed and instantiates the variable when the Set...New statement is encountered.

These two syntaxes appear to be reasonably close to one another, and one would expect them to behave in a way that's nearly identical. (In fact, in Visual Basic .NET, their behaviors are identical.) But there is a slight difference when it comes to object destruction and reinstantiation. In the first syntax, since an object reference is created when the object variable is first referenced in code, a seemingly innocuous statement like

If obj Is Nothing

always fails, since if the object does not contain a valid reference when the statement is encountered, it is reinstantiated in order to evaluate the condition. Many people, incidentally, consider this behavior to be a bug.

Your working code relies precisely on this peculiarity of the Dim...New statement. First, you declare a Recordset object, rs, on line 2, using the first syntax. When you call its Open method on line 4, the object is instantiated for the first time. It is then destroyed when you set the object reference equal to Nothing, but it is automatically reinstantiated in line 6.

In contrast, when you use the second syntax, the variable is instantiated by the Set statement, you call its Open method (line 4), then set it to Nothing (line 5). In line 6, you reference the rs object variable once again to call its Open method. This object variable is a 32-bit pointer to a physical object in memory. By setting it to Nothing in line 5, you set its value to 0; that is, you make it a null pointer. In calling the Open method, you're calling a method on a null object reference. Needless to say, the result is a syntax error. If you'd wanted to prevent it, you would have had to precede the call to the Recordset object's Open method with a statement that reinstantiates the object, like so:

Set rs = New ADODB.Recordset

Note that regardless of which method of object instantiation you use in VB .NET, your working code will fail, since in VB .NET an object must always be reinstantiated once it is set to Nothing; VB .NET will never automatically instantiate an object "for free" by the simple fact that it is referenced.

I hope this helps to answer your question.

--Ron

Return to: Ron's VB Forum