How to Properly Open a Recordset in ADO

[bullfrog83]

How do you properly open and close a recordset in ado? Currently, this is my overall structure:

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset

cn.Open CurrentProject.Connection

Set rs = New ADODB.Recordset

rs.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)

'If I'm opening more than one recordset which is often the case

Set rs2 = New ADODB.Recordset

rs2.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)

'rest of code

rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
cn.Close

I've been doing some research which hasn't been too helpful and find myself a bit confused as to what's the best or most efficient way of doing this. Moreover, I find myself confused about the use of "New". When do I need to use it and why?

[ADezii]

How do you properly open and close a recordset in ado? Currently, this is my overall structure:

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset

cn.Open CurrentProject.Connection

Set rs = New ADODB.Recordset

rs.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)

'If I'm opening more than one recordset which is often the case

Set rs2 = New ADODB.Recordset

rs2.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)

'rest of code

rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
cn.Close

I've been doing some research which hasn't been too helpful and find myself a bit confused as to what's the best or most efficient way of doing this. Moreover, I find myself confused about the use of "New". When do I need to use it and why?

There are several Methods to Open/Process/Close an ADO Recordset. I'll show you my favorite Method based on the Employees Table of the Sample Northwind Database:

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"

Set rs = New ADODB.Recordset

With rs
  .Source = strSQL
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
    .Open
    
   Do While Not .EOF
     Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
       .MoveNext
   Loop
End With
  
rs.Close
Set rs = Nothing

[bullfrog83]

There are several Methods to Open/Process/Close an ADO Recordset. I'll show you my favorite Method based on the Employees Table of the Sample Northwind Database:

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"

Set rs = New ADODB.Recordset

With rs
  .Source = strSQL
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
    .Open
    
   Do While Not .EOF
     Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
       .MoveNext
   Loop
End With
  
rs.Close
Set rs = Nothing

Thanks! However, is there a real difference between the way I presented opening a recordset and the way you prefer? Because it looks like your way is almost exactly the same as mine except it has a bit more code since you dim a strSQL and type out the property names of the recordset.


opening record like this:

rs.Open "strSQL", cn, adOpenDynamic, adLockOptimisti c

and the way you prefer?

[NeoPa]

I suspect the confusion is down to the question asked. The fact that you mention the New keyword in your question leads me to believe you're not actually asking what it sounded like you were asking, but were struggling to find a way to say what you really meant. There are a bunch of different ways to deal with a recordset in ADO. I suspect you know enough about these. What I don't really understand is what you're really after.

What I can say though, is that the use of New is a lot to do with whether a new object needs to be provided or not. Some procedures will take an existing object, and populate it with the relevant information. Other (function) procedures may return a valid object to the caller. If you are planning on using a procedure that updates an existing object then you need an object to start with. This is where the New keyword comes in. This actually reserves space for the object in memory and does any initialisation defined by the class of the object. It is ready to be used, but often has no valid data until it is populated by a procedure that does that job (In the case of a recordset this would typically be to open the recordset and update the object with the related data). An object created without the New keyword can be considered to be simply a pointer to an object of that class. If that object Is Nothing, has not yet been set, then that object is fundamentally unusable until it is Set to point to a valid object of that class, generally by a function procedure that does that job (In the case of a recordset this would typically be to create a new object of the class; open the recordset; populate the new object with the relevant data and then return a pointer to this newly created object).

Does this help towards your understanding at all?

[bullfrog83]

I suspect the confusion is down to the question asked. The fact that you mention the New keyword in your question leads me to believe you're not actually asking what it sounded like you were asking, but were struggling to find a way to say what you really meant. There are a bunch of different ways to deal with a recordset in ADO. I suspect you know enough about these. What I don't really understand is what you're really after.

What I can say though, is that the use of New is a lot to do with whether a new object needs to be provided or not. Some procedures will take an existing object, and populate it with the relevant information. Other (function) procedures may return a valid object to the caller. If you are planning on using a procedure that updates an existing object then you need an object to start with. This is where the New keyword comes in. This actually reserves space for the object in memory and does any initialisation defined by the class of the object. It is ready to be used, but often has no valid data until it is populated by a procedure that does that job (In the case of a recordset this would typically be to open the recordset and update the object with the related data). An object created without the New keyword can be considered to be simply a pointer to an object.of that class. If that object Is Nothing, has not yet been set, then that object is fundamentally unusable until it is Set to point to a valid object of that class, generally by a function procedure that does that job (In the case of a recordset this would typically be to create a new object of the class; open the recordset; populate the new object with the relevant data and then return a pointer to this newly created object).

