By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,499 Members | 829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,499 IT Pros & Developers. It's quick & easy.

Do I need to use Nothing after each rst.close?

P: n/a
My program looks like this:

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
'Invoice'")
Do some work
rst.close

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
'Credit Memo'")
Do some work
rst.close

this pattern is repeated several more times and then

rst.close
Set rst = Nothing
Set db = Nothing

Do I need to set rst to Nothing after each rst.close or only after the final
close?
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Thu, 15 Jan 2004 17:33:48 GMT, "Karl Irvin" <88********@comcast.net> wrote:
My program looks like this:

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
'Invoice'")
Do some work
rst.close

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
'Credit Memo'")
Do some work
rst.close

this pattern is repeated several more times and then

rst.close
Set rst = Nothing
Set db = Nothing

Do I need to set rst to Nothing after each rst.close or only after the final
close?


What you are doing is correct, and it's what most people do. Personally,
though, I try to be more rigorous and set everything to Nothing (of the kinds
of things that need to be explicitly set to Nothing) at the same time I close
it. Since you can put statements on the same line using a colon separator, I
usually say...

rst.Close: Set rst = Nothing
Nov 12 '05 #2

P: n/a

One more question, if you don't mind me butting in...

Do you need rst.Close or can you simply to straight to Set rst = Nothing???

Thanks

Chris
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:bh********************************@4ax.com...
On Thu, 15 Jan 2004 17:33:48 GMT, "Karl Irvin" <88********@comcast.net> wrote:
My program looks like this:

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType ='Invoice'")
Do some work
rst.close

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType ='Credit Memo'")
Do some work
rst.close

this pattern is repeated several more times and then

rst.close
Set rst = Nothing
Set db = Nothing

Do I need to set rst to Nothing after each rst.close or only after the finalclose?

What you are doing is correct, and it's what most people do. Personally,
though, I try to be more rigorous and set everything to Nothing (of the

kinds of things that need to be explicitly set to Nothing) at the same time I close it. Since you can put statements on the same line using a colon separator, I usually say...

rst.Close: Set rst = Nothing

Nov 12 '05 #3

P: n/a
ya******@yahoo.com (Yamafopa!) wrote in
<bu**********@news.storm.ca>:
Do you need rst.Close or can you simply to straight to Set rst =
Nothing???


Yes, you need to do both, because they are too different things:

1. Set rst.Close releases the memory structures the recordset was
stored in.

2. Set rst = Nothing clears the pointer to that memory structure,
but doesn't necessarily release it.

The recordset variable is a pointer to a memory structure, so
clearing the pointer is a separate operation from destroying that
memory structure.

And, obviously, you've got to do the .Close before clearing the
pointer.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #4

P: n/a


David

Thank you for the concise answer. I suspected it was something like that
but had not been able to find it in a reference.

chris
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<bh********************************@4ax.com>. ..
On Thu, 15 Jan 2004 17:33:48 GMT, "Karl Irvin" <88********@comcast.net> wrote:
My program looks like this:

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
'Invoice'")
Do some work
rst.close

Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
'Credit Memo'")
Do some work
rst.close

this pattern is repeated several more times and then

rst.close
Set rst = Nothing
Set db = Nothing

Do I need to set rst to Nothing after each rst.close or only after the final
close?


What you are doing is correct, and it's what most people do. Personally,
though, I try to be more rigorous and set everything to Nothing (of the kinds
of things that need to be explicitly set to Nothing) at the same time I close
it. Since you can put statements on the same line using a colon separator, I
usually say...

rst.Close: Set rst = Nothing


Incidentally can you see anything why this wouldn't work?

Function GetRidOf(ParamArray RecordsetsToClose() )
On Error Resume Next
Dim y
For Each y In RecordsetsToClose
y.Close
Set y = Nothing
Next
End Function

Usage:
GetRidOf rstA,rstB,rstC
Nov 12 '05 #6

P: n/a
On 20 Jan 2004 03:35:05 -0800, dr**********@hotmail.com (Terry Bell) wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<bh********************************@4ax.com>. ..
On Thu, 15 Jan 2004 17:33:48 GMT, "Karl Irvin" <88********@comcast.net> wrote:
>My program looks like this:
>
>Dim db As DAO.Database, rst As DAO.Recordset
>Set db = CurrentDb
>
>Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
>'Invoice'")
>Do some work
>rst.close
>
>Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
>'Credit Memo'")
>Do some work
>rst.close
>
>this pattern is repeated several more times and then
>
>rst.close
>Set rst = Nothing
>Set db = Nothing
>
>Do I need to set rst to Nothing after each rst.close or only after the final
>close?
>


What you are doing is correct, and it's what most people do. Personally,
though, I try to be more rigorous and set everything to Nothing (of the kinds
of things that need to be explicitly set to Nothing) at the same time I close
it. Since you can put statements on the same line using a colon separator, I
usually say...

