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?
@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
9 51753 @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
@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?
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?
@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?
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.
@NeoPa
OK, now I understand this a bit better.
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
@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
-
@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
Sign in to post your reply or Sign up for a free account.
Similar topics
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,...
|
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...
|
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 =...
|
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...
|
by: (Pete Cresswell) |
last post by:
Say I've got a RecordSet:
-----------------------------------------------------------
Set myRS = CurrentDB.OpenRecordset(SomeSQL, dbOpenDynaset)...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |