To .Close or not to .Close

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

Guest

Is it a requirement or just good form to clos a recordset before setting the
instance to nothing (or both)?

E.g. if I "set rs=db.openrecordset(sSQL)", do I need to "rs.close" it when
I'm done with it before I "set rs=nothing", or does the latter pretty much
take care of the former?

Obviously I'm not doing any updates in this example, just accessing some data.
 
In a perfect world, you could get away with sloppy programming, and Access
would close the recordset and free up the memory automatically when the
object went out of scope. We don't live in a perfect world.

Several years ago, back in the Access 97 era, several programmers learnt
that the hard way. When they tried to close their database, Access would
minimize to the task bar, but would not close (short of clobbering it with
Ctrl+Alt+Del.) Failure to close recordset and set to Nothing ended up being
one of the causes.

Since that time you will find that many of us are quite pedantic about
closing what you open (but only what you open), and explicitly setting
object variables to nothing. It's good practice anyway.
 
hi Chris,
Is it a requirement or just good form to clos a recordset before setting the
instance to nothing (or both)?
Click to expand...
In Access 97 and early it was necessary, as it left handles open thus
losing memory.

Nowadays it is "just" good style.

Other objects may need it, for controlled destroy order of used resources.

It makes reading easier, as it provides a kind of symmetry to your code.


mfG
--> stefan <--
 
Thank you Allen/Stephen. I'm a stickler for form myself and will definitely
take on a review of my code to ensure good form. I just wanted to prioritize
that task accordingly. Thanks!
 
Is it a requirement or just good form to clos a recordset before
setting the instance to nothing (or both)?

E.g. if I "set rs=db.openrecordset(sSQL)", do I need to "rs.close"
it when I'm done with it before I "set rs=nothing", or does the
latter pretty much take care of the former?

Obviously I'm not doing any updates in this example, just
accessing some data.
Click to expand...

Others have said YES to your question (i.e., you do need to close
it), but not really explained why:

The reason is that when you close a recordset you're doing something
to the data structure represented by the recordset variable, i.e.,
you're releasing its memory. When you set a recordset variable to
Nothing, you're doing nothing but clearing the contents of the
variable (a pointer to the data structure that you closed). Since
VBA works by reference counts, theoretically, setting to Nothing
should bring the references down to zero and when the recordset
variable goes out of scope, the data structure it pointed to would
be released.

But that's depending on something happening that isn't 100%
reliable.
 
Is it a requirement or just good form to clos a recordset before setting the
instance to nothing (or both)?

E.g. if I "set rs=db.openrecordset(sSQL)", do I need to "rs.close" it when
I'm done with it before I "set rs=nothing", or does the latter pretty much
take care of the former?
Click to expand...

Explicitly call the recordset's Close method? Yes. Set an object
variable to Nothing? No, not without good reason. "Trust the garbage
collector, Luke."

....but using DAO might be a good reason for paranoia:

"[DAO provides] another example of poor teardown code. DAO has Close
methods that must be called in the correct order, and the objects must
be released in the correct order as well (Recordset before Database,
for example). This single poor object model behavior has led to the
misconception that VB leaks memory unless you explicitly set all the
local variables to nothing at the end of a function. This is a
completely false notion in a well-designed object model. VB can clear
the variables faster at the End Sub line than you can from code, and
it
checks the variables even if you explicitly release your references.
Any effort you make is duplicated." Matt Curland: Advanced Visual
Basic
6 (P110).

Jamie.

--
 
Back
Top