rst.close vs. set rst=nothing

[NimbusSoftware]

Which is better, closing a database or recordset object with .close or by setting it to nothing?

 

[gecko_1]

I have been using both.
db.Close
Set db = Nothing

Set db = Nothing clears up the memory used by the db reference. (my understanding)

 

[WayneRyan]

Nimbus,

Best to do both; close and set to Nothing. But Access never really recovers
the resources.

Wayne

 

[RoyVidar]

I've seen somewhere in the helpfiles, it's supposed to be sufficient to set a DAO recordset variables to nothing, but I'm to paranoid to rely upon it. I use both.

Closing the DAO database object, only if you've opened it. If you've just set the object to an instance of the current database (set db=currentdb), there's no need to close it, only set it to nothing.

"But Access never really recovers the resources." - I agree it may seem like that from time to time, but I'm not convinced it's true.

A little story:
I have a customer to which I delivered an app that downloaded and imported a ~~45K text file every 15 minutes during business hours. I instructed them to close it from time to time (every weekend, using compact on close to reindex..., releasing whatever unreleased objects etc). I was a bit uncertain about relying on a forms timer over time, which is also one of the reasons I instructed them to close the db from time to time.

I got a call 6 (nearly 7) months after it went in production to fix some errors. Due to how the app works, I could verify they hadn't closed it at all for the period (running 24/7 for 6 months, importing information, sending e-mails...), and the errors where due to hitting the storage limitations on the drive. With the amount of recordset operetions, and other objects instantiated and released it shouldn't have lasted a week. Part of the story goes on, since then, they still don't bother closing it unless there's some errors. Once it run nearly a year before bombing due to some consequences of a thunderstorm

I think properly closing and releasing object variables at least helps Access in reclaiming memory and avoiding memory leaks.

 

[modest]

We all vote both.

.close actually closes the opened object, but leaves the variable pointing to it still.

set variable = nothing releases the pointer.

 

[RoyVidar]

Might be just a matter of semantics, but I do not vote both. I vote use what is appropriate.

Recordsets that are opened through the OpenRecordset method, I do close and release. Recordsets that are assigned for instance the form recordsetclone, I only release.

Database objects that are opened (OpenCurrentDatabase?), I close and release. Databaseobjects that are assigned CurrentDB, I release.

 

[modest]

You should always use what's appropriate.