rs.close vs. set rs = nothing

  • Thread starter Thread starter markmarko
  • Start date Start date
M

markmarko

Does setting a recordset = nothing accomplish the same as closing it? I've
read a number of statements that it's smart to do both (close and set to
nothing), but msdn says the following:

"An alternative to the Close method is to set the value of an object
variable to Nothing (Set dbsTemp = Nothing)."

http://msdn.microsoft.com/en-us/library/bb243098.aspx

That sounds like setting to nothing accomplishes the same thing as closing.
Is this correct?
 
Rule of thumb -- if you open the object, you need to close the object.
Setting it to Nothing without closing it first runs the great (in my
opinion) risk that the object becomes orphaned, and therefore becomes a
memory leak. Even though MS says it's ok to set to Nothing without closing
(not all MS advice is the best to use <g>), why take such a shortcut when
closing it is just one more step and it's not prone to problems?
 
markmarko said:
Does setting a recordset = nothing accomplish the same as closing it? I've
read a number of statements that it's smart to do both (close and set to
nothing), but msdn says the following:

"An alternative to the Close method is to set the value of an object
variable to Nothing (Set dbsTemp = Nothing)."

http://msdn.microsoft.com/en-us/library/bb243098.aspx

That sounds like setting to nothing accomplishes the same thing as closing.
Is this correct?
Click to expand...
 
Does setting a recordset = nothing accomplish the same as closing
it?
Click to expand...

No. When you do this:

Set rs = CurrentDB.OpenRecordset("SQL String")

you have done two things:

1. you've initialized a structure in memory that's populated with
the data from your SQL statement.

2. you've returned a pointer to that memory structure and stored it
in the variable rs.

When you set rs = Nothing, all you're doing is clearing the pointer
-- you're not doing anything to release the memory used by the data
structure it was pointing to. Now, because VBA uses reference
counting to handle memory, if that was the last reference to that
memory structure, VBA should, theoretically, release the memory
(since there's no longer any reference to it). But, of course, if
VBA reference counting worked reliably for releasing memory, life
would be a lot easier. The fact is, that reference counting is an
unreliable method for managing memory, and you end up with memory
leaks.

So, instead of depending on Set rs = Nothing to release the memory
used by the recordset that the pointer stored in the variable rs
refers to, we explicitly close the recordset and then clear the
pointer:

rs.Close
Set rs = Nothing

Both are essential, no matter what MS's code examples my indicate.
I've
read a number of statements that it's smart to do both (close and
set to nothing), but msdn says the following:

"An alternative to the Close method is to set the value of an
object variable to Nothing (Set dbsTemp = Nothing)."

http://msdn.microsoft.com/en-us/library/bb243098.aspx

That sounds like setting to nothing accomplishes the same thing as
closing. Is this correct?
Click to expand...

See above. In an ideal world, MSDN would be right. In the real
world, it's not.

Of course, it also depends on what you're doing. Apparently, ADO
doesn't have the memory leaks when used with VBA that DAO does, so
the advice there may work for ADO (and for other libraries, too). We
know for a fact that it does *not* work reliably for DAO in all
versions of Access (that is, it may work reliably in some versions,
but if your code may someday be running in a different version of
Access, you don't want to depend on something specific to the
version in which you wrote it).
 
Close what you Open, Destroy what you Create.

As I Golden Rule, the moment that I type a SET I add its companion and do
the same with .Open. Remember also that if you're using an error handler you
also have to decide when and where to .Close and Set = Nothing as the Error
Handler might by pass the specific statements in the code. You'll have to
deal with it on a case by case basis.
 
That is not VBA but COM who maintains the reference count.

The fact is, that reference counting is an
unreliable method for managing memory, and you end up with memory
leaks.
Click to expand...


Are you telling you are now a fervent partisan of dotNet (which do not relay
on reference counting) ? Just kidding.


Vanderghast, Access MVP
 
Back
Top