VBA: benefit of closing objects and setting to nothing?

Question

I've heard and read debates on whether or not it is necessary to close objects and set their object variables to nothing. Some online commenters have said that while it was essential in the past to do so (e.g., pre-2007, leaving a recordset open when Access closed would require Task Manager to terminate Access), memory is now handled much better --- the result of which is that if an object is no longer needed in a procedure, the End Sub/End Function statement now does what "[object].Close" and "Set [object] = Nothing" previously did.

Besides allowing an object variable to be redefined or to prevent unintended changes to the object, is it essential or beneficial to close all objects and set their object variables to nothing before ending the sub or function when there is no further use for those objects? If no longer essential or beneficial, with which version of Office did that become the case? If it is essential or beneficial, why? Does doing so help reduce file-size bloat over time (due to temporary objects)? Thanks.

Answer 1

https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-lockedits-property-dao

I am afraid it is not such simple as you displayed and tested.

Please understand the meaning of lock.

You can refer one of some MS documents in above url.

Answer 2

also with a huge recordset.

You open it as table or dynaset move last 1000 times,it will stackoverflow.

But if you open and paired with close,no problem happen.

For the rust of your program,you'd better use close and nothing for cleaning.

Of course,as to very simple codes as you have shown,it is not very necessory .

But when in huge codes block and multiple process to access database,this is very important.

Answer 3

Thanks for responding, GroverParkGeorge. I am of the same opinion and have the same practice. Every "open" should have a "close", nothing should be assumed or left to chance. I am looking for a technical justification (if there still is one) because I cannot convince younger developers with my "best practices" argument.

Hi Will,

I am not that strict: it depends on the scope of a variable.

If an object is declared within a procedure, memory should be cleared after ending the procedure, and not by the developer.

Global declared objects are something different, and hard to understand.

Especially in my case, where I use quite a few global objects in a shared library database to build dynamical applications.

Imb.

Answer 4

I think enforcing consistency is a higher priority than relying on what you expect to have happen.

Answer 5

I think enforcing consistency is a higher priority than relying on what you expect to have happen.

Hi George,

I am afraid that I interpret your answer in a different way than you meant.

Can you elaborate a little more on that?

Thank you.

Imb.