rs.Close vs. Set rs = Nothing

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Good afternoon,
Is there any difference to using either of these at the
end of your code?
KB Article 186311 says to explicitly close recordsets to
avoid code bloat.
Does rs.Close and 'Set rs = Nothing' accomplish the same
effect? Or does rs.Close absolutely need to be used?
Also, does this also apply to databases and querydefs as
well?
Thank you in advance!

Rob
 
Rob said:
Good afternoon,
Is there any difference to using either of these at the
end of your code?
KB Article 186311 says to explicitly close recordsets to
avoid code bloat.
Does rs.Close and 'Set rs = Nothing' accomplish the same
effect? Or does rs.Close absolutely need to be used?
Also, does this also apply to databases and querydefs as
well?
Thank you in advance!
Click to expand...

Not really the same thing. You have two "entities", the actual recordset
and the object variable that is pointing at it.

rs.Close is closing the RecordSet entity so you end up still having an
object variable pointing at a (now closed) Recordset.

Set rs = Nothing releases the variable itself and whatever memory it was
taking up.

Both "should" happen automatically when scope dictates it, but I always
explicitly do both anyway because we definitely see instances where the
automatic cleanup does not happen.
 
Rick,
Thanks very much for your response!
Is there any difference as to which one you should do
first?
rs.Close, then Set rs = Nothing
or
Set rs = Nothing, then rs.Close

Also, could you ellaborate on "Both "should" happen
automatically when scope dictates"?
Thanks very much!!

Rob
 
Rob said:
Good afternoon,
Is there any difference to using either of these at the
end of your code?
KB Article 186311 says to explicitly close recordsets to
avoid code bloat.
Does rs.Close and 'Set rs = Nothing' accomplish the same
effect? Or does rs.Close absolutely need to be used?
Also, does this also apply to databases and querydefs as
well?
Thank you in advance!

Rob
Click to expand...

They are not exactly the same. The statement "rs.Close" calls the Close
method of the recordset object to which rs is a reference. The
statement "Set rs = Nothing" destroys the reference to that object which
is held in the variable rs. If that happens to be the only reference to
that recordset object, then destroying the reference will cause the
object itself to be destroyed, and part of the termination logic for
that object calls the Close method. So usually "Set rs = Nothing" has
the same effect as "rs.Close" followed by "Set rs = Nothing". But
consider this procedure:

'----- start of code -----
Sub FunWithRecordsets()

On Error Resume Next
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim lngRC As Long

Set rs1 = CurrentDb.OpenRecordset("Table1")
Set rs2 = rs1

Debug.Print "*A*"
Err.Clear: lngRC = rs1.RecordCount
Debug.Print "rs1", TypeName(rs1), rs1 Is Nothing, Err.Number
Err.Clear: lngRC = rs2.RecordCount
Debug.Print "rs2", TypeName(rs2), rs2 Is Nothing, Err.Number

Set rs1 = Nothing
Debug.Print "*B*"
Err.Clear: lngRC = rs1.RecordCount
Debug.Print "rs1", TypeName(rs1), rs1 Is Nothing, Err.Number
Err.Clear: lngRC = rs2.RecordCount
Debug.Print "rs2", TypeName(rs2), rs2 Is Nothing, Err.Number

Set rs1 = rs2
Debug.Print "*C*"
Err.Clear: lngRC = rs1.RecordCount
Debug.Print "rs1", TypeName(rs1), rs1 Is Nothing, Err.Number
Err.Clear: lngRC = rs2.RecordCount
Debug.Print "rs2", TypeName(rs2), rs2 Is Nothing, Err.Number

rs1.Close
Debug.Print "*D*"
Err.Clear: lngRC = rs1.RecordCount
Debug.Print "rs1", TypeName(rs1), rs1 Is Nothing, Err.Number
Err.Clear: lngRC = rs2.RecordCount
Debug.Print "rs2", TypeName(rs2), rs2 Is Nothing, Err.Number

Set rs1 = Nothing
Debug.Print "*E*"
Err.Clear: lngRC = rs1.RecordCount
Debug.Print "rs1", TypeName(rs1), rs1 Is Nothing, Err.Number
Err.Clear: lngRC = rs2.RecordCount
Debug.Print "rs2", TypeName(rs2), rs2 Is Nothing, Err.Number

