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?

hi,

markmarko wrote:
> 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/E...ateBinding.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...t-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 <--

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" wrote:

> 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?
>
>

> 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?

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.

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.

"markmarko" wrote:

> 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.

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"
<(E-Mail Removed)> wrote in
message
news:B8B8B7A5-73BC-4D07-AE45-(E-Mail Removed)...
>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.
>
> "markmarko" wrote:
>
>> 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.

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" <(E-Mail Removed)> wrote in message
news:4B55D988-4B0E-4AAD-AF76-(E-Mail Removed)...
> 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?
>
>

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

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

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/



"GeoffG" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in
> message news:B8B8B7A5-73BC-4D07-AE45-(E-Mail Removed)...
>>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.
>>
>> "markmarko" wrote:
>>
>>> 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.
>
>