473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to Properly Open a Recordset in ADO

124 100+
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

9 51753
ADezii
8,834 Expert 8TB
@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
bullfrog83
124 100+
@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
32,556 Expert Mod 16PB
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
bullfrog83
124 100+
@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
32,556 Expert Mod 16PB
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
bullfrog83
124 100+
@NeoPa
OK, now I understand this a bit better.
Jul 2 '10 #7
ADezii
8,834 Expert 8TB
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
bullfrog83
124 100+
@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
8,834 Expert 8TB
@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

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

Similar topics

1
by: kgs | last post by:
Problem exists on ms-access, ms-sqlserver using ADO (not in ODBC), visual Basic, C#, and VB.NET. Somethimes when I open second recordset in this same connection that first, it has EOF, but I know,...
4
by: Dalan | last post by:
I presume that using an open recordset method is the preferred method of accomplishing what I'm trying to do. Of course, if there are other options that would work, feel free to share them. I...
2
by: Colleyville Alan | last post by:
I ran into problems in an app in which I received a msg of "cannot lock the table, it is in use by another user". It turns out that I had opened a recordset with a command like set rstmyrecs =...
2
by: Sunil Korah | last post by:
I am having some trouble with opening recordsets. I have used code more or less straight from the access help. But still I am getting some errors. I am unable to work out what exactly I am doing...
12
by: (Pete Cresswell) | last post by:
Say I've got a RecordSet: ----------------------------------------------------------- Set myRS = CurrentDB.OpenRecordset(SomeSQL, dbOpenDynaset)...
0
by: solar | last post by:
I am creating a new recordset in 2 tables, orders and order details.Obviously i have to open the recordset for two tables.Shall i open the recordset for the table order details and when shall i...
2
by: kifaro | last post by:
Hi I am opening recordsets in my asp page with the following command: rs.open mysql,cn,3,3 on occasion it isn't working and I have to do: set rs=cn.execute(mysql) Any clue why?? Regards,...
2
by: Kosmos | last post by:
I am opening the following connections as such although I am only referring to the first connection for this question: Dim con1 As ADODB.Connection Dim con2 As ADODB.Connection Dim recSet1 As...
2
by: banderson | last post by:
Hello Bytes, I am having trouble copying a value from an open record in a recordset into a form. I have a form frmOutreachReferral that is filled in after a Site Visit has been performed and it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.