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?
9 51905 ADezii 8,834
Recognized Expert Expert @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, adLockOptimisti c
and the way you prefer?
NeoPa 32,569
Recognized Expert Moderator MVP
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,569
Recognized Expert Moderator MVP 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.
ADezii 8,834
Recognized Expert Expert
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
-
ADezii 8,834
Recognized Expert Expert @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, that there are records. There aren't
error when I put Sleep 500, before opening, or I open from second
connection.
|
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 need to provide a means of scanning one field for all records and to
trigger a message upon opening the form for alerting a user that
maintenance activities need to be promptly performed. The recordset
does not necessarily have to be used for any...
|
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 = openrecordset("Tablename") to
get a record count and I was trying to run a query to modify the table even
though I had not yet closed the recorset.
I fixed most of them, but I was wondering if there is a way to test if a
recorset is open. I got...
|
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 wrong.
1.When I try the following code it gives the error message "compile
error- type mismatch" on the last line. ( I have seen in past postings
that this error is usually because of DAO/ADO mismatch. Is the
recordsource for forms in Access 2000...
|
by: (Pete Cresswell) |
last post by:
Say I've got a RecordSet:
-----------------------------------------------------------
Set myRS = CurrentDB.OpenRecordset(SomeSQL, dbOpenDynaset)
-----------------------------------------------------------
Is there any way to dynamically filter/unfilter/re-filter that RS without
doing any .Opens or .Closes?
--
PeteCresswell
| |
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 open it ?
My function shown below does not create a new recordset in the second
table,only in the table orders:
|
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,
Aaron
|
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 ADODB.Recordset
Dim recSet2 As ADODB.Recordset
Set con1 = CurrentProject.Connection
Set con2 = CurrentProject.Connection
Set recSet1 = New ADODB.Recordset
Set recSet2 = New ADODB.Recordset
|
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 is determined that the site needs a follow up visit.
A Site Visit is entered into frmSV and is based on tblOutreachAdmin with its primary key (autonumber) field called OutreachID. If a referral is needed, you click on a button to open...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |