Dim rs as recordset or object? How to check if an rs is open?

[markmarko]

I'm wondering about the difference in declaring a recordset as a recordset or
as an object. For example, the difference between these two decalrations

Dim rs1 as recordset
Dim rs2 as object

When declared as object, I can reference fields in this manner:
rs2.ID or rs2.LastName, etc.

When declard as recordset, those same references error.

rs1.ID results in error "Compile error: Method or data member not found"

HOW does one reference fields in a recordset when it's declared as a
recordset?

ALSO, regarding closing open recordsets, I generally use this code...

For Each TempRsForClosing In CurrentDb.recordsets
If TempRsForClosing.Name = rsSales Then rsSales.Close
If TempRsForClosing.Name = rsInstalls Then rsInstalls.Close
Next TempRsForClosing

The idea being to check that a given recordset is in fact open, then close
it (to avoid errors if it tries to close a recordset that is not open).

THE main question is that this closing code isn't working for me. My
recordsets are currently declared as objects, and when it gets to the For
Each loop, it bypasses it, even though I'm certain there are recordsets
variables still defined.

Should I use a different For Each statement? Should I instead declare
recordsets as recordsets (as opposed to objects)? If so, how do I reference
the fields in that recordset?

 

[Stefan Hoffmann]

hi,

I'm wondering about the difference in declaring a recordset as a recordset or
as an object. For example, the difference between these two decalrations

Dim rs1 as recordset
Dim rs2 as object

It's early binding vs. late binding:

http://word.mvps.org/fAQs/InterDev/EarlyvsLateBinding.htm

When declared as object, I can reference fields in this manner:
rs2.ID or rs2.LastName, etc.

This is bang vs. dot:

http://blogs.msdn.com/access/archive/2008/05/30/dot-or-bang.aspx

When declard as recordset, those same references error.

btw, you should declare your recordsets as DAO.Recordset as it otherwise
may conflict with ADODB.Recordset. Which can also cause an reference error.

Should I use a different For Each statement? Should I instead declare
recordsets as recordsets (as opposed to objects)? If so, how do I reference
the fields in that recordset?

Keep the scope as small as possible. So handle your recordsets locally
and do a clean-up as early as possible:

Private Sub WhatEver()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(..)

' Do something.

rs.Close
Set rs = Nothing

End Sub



mfG
--> stefan <--

 

[Jim Burke in Novi]

With DAO or ADODB declared recordsets, use ! to reference fields, . for
methods and properties (open, close, movefirst,etc.)

In my opinion you should explicitly close a recordset as soon as you know
you are done with it, e.g. rs.close. In your error handling code in the
routine where you access the recordset (you should always have an error
handler, i.e. On Error ...) you can check to see if a recordset is still
open. You use either the State or Status property, I forget which one
offhand. I'm sure there's some doc online to tell what the values are, i.e.
rs.Status = 1 means it is still open. You should also set the recordset
variable to Nothing after you're done with it, e.g. Set rs = nothing.

 

[markmarko]

It's early binding vs. late binding:
Ok, I've heard of that, but didn't understand it.

This is bang vs. dot:

Yes, I understand the difference... I guess I didn't type it in my post, but
I had also tried bang with no luck... Turns out I needed also to add .value
after the field name.

Keep the scope as small as possible. So handle your recordsets locally
and do a clean-up as early as possible:

Private Sub WhatEver()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(..)

' Do something.

rs.Close
Set rs = Nothing

Hmm, interesting idea... The way I had set it up was to close any open
recordset at the very end of the sub, both if the procedure ran normally, or
if an error occured.

So it sounds like your suggestion is to close it as soon as possible, then
have error code check if any recordsets are still open, and close those?

 

[markmarko]

Reply is under above post.

Also, I cannot find such a property as State or Status. Here's the list of
recordset properties...
http://msdn.microsoft.com/en-us/library/bb259066.aspx
Any other suggestions for checking for open recordsets?

PS - Are you in Novi Michigan? If so, we're practically neighbors! I'm in
Clinton Township.

 

[Jim Burke in Novi]

I always use ADODB, so I guess those properties don't exist with DAO. I guess
you can always put a close in your error handler for each recordset that may
still be open, and precede the close with an On Error Resume Next. Seems like
a bit of a sloppy way to do it, but I think it should work. I'm sure there's
a cleaner, simpler way to do it, but don't know what it is offhand.

Yeah, I'm in Novi, MI. We used to have a customer in Clinton Twp, over near
19 and Hayes. Hated having to drive over there.

 

[GeoffG]

I think the usual way with DAO is:

If Not RS is Nothing then
RS.Close
Set RS = Nothing
End If

Regards
Geoff



"Jim Burke in Novi"
message

 

[david]

When a recordset object is not open, it is not a recordset object.

If you want to, you can start from the other end:

Dim rs As DAO.Recordset
Dim myrs As DAO.Recordset
Dim db As DAO.Database
Set db = CodeDb
Set myrs = db.OpenRecordset("table1")

For Each rs In db.Recordsets
If (rs Is myrs) Then
MsgBox "yes"
End If
Next

You can also tell if a Recordset has NEVER been initialised:

dim rs as dao.recordset
msgbox vartype(rs)
Set rs = codedb.OpenRecordset("table1")
rs.close
msgbox vartype(rs)
set rs = nothing
msgbox vartype(rs)


Or you can assume that it is an open recordset, and catch the exception

On error goto Catch

msgbox rs.name
exit sub

Catch:
set rs = db.openrecordset( ...)

(david)

 

[markmarko]

If Not RS is Nothing then

RS.Close
Set RS = Nothing
End If

Ahhh, that seems like it would work nicely... Thanks!

 

[Ken Snell \(MVP\)]

Or just trap the error and be sure that you've closed the recordset:

On Error Resume Next
If RS Is Nothing = False Then
RS.Close
Set RS = Nothing
End If
Err.Clear
On Error GoTo 0

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ken Snell \(MVP\)]

I'm in
Clinton Township.

Rochester Hills, Michigan is my location!

 

[David W. Fenton]

=?Utf-8?B?SmltIEJ1cmtlIGluIE5vdmk=?=

In my opinion you should explicitly close a recordset as soon as
you know you are done with it, e.g. rs.close.

I agree with this.

In your error handling code in the
routine where you access the recordset (you should always have an
error handler, i.e. On Error ...) you can check to see if a
recordset is still open. You use either the State or Status
property,

That may be in ADO, but not in DAO, which has no such property. So
far as I know there is no way to check if a DAO recordset is still
open (except to do something you can do with an open recordset, such
as checking rs.Fields.Count, and trap the error if it fails).

I forget which one
offhand. I'm sure there's some doc online to tell what the values
are, i.e. rs.Status = 1 means it is still open. You should also
set the recordset variable to Nothing after you're done with it,
e.g. Set rs = nothing.

Reliable informants say that none of these things is necessary in
Access VBA when using ADO, because the objects go out of scope
without causing reference count problems (which is the source of the
problem with DAO).

 

[David W. Fenton]

I'm wondering about the difference in declaring a recordset as a
recordset or as an object. For example, the difference between
these two decalrations

Dim rs1 as recordset
Dim rs2 as object

When declared as object, I can reference fields in this manner:
rs2.ID or rs2.LastName, etc.

When declard as recordset, those same references error.

Why would you ever declare a DAO recordset as anything other than a
recordset? Using an object data type is only helpful if you're using
late binding, and you'll never use late binding with DAO in Access
itself (unless you're crazy).

rs1.ID results in error "Compile error: Method or data member not
found" HOW does one reference fields in a recordset when it's
declared as a recordset?

Only rs1!ID or rs1("ID") is valid.

ALSO, regarding closing open recordsets, I generally use this
code...

For Each TempRsForClosing In CurrentDb.recordsets
If TempRsForClosing.Name = rsSales Then rsSales.Close
If TempRsForClosing.Name = rsInstalls Then rsInstalls.Close
Next TempRsForClosing

This is only going to work for recordsets that are opened as a child
of CurrentDB. In other words, records opened like this:

CurrentDB.Openrecordset("[SQL statement]")

is going to be in the CurrentDB.Recordsets collection.

The idea being to check that a given recordset is in fact open,
then close it (to avoid errors if it tries to close a recordset
that is not open).

It would never occur to me to contemplate such an approach -- I
close each recordset as soon as I'm done with it, and would have no
place for a generic cleanup routine based on looping through
CurrentDB.Recordsets.

THE main question is that this closing code isn't working for me.
My recordsets are currently declared as objects, and when it gets
to the For Each loop, it bypasses it, even though I'm certain
there are recordsets variables still defined.

Are you trying to use DAO with late binding in Access? Why would you
do such an incredibly crazy thing?

Should I use a different For Each statement? Should I instead
declare recordsets as recordsets (as opposed to objects)? If so,
how do I reference the fields in that recordset?

What benefit do you imagine you're getting by using DAO objects via
generic object variables?

 

[David W. Fenton]

I think the usual way with DAO is:

If Not RS is Nothing then
RS.Close
Set RS = Nothing
End If

But a recordset can be Not Nothing and still be closed, such as
here:

rs.Close
' recordset is closed, but the pointer is still
' initialized until then next line
Set rs = Nothing

Remember, closing the recordset does *not* clear the pointer stored
in your recordset variable. It only releases the memory the
recordset is using.

 

[David W. Fenton]

Or just trap the error and be sure that you've closed the
recordset:

On Error Resume Next
If RS Is Nothing = False Then
RS.Close
Set RS = Nothing
End If
Err.Clear
On Error GoTo 0

Terrible advice, because it's just bad practice to ever depend on
completely ignoring errors. What if the error that occurs is
something other than the one you have decided to ignore? No,
instead, you need an error handler that ignores the specific error
number raised when you try to close a non-open recordset, and let
all other errors alone.

 

[Ken Snell \(MVP\)]

Terrible advice

I disagree in this particular case, but different approaches for different
folks.

 

[Tony Toews [MVP]]

Using an object data type is only helpful if you're using
late binding, and you'll never use late binding with DAO in Access
itself (unless you're crazy).

Well, I do using VB6 in the Auto FE Updater. But I did use late
binding with DAO to use the most recent version of DAO found on the
system to determine what version the MDB/MDE/ACCDB/ACCDE file was
created in so I knew what version of Access to call.

However yes I am crazy. And this is a highly unique situation and
isn't applicable to Access.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[GeoffG]

But a recordset can be Not Nothing and

still be closed

The recordset could be closed, but it wouldn't be,
unless you'd closed it. If you destroy the pointer
immediately after you close the recordset, then you
know for sure that, if RS points to something, then it
points to an open recordset. As a consequence, you have
a useful test for an open recordset. If you think you
might close a recordset without destroying the pointer,
then you can call a subroutine to do both things
reliably at the same time. Personally, I prefer a
clean-up subroutine that takes care of destroying all
objects at the end of a program; I'm less likely to
miss objects or deal with them inappropriately.

Regards
Geoff




in message

 

[David W. Fenton]

Well, I do using VB6 in the Auto FE Updater.

Which is not Access, of course.

And VB6 has no CurrentDB, right?

But I did use late
binding with DAO to use the most recent version of DAO found on
the system to determine what version the MDB/MDE/ACCDB/ACCDE file
was created in so I knew what version of Access to call.

However yes I am crazy. And this is a highly unique situation and
isn't applicable to Access.

It isn't Access, so it's not crazy at all.

 

[David W. Fenton]

The recordset could be closed, but it wouldn't be,
unless you'd closed it.

Or if it was never opened.

If you destroy the pointer
immediately after you close the recordset, then you
know for sure that, if RS points to something, then it
points to an open recordset.

I don't know how you know that. If you set it to Nothing immediately
after closing your recordset, then, sure, you know it no longer
points to an in-use memory structure. But so far as I know, there is
no way to know *before* you issue the rs.Close whether or not the
recordset is open or not (except for trapping for the error that
pops up if you try to close a recordset that isn't open).

As a consequence, you have
a useful test for an open recordset. If you think you
might close a recordset without destroying the pointer,
then you can call a subroutine to do both things
reliably at the same time. Personally, I prefer a
clean-up subroutine that takes care of destroying all
objects at the end of a program; I'm less likely to
miss objects or deal with them inappropriately.

I'm not sure what you're adding to the discussion here. If you clean
up all your recordsets with a single piece of code, it will have to
be something like this:

Public Sub CleanupRecordset(rs As DAO.Recordset)
On Error GoTo errHandler

rs.Close

exitRoutine:
Set rs = Nothing
Exit Sub

errHandler:
Select Case Err.Number
Case 91 ' Object variable or With Block not set
Case Else
MsgBox Err.Number & ": " & Err.Description, _
vbExclamation, "Error in CleanupRecordset()"
End Select
Resume exitRoutine
End Sub

And, of course, it may bet that 91 is not the only error number
(it's the one I got by trying to do rs.Close with a public recordset
variable that had not been opened).