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

if with DAO or ADO,leaving a recordset open will lock the table or database.

Answer 2

I'm of the opinion that, as a professional, you should want to adopt the most professional practices.

While it isn't always necessary to explicitly clean up after using the objects, I think of it the same way as I do watching my granddaughter drag toys out of the toybox. She doesn't have to put them away--mom or dad or grandpa can and often do take care of it.

On the other hand, there is a bigger issue at work; that is learning to take care of her toys and cleaning up after herself.

Answer 3

I'm afraid that isn't so, peiyezhu. In my version of Access (the latest at time of this posting, see version below), a table is not locked if a DAO recordset is opened but not closed. Attached is a screenshot of a subroutine that opens a table in a DAO recordset (but doesn't close it): after running the VBA, I was able to open the backend database and the table in question, then edit a record (changing "East" to "NOWHERE"). The table was therefore not locked. I am using Microsoft® Access® for Microsoft 365 MSO (Version 2405 Build 16.0.17628.20006) 64-bit.

Answer 4

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.

I know that one cause of database corruption is the presence of artifacts leftover from temporary objects Access creates to perform certain tasks, but which are not always automatically deleted and the space freed when the object is no longer needed (see section "Database files grow with use" in this MS article on compact-and-repair). In what I read, it isn't clear if those "temporary objects" include objects opened with VBA or if closing them helps reduce file bloat and delay corruption.

Answer 5

Sorry to have been flippant about it.

I think your comment says it all, though. "nothing should be assumed or left to chance."

To be honest, I've never delved into the issue for VBA because it sometimes feels like the urge not to clean up comes from lack of commitment to good (or best) practices. I.e. it's okay to just get by with the least effort.