Recordset Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

After opening a recordset, when do you use the following:

rs.close
rs = nothing

What's the difference? Thanks!
 
You use them when you are done processing the recordset.
rs.close Closes the recordset so you are no longer attached to it.
rs = nothing destroys the recordset object you created with the set rs=
This releases the memory used by the object.
 
rs.close Closes the recordset, but leaves the rs Object in existance.

Set rs = nothing (as it should be)

Does just that and cleans up the variable.

The difference is the OBJECT still exists so you could have a code like this

Set db = CurrentDb()
Set rs = db.openRecordset("SELECT * FROM tblReservations WHERE dteDate =
#10/31/2005#)

Code: 
rs.Close
rs = db.openRecordset("SELECT * FROM tblReservations WHERE location =
"Home")

[Code]

rs.Close
rs = db.OpenRecordset("SELECT * FROM tblGuests")

[Code]

rs.Close
Set rs = Nothing

The example above opens three different recordsets using the same
recordset object. In a way, its like calling your Office and asking to
speak to person #1, then #2, then #3 without ever hanging up. As opposed
to speaking person #1 then hanging up calling the exact same number to
speak with person #2.
 
Thanks to all three of you. It's funny though, to me, it would make more
since for them to be reversed based on the verbiage...

David C. Holley said:
rs.close Closes the recordset, but leaves the rs Object in existance.

Set rs = nothing (as it should be)

Does just that and cleans up the variable.

The difference is the OBJECT still exists so you could have a code like this

Set db = CurrentDb()
Set rs = db.openRecordset("SELECT * FROM tblReservations WHERE dteDate =
#10/31/2005#)

Code: 
rs.Close
rs = db.openRecordset("SELECT * FROM tblReservations WHERE location =
"Home")

[Code]

rs.Close
rs = db.OpenRecordset("SELECT * FROM tblGuests")

[Code]

rs.Close
Set rs = Nothing

The example above opens three different recordsets using the same
recordset object. In a way, its like calling your Office and asking to
speak to person #1, then #2, then #3 without ever hanging up. As opposed
to speaking person #1 then hanging up calling the exact same number to
speak with person #2.


[QUOTE]
After opening a recordset, when do you use the following:

rs.close
rs = nothing

What's the difference?  Thanks![/QUOTE]
[/QUOTE]
Click to expand...
 
What you open, always close. What you create always destroy. (Oh that's
scary) Anyways, when I code a loop, if...then, SET statements, etc. -
anything that requires a followup statement (or if one is preferred), I
always immediately add it so as not to forget. I am also in the habit of
destroying my objects in the reverse order that I create them as in

Set db = [Statement]
Set qry = [Statement]
Set parm = [Statement]
Set rs = [Statement]

Code: 
Set rs = nothing
Set parm = nothing
Set qry = nothing
Set db = nothing

While I have heard rumors that Access now cleans up object variables on
its own, I have not actually seen documentation to that effect *AND* it
was drilled into me that it is always be to EXPLICITY cleanup after
yourself. (Mom taught me that)

David H
[QUOTE]
Thanks to all three of you.  It's funny though, to me, it would make more
since for them to be reversed based on the verbiage...

:

[QUOTE]
rs.close Closes the recordset, but leaves the rs Object in existance.

Set rs = nothing (as it should be)

Does just that and cleans up the variable.

The difference is the OBJECT still exists so you could have a code like this

Set db = CurrentDb()
Set rs = db.openRecordset("SELECT * FROM tblReservations WHERE dteDate =
#10/31/2005#)

[CODE]

rs.Close
rs = db.openRecordset("SELECT * FROM tblReservations WHERE location =
"Home")

[Code]

rs.Close
rs = db.OpenRecordset("SELECT * FROM tblGuests")

[Code]

rs.Close
Set rs = Nothing

The example above opens three different recordsets using the same
recordset object. In a way, its like calling your Office and asking to
speak to person #1, then #2, then #3 without ever hanging up. As opposed
to speaking person #1 then hanging up calling the exact same number to
speak with person #2.


[QUOTE]
After opening a recordset, when do you use the following:

rs.close
rs = nothing

What's the difference?  Thanks![/QUOTE]
[/QUOTE][/QUOTE]
 
Set rs = nothing
Set parm = nothing
Set qry = nothing
Set db = nothing

While I have heard rumors that Access now cleans up object variables on
its own, I have not actually seen documentation to that effect *AND* it
was drilled into me that it is always be to EXPLICITY cleanup after
yourself.
Click to expand...

Always has been the case: read up the help files on variable scoping.
Problem was intermittent problems with memory leakage ascribed to bugs in
the garbage collection system.

Bear in mind that setting things to Nothing does not destroy them anyway:

Set rs1 = db.OpenRecordset(something, etc)

Set rs2 = rs1

Set rs1 = Nothing ' the recordset is not affected
debug.print rs1.EOF ' object missing error

debug.print rs2.EOF ' is a valid statement

You simply _have_ to understand the difference between objects and the
variables that refer to them. It's easier in C because it's all explicit.
Not keeping things clear leads to errors like this:

Public Function GetData() As Recordset
dim rs as Recordset
Set rs = db.OpenRecordset(something, etc)

if rs.BOF then
' no records, return error value
Set GetData = Nothing

else
' return the records
Set GetData = rs

end if

' since we opened it, we'd better close it...
rs.Close

End Function


If you see what I mean!


All the best


Tim F
 
How do you determine if the recordset still exists after closing it?
Click to expand...

This is one of those tree-falling-over-in-the-woods-when-there's-nobody-
there-to-hear-it type of questions.

Try this:

Public Sub CloseRS()

Dim db As Database

Dim rsOne As Recordset
Dim rsTwo As Recordset

Dim jetSQL As String

jetSQL = "SELECT ALL TestID, FullName FROM Test;"

Set db = CurrentDb()
Set rsOne = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)
Set rsTwo = rsOne

rsOne.Close

If rsTwo Is Nothing Then
Debug.Print "It's nothing to be proud of"

Else
Debug.Print "It's not nothing so it must be something"
Debug.Print "It has " & rsTwo.Fields.Count & " fields"

End If


End Sub

Go figure. Does it exist or doesn't it? Ceci n'est pas une pipe.


Tim F
 
Are you reusing the recordset Object for another operation as in...

Set rs=CurrentDb.OpenRecordSet("tblTransports", dbOpenForwardOnly)
with rs
While not .EOF
.Edit
.Fields("dteModified") = Now
.Update
.MoveNext
wend
.Close
end with

Set rs = CurrentDb.OpenRecordSet("tblGuests", dbOpenForwardOnly)
with rs
..close
end with

Set rs= Nothing
 
I am just checking before I use the recordset to avoid an error when using a
recordset method.
Thanks.
 
So I take it that your in a situation where you're the RecordSource
object across multiple SUBS where you create it in one sub, use it in
another and then reuse it in yet another?
 
Back
Top