To .Close or not to .Close

[KitCaz]

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.

 

[Allen Browne]

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.

 

[Stefan Hoffmann]

hi Chris,

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

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 <--

 

[KitCaz]

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!

 

[David W. Fenton]

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.

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.