By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,342 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

How to Properly Open a Recordset in ADO

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

Expand|Select|Wrap|Line Numbers
  1. Dim cn As New ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3. Dim rs2 As ADODB.Recordset
  4.  
  5. cn.Open CurrentProject.Connection
  6.  
  7. Set rs = New ADODB.Recordset
  8.  
  9. rs.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)
  10.  
  11. 'If I'm opening more than one recordset which is often the case
  12.  
  13. Set rs2 = New ADODB.Recordset
  14.  
  15. rs2.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)
  16.  
  17. 'rest of code
  18.  
  19. rs.Close
  20. Set rs = Nothing
  21. rs2.Close
  22. Set rs2 = Nothing
  23. cn.Close
  24.  
  25.  
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?
Jul 1 '10 #1

✓ answered by ADezii

@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:
Expand|Select|Wrap|Line Numbers
  1. Dim cn As New ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3. Dim strSQL As String
  4.  
  5. strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"
  6.  
  7. Set rs = New ADODB.Recordset
  8.  
  9. With rs
  10.   .Source = strSQL
  11.   .ActiveConnection = CurrentProject.Connection
  12.   .CursorType = adOpenDynamic
  13.   .LockType = adLockOptimistic
  14.     .Open
  15.  
  16.    Do While Not .EOF
  17.      Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
  18.        .MoveNext
  19.    Loop
  20. End With
  21.  
  22. rs.Close
  23. Set rs = Nothing

Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,701
@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:
Expand|Select|Wrap|Line Numbers
  1. Dim cn As New ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3. Dim strSQL As String
  4.  
  5. strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"
  6.  
  7. Set rs = New ADODB.Recordset
  8.  
  9. With rs
  10.   .Source = strSQL
  11.   .ActiveConnection = CurrentProject.Connection
  12.   .CursorType = adOpenDynamic
  13.   .LockType = adLockOptimistic
  14.     .Open
  15.  
  16.    Do While Not .EOF
  17.      Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
  18.        .MoveNext
  19.    Loop
  20. End With
  21.  
  22. rs.Close
  23. Set rs = Nothing
Jul 1 '10 #2

100+
P: 124
@ADezii
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, adLockOptimistic

and the way you prefer?
Jul 2 '10 #3

NeoPa
Expert Mod 15k+
P: 31,768
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?
Jul 2 '10 #4

100+
P: 124
@NeoPa
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?
Jul 2 '10 #5

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 2 '10 #6

100+
P: 124
@NeoPa
OK, now I understand this a bit better.
Jul 2 '10 #7

ADezii
Expert 5K+
P: 8,701
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.
Expand|Select|Wrap|Line Numbers
  1. Dim cn As New ADODB.Connection
  2. Dim rs As New ADODB.Recordset
  3. Dim strSQL As String
  4.  
  5. strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"
  6.  
  7. With rs
  8.   .Source = strSQL
  9.   .ActiveConnection = CurrentProject.Connection
  10.   .CursorType = adOpenDynamic
  11.   .LockType = adLockOptimistic
  12.     .Open
  13.  
  14.    Do While Not .EOF
  15.      Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
  16.        .MoveNext
  17.    Loop
  18. End With
  19.  
  20. rs.Close
  21. Set rs = Nothing
Jul 2 '10 #8

100+
P: 124
@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:

Expand|Select|Wrap|Line Numbers
  1. rs.Open "strSQL", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  2.  
And this way?...

Expand|Select|Wrap|Line Numbers
  1. With rs 
  2.   .Source = strSQL 
  3.   .ActiveConnection = CurrentProject.Connection 
  4.   .CursorType = adOpenDynamic 
  5.   .LockType = adLockOptimistic 
  6.   .Open 
  7. End With
  8.  
Jul 2 '10 #9

ADezii
Expert 5K+
P: 8,701
@bullfrog83
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
Jul 2 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.