rst.Close: Set rst = Nothing


Incidentally can you see anything why this wouldn't work?

Function GetRidOf(ParamArray RecordsetsToClose() )
On Error Resume Next
Dim y
For Each y In RecordsetsToClose
y.Close
Set y = Nothing
Next
End Function

Usage:
GetRidOf rstA,rstB,rstC


Actually, I don't think that works because "y" is a copy of the reference, so
the original reference will not be cleared. I did a test of something similar
that does work though...

Public Sub GetRidOf(ParamArray RecordsetsToClose() )
Dim lngIndex as Long
On Error Resume Next
For lngIndex = LBound(RecordsetsToClose) To UBound(RecordsetsToClose)
RecordsetsToClose(lngIndex).Close
Set RecordsetsToClose(lngIndex) = Nothing
Next
End Sub

Nov 12 '05 #7

P: n/a
dr**********@hotmail.com (Terry Bell) wrote in
news:92*************************@posting.google.co m:
Steve Jorgensen <no****@nospam.nospam> wrote in message
news:<bh********************************@4ax.com>. ..
On Thu, 15 Jan 2004 17:33:48 GMT, "Karl Irvin" <88********@comcast.net>
wrote:
>My program looks like this:
>
>Dim db As DAO.Database, rst As DAO.Recordset
>Set db = CurrentDb
>
>Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE
>TxnType = 'Invoice'")
>Do some work
>rst.close
>
>Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE
>TxnType = 'Credit Memo'")
>Do some work
>rst.close
>
>this pattern is repeated several more times and then
>
>rst.close
>Set rst = Nothing
>Set db = Nothing
>
>Do I need to set rst to Nothing after each rst.close or only after the
>final close?
>


What you are doing is correct, and it's what most people do.
Personally, though, I try to be more rigorous and set everything to
Nothing (of the kinds of things that need to be explicitly set to
Nothing) at the same time I close it. Since you can put statements on
the same line using a colon separator, I usually say...

rst.Close: Set rst = Nothing


Incidentally can you see anything why this wouldn't work?

Function GetRidOf(ParamArray RecordsetsToClose() )
On Error Resume Next
Dim y
For Each y In RecordsetsToClose
y.Close
Set y = Nothing
Next
End Function

Usage:
GetRidOf rstA,rstB,rstC


The funtion closes the recordsets, but it does not release the object
pointers. Try:

Sub temp()
Dim r As DAO.Recordset
Dim s As String
s = "Object Pointer"
Set r = DBEngine(0)(0).OpenRecordset("SELECT * FROM MSysObjects")
s = s & vbNewLine & "Before GetRidOf:" & ObjPtr(r)
GetRidOf r
s = s & vbNewLine & "After GetRidOf:" & ObjPtr(r)
Set r = Nothing
s = s & vbNewLine & "After SetToNothing:" & ObjPtr(r)
MsgBox s
End Sub

Function GetRidOf(ParamArray RecordsetsToClose())
On Error Resume Next
Dim y
For Each y In RecordsetsToClose
y.Close
Set y = Nothing
Next
End Function
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #8

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<tl********************************@4ax.com>. ..
On 20 Jan 2004 03:35:05 -0800, dr**********@hotmail.com (Terry Bell) wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<bh********************************@4ax.com>. ..
On Thu, 15 Jan 2004 17:33:48 GMT, "Karl Irvin" <88********@comcast.net> wrote:

>My program looks like this:
>
>Dim db As DAO.Database, rst As DAO.Recordset
>Set db = CurrentDb
>
>Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
>'Invoice'")
>Do some work
>rst.close
>
>Set rst = db.OpenRecordset("SELECT * FROM tUnpaidInvoiceIDs WHERE TxnType =
>'Credit Memo'")
>Do some work
>rst.close
>
>this pattern is repeated several more times and then
>
>rst.close
>Set rst = Nothing
>Set db = Nothing
>
>Do I need to set rst to Nothing after each rst.close or only after the final
>close?
>

What you are doing is correct, and it's what most people do. Personally,
though, I try to be more rigorous and set everything to Nothing (of the kinds
of things that need to be explicitly set to Nothing) at the same time I close
it. Since you can put statements on the same line using a colon separator, I
usually say...

rst.Close: Set rst = Nothing


Incidentally can you see anything why this wouldn't work?

Function GetRidOf(ParamArray RecordsetsToClose() )
On Error Resume Next
Dim y
For Each y In RecordsetsToClose
y.Close
Set y = Nothing
Next
End Function

Usage:
GetRidOf rstA,rstB,rstC


Actually, I don't think that works because "y" is a copy of the reference, so
the original reference will not be cleared. I did a test of something similar
that does work though...

Public Sub GetRidOf(ParamArray RecordsetsToClose() )
Dim lngIndex as Long
On Error Resume Next
For lngIndex = LBound(RecordsetsToClose) To UBound(RecordsetsToClose)
RecordsetsToClose(lngIndex).Close
Set RecordsetsToClose(lngIndex) = Nothing
Next
End Sub

Well that's interesting.
So is this what happens?
When you Dim y and do the For Each, on each iteration
it assigns to y a pointer to the recordset structure.
The Close works, because that's an operation on the
structure that's being pointed to.
But Set y = Nothing ... does nothing because y is not
explicitly associated with an object variable?
And thanks Lyle I tried your test with Steve's code
and confirmed it worked.
Terry
Nov 12 '05 #9

P: n/a
On 20 Jan 2004 18:42:27 -0800, dr**********@hotmail.com (Terry Bell) wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<tl********************************@4ax.com>. ..
On 20 Jan 2004 03:35:05 -0800, dr**********@hotmail.com (Terry Bell) wrote:

Well that's interesting.
So is this what happens?
When you Dim y and do the For Each, on each iteration
it assigns to y a pointer to the recordset structure.
The Close works, because that's an operation on the
structure that's being pointed to.
But Set y = Nothing ... does nothing because y is not
explicitly associated with an object variable?
And thanks Lyle I tried your test with Steve's code
and confirmed it worked.
Terry


Close - very close, but not quite.

This following may benefit some future Google searchers...

OK, the recordset itself is an object instance located somewhere in memory as
you suggest. Technically, it is stored as a structure (with its own
references to other structures), but we don't think of them as mere
structures, because metaphorically, they have behaviors as well as data,
though technically, the behaviors are implemented by the inderlyng class code.

Anyway, the code that calls our function has a recordset variable which is
just little more than a pointer to the recordset instance. The little more
part is that the recordset also knows what references are pointing to it, and
will go out of scope only when all of those pointers go out of scope or are no
longer pointing to that object.

So, when you call the function, the parameters are passed by reference unless
you say otherwise, which means that when you manipulate the reference, you are
directly manipulating the variable passed to the reference by the calling
code. It turns out this works with paramarrays as well such that each element
in the array is actually a reference to the variable that was passed to it,
not a copy of its contents. In a way, you could say the parameter in the
function operates as a pointer to the Recordset variable in the calling
function, which in turn acts as a pointer to the Recordset instance somewhere
in memory. Note that there is actually only one thing pointing directly to
the Recordset instance, so the reference counter is 1.

Now, if I make a new Recordset variable (or variant that will hold a recordset
reference), and I copy the parameter to it, I now have 2 direct references to
the recordset, and on indirect reference. The reference count is 2. If I set
the second variable to Nothing, it reduces the reference count to 1 because
the second one no longer points to the recordset, but it has no effect on the
original reference which what accounts for the 1-count.

When you run the For Each loop, the item variable is being successively
assigned copies of the Recordset references just as if you manually assigned
them, so each Recordset's reference count goes briefly to 2, then back down to
1. The calling code's references all still point to the instance, so it does
not get released.

When we loop through, and directly assign the paramarray elements to Nothing,
these assignments apply to the variables passed to those parameters directly,
so the original reference is being released, and the Recordset can get
released.
Nov 12 '05 #10

P: n/a
Sub temp()
Dim r As DAO.Recordset
Dim s As String
s = "Object Pointer"
Set r = DBEngine(0)(0).OpenRecordset("SELECT * FROM MSysObjects")
s = s & vbNewLine & "Before GetRidOf:" & ObjPtr(r)
GetRidOf r
s = s & vbNewLine & "After GetRidOf:" & ObjPtr(r)
Set r = Nothing
s = s & vbNewLine & "After SetToNothing:" & ObjPtr(r)
MsgBox s
End Sub

Sub GetRidOf(ByRef RecordsetsToClose As DAO.Recordset)
On Error GoTo errHandler

RecordsetsToClose.Close
Set RecordsetsToClose = Nothing

Exit Sub
errHandler:
Debug.Print Err.Number, Err.Description, Err.Source
End Sub
Nov 12 '05 #11

P: n/a
mi****@execpc.com (almish) wrote in news:95d47401.0401202151.73ef0347
@posting.google.com:
Sub temp()
Dim r As DAO.Recordset
Dim s As String
s = "Object Pointer"
Set r = DBEngine(0)(0).OpenRecordset("SELECT * FROM MSysObjects")
s = s & vbNewLine & "Before GetRidOf:" & ObjPtr(r)
GetRidOf r
s = s & vbNewLine & "After GetRidOf:" & ObjPtr(r)
Set r = Nothing
s = s & vbNewLine & "After SetToNothing:" & ObjPtr(r)
MsgBox s
End Sub

Sub GetRidOf(ByRef RecordsetsToClose As DAO.Recordset)
On Error GoTo errHandler

RecordsetsToClose.Close
Set RecordsetsToClose = Nothing

Exit Sub
errHandler:
Debug.Print Err.Number, Err.Description, Err.Source
End Sub


I believe the original poster wished to pass an array of recordsets.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.