Does this help towards your understanding at all?

It does help. I suppose my confusion over the New keyword was that I already had Dim rs As ADODB.Recordset so why would I have to Set rs = New ADODB.Recordset ?

[NeoPa]

bullfrog83: It does help. I suppose my confusion over the New keyword was that I already had Dim rs As ADODB.Recordset so why would I have to Set rs = New ADODB.Recordset ?

VBA tries to hide pointers from you because it's something that seems to confuse a lot of coders, particularly those non-professional coders that may use VBA bacause they want to extend an Office application.

Nevertheless, it's probably easier if you consider Dim rs As ADODB.Recordset as code correctly setting up a pointer value to an object of class ADODB.Recordset . Notice this pointer has not yet been set. The pointer variable (rs) exists, but contains no pointer as yet. rs Is Nothing in VBA parlance. Set rs = New ADODB.Recordset then sets the pointer to a newly created object of class ADODB.Recordset . At this point, the code can now use this pointer to find the .Name of the recordset (it won't be set yet, but the space will be there for it to find).

Looking at ADezii's code on line #10, the .Source property can now be set. Prior to setting the pointer in his line #7, this statement would have caused an error.

[bullfrog83]

VBA tries to hide pointers from you because it's something that seems to confuse a lot of coders, particularly those non-professional coders that may use VBA bacause they want to extend an Office application.

Nevertheless, it's probably easier if you consider Dim rs As ADODB.Recordset as code correctly setting up a pointer value to an object of class ADODB.Recordset . Notice this pointer has not yet been set. The pointer variable (rs) exists, but contains no pointer as yet. rs Is Nothing in VBA parlance. Set rs = New ADODB.Recordset then sets the pointer to a newly created object of class ADODB.Recordset . At this point, the code can now use this pointer to find the .Name of the recordset (it won't be set yet, but the space will be there for it to find).

Looking at ADezii's code on line #10, the .Source property can now be set. Prior to setting the pointer in his line #7, this statement would have caused an error.

OK, now I understand this a bit better.

[ADezii]

To confuse you even further, you can both Declare and Instantiate an Object Variable in the same Statement, effectively eliminating the need for a Set Statement. Refer to the Revised Code Block, specifically Line# 2 and the elimination of the Set Statement. In this spercific case, a New Instance of the Object is created on first reference to it, so you don't have to use the Set statement to assign the Object reference. This Syntax, however, is NOT recommended for technical reasons. I simply point this out to you because of the interest which you have displayed on this Topic.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"

With rs
  .Source = strSQL
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
    .Open
    
   Do While Not .EOF
     Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
       .MoveNext
   Loop
End With
  
rs.Close
Set rs = Nothing

[bullfrog83]

To confuse you even further, you can both Declare and Instantiate an Object Variable in the same Statement, effectively eliminating the need for a Set Statement. Refer to the Revised Code Block, specifically Line# 2 and the elimination of the Set Statement. In this spercific case, a New Instance of the Object is created on first reference to it, so you don't have to use the Set statement to assign the Object reference. This Syntax, however, is NOT recommended for technical reasons. I simply point this out to you because of the interest which you have displayed on this Topic.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"

With rs
  .Source = strSQL
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
    .Open
    
   Do While Not .EOF
     Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
       .MoveNext
   Loop
End With
  
rs.Close
Set rs = Nothing

Actually, this doesn't confuse me! I understand what you're saying with some help from NeoPA. However, is a difference between opening a recordset this way:

rs.Open "strSQL", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

And this way?...

With rs 
  .Source = strSQL 
  .ActiveConnection = CurrentProject.Connection 
  .CursorType = adOpenDynamic 
  .LockType = adLockOptimistic 
  .Open 
End With

[ADezii]

Actually, this doesn't confuse me! I understand what you're saying with some help from NeoPA. However, is a difference between opening a recordset this way:

rs.Open "strSQL", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

And this way?...

With rs 
  .Source = strSQL 
  .ActiveConnection = CurrentProject.Connection 
  .CursorType = adOpenDynamic 
  .LockType = adLockOptimistic 
  .Open 
End With

However, is a difference between opening a recordset this way:

No difference whatsoever, it is just semantics and a matter of:

• Passing Arguments to the Open Method of an ADODB Recordset Object
• ---------------------------- OR ----------------------------
• Setting Properties of an ADODB Recordset Object