Set rs2 = Nothing
Debug.Print "*F*"
Err.Clear: lngRC = rs1.RecordCount
Debug.Print "rs1", TypeName(rs1), rs1 Is Nothing, Err.Number
Err.Clear: lngRC = rs2.RecordCount
Debug.Print "rs2", TypeName(rs2), rs2 Is Nothing, Err.Number

End Sub

'----- end of code -----

Running the procedure results in the following lined printed to the
Immediate Window:

------ start of output ------
*A*
rs1 Recordset False 0
rs2 Recordset False 0
*B*
rs1 Nothing True 91
rs2 Recordset False 0
*C*
rs1 Recordset False 0
rs2 Recordset False 0
*D*
rs1 Recordset False 3420
rs2 Recordset False 3420
*E*
rs1 Nothing True 91
rs2 Recordset False 3420
*F*
rs1 Nothing True 91
rs2 Nothing True 91
------ end of output ------

Just so you know, error 91 is "Object variable or With block variable
not set", and error 3420 is "Object invalid or no longer set".

As you can see, the code above has two object variables which are
initially set to the same recordset object. Setting rs1 to Nothing, as
in step B, doesn't invalidate rs2, nor does it close the recordset (we
can still read rs2.RecordCount without error). We can reassign rs1 as a
reference to that same recordset (step C). Once we've called the
recordset's Close method in step D, neither recordset variable can get
at the .RecordCount property; we get error 3420. However, neither rs1
nor rs2 is Nothing ... yet. In steps E and F we set each in turn to
Nothing, and see the results.

What does all this mean, practically? It means that you should feel
free to close any object that you opened, because you presumably know
that there's no other reference to it floating around anywhere. You
should not, as a rule, close any object you didn't open, because you
don't know for sure that that same object isn't being referred to by
some other code (not necessarily under your control) that is relying on
it being open. Most people I know believe you should make a practice of
always closing any object you opened, and never closing any object you
didn't open.

As far as setting to Nothing is concerned, you shouldn't *have* to set
any non-static, local variable to Nothing -- normal procedure exit
routines should take care of that. However, there have been reports in
the past of bugs involving Access VBA not cleaning up local object
variables. I haven't seen these bugs myself, and I'm pretty sure
they've long since been fixed, but it's not a bad habit at all to
explicitly set to Nothing any object reference you're through using.
You may as well be sure. And doing this is absolutely essential if the
object variable is static or global in scope; otherwise you may find
that Access won't close because there's a dangling object reference that
can't be cleared from memory.
 
Dirk,
Thank you very much for the detailed reply!
Basically, from what I understand...both of these should
be used, just to be sure in order to clear the memory?
Does the order in which they are used matter?

Thanks very much!

Rob
 
You have to rs.Close first, because if you destroy the
reference, then try and use it, you will get an error.

When you destroy the object (Set rs = Nothing), what
SHOULD happen is that recordset is closed. Also, if the
routine has the variable declared locally, and the routine
ends, the object SHOULD be destroyed. Both of these
happen most of the time, but not always. That's why it's
good to explicitly close all recordsets, and set all
object variables to Nothing before ending the routine.


Just like Momma used to say, if you open something, close
it. If you use something, put it back like you found it.



Chris Nebinger
 
Rob said:
Dirk,
Thank you very much for the detailed reply!
Basically, from what I understand...both of these should
be used, just to be sure in order to clear the memory?
Does the order in which they are used matter?

Thanks very much!
Click to expand...

First close, then set to Nothing. I can't improve on Chris Nebinger's
answer.
 
One other tip:

Use On Error Resume Next in the Exit_btnSave_Click: routine to force all
variables to be released.
This will work fine when there are no errors. But more importantly the same
code works even when the error is that there is no rs object reference set.

Private Sub btnSave_Click()
On Error GoTo Err_btnSaveMatch_Click

Dim db As Database, tdf As TableDef, fld As Field, rs As Recordset, qdf
As QueryDef
Dim ctl As Control, strSQL As String, prm As Parameter, Criteria As
String
Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)

If rs.BOF Then
'do stuff here with the rs

Exit_btnSave_Click:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_btnSave_Click:

MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "MyForm - btnSave_Click"
Resume Exit_btnSave_Click

End Sub
 
Back
Top