Handling Objects (Opening/Closing)

[jmarchal1955]

I wonder if there are any best practices about handling objects in Access VBA. For example, if I have several modules that need to use an object, like FileScripting, or the same Recordsets for example. Is it preferred to create each object in each module and close it when done? Or is it preferred to create Public objects that can be used from anywhere? I have programmed a lot in Access VBA, and this has always been an issue for me. Thanks very much.

[June7]

Objects take up memory. I prefer not to instantiate if they aren't needed. If the object is needed for every single procedure (or at least a lot of them) then instantiating when db opens might be efficient. Just depends on the object and how it is used. If you open a recordset but then edit the table(s) the recordset is based on, the edits will not be available in the recordset, unless maybe it is a RecordsetClone.

Would there be any real gain in performance? I doubt it.

Maybe copy/paste is a devil in disguise (so easy to replicate code) and one must make concerted effort not to get carried away with copy/paste and consolidate code whenever possible.

[jmarchal1955]

Thanks very much for your response June7. This has always been a concern, mostly because I want to write the best code I can. Good point about the Recordsets, I had not considered that. I was just concerned about the overhead to create, use, close, and set to "Nothing" each time I used an object. Thanks again.

[robrich22]

I would not personally advise to reuse any objects. When an object goes out of scope, the memory will be reclaimed. You do not need to worry about that. Declare the variables close as you can to where they are used and limit their scope to only what is necessary.

[ItsMe]

For automation it can be feasible to create a class module. I will typically copy and paste code snippets into forms' class modules as sub procedures before relying on a stand alone Class Module.

An example where I have employed a class module would be using an API offered by a third party. When the application I am developing is centered around automation of another app, it makes sense to have the API available to many of the form's modules. Placing the API in a Standard Module is not efficient compared to using a Class Module where you can create and destroy objects, reserving system memory when needed and releasing it when finished.