I'm actually doing this in Excel vba, but I believe it's exactly identical using access or excel, and I worry about tiring out my excel forum colleagues with so many discussions, SO:

When I manipulate objects (tables, query defs, etc) in a database that I'm access via DAO, I'm unsure of the correct way to close the database. In fact this question applies to a lot of things I do in VBA.

At some point in the code, there's a place where an object is SET to basically "open" something.

• using FSO, declare TextStream, set TextStream to "open" a text file
• using DAO, declare DAO database, set it to "open" an actual path to a database file

are both examples of this.
I am learning these things, and it almost seems that in some cases the files really are OPEN in the conventional sense (i.e., use the code to close them or risk having an invisible file actually open on your computer) (and I know this is how it works when you use Excel VBA to open a workbook - if you don't use WorkbookObject.Close (or ApplicationObject.Quit), it REALLY WILL be still open and probably invisible.

So right now I'm using some stuff like:
Dim myDb As DAO.Database
Set myDb = DAO.OpenDatabase("Filename")
'and then I do some stuff.

When I done 'doing some stuff', is it enough to SET myDb to NOTHING? Is that the same as closing it in real life? Or does that just save overhead within my code, but doesn't really close the object in real life?

Is my confusion making sense? So it's kind of 2 questions.......1) are things really open and when to close them, and 2) does SETTING it to nothing equal closing it in real life.

Here is my motto ... If you Open it, then Close it ...

Setting to Nothing is not the same as Closing, and setting an object variable to Nothing is not neccessary in VBA for clean up. In actuallity, VBA was designed in such a way the even Closing is not "needed", but -- its really hard for many (including myself) to let go of the motto I stated above. Its just a good practice that removes uncertainty when computers don't act the way they should!

Some may argue that setting to Nothing is a good practice as well, and I don't disagree, but object reference variables are different than the actual "object". So, I stand by the motto above.

Ok, I can live with that....If it's a safe motto for you, it's probably ultra-safe for me (IMG:style_emoticons/default/smile.gif)

can I add one sub-question?

What if I'm doing this within my error handler. I've learned to do:


What about closing? If not isopen() ?? Or does it vary...

thanks Brent!!

This post has been edited by ipisors: Apr 10 2012, 06:18 PM

It is important to close connections otherwise they are left to time out. Closing recordsets maybe but setting to Nothing I doubt that it matters.

I walked a middle line for a while and have a function that accepts an object as an argument. It goes through closing and setting to Nothing yet the call is unobtrusive in the code.

As a comparison it is worth considering the normal practice in VB.NET where is is recommended NOT to set an object to Nothing in the procedure exit. This is because the object is retained in memory until the last time it is referenced. After this point the garbage collection in the Common Laguage Runtime automatically removes it. The extra reference in the exit actually means it is retained in the memory beyond its requirement.

Of course VBA may be different since it is not run in CLR.

My advice is to stick with the clearing of the object in VBA at least unitl you can reliably code evertyhing using best practices. Until then, when something doesn't work to expectations your boss might get an expert in to look at the problems. If that expert tells your boss that you are not closing things like you should it will be bad for your reputation. Later you can consider dropping it when you can be sure you won't have major troubles and can hold your own against an expert.

Hi Isaac

I'm also a bit unclear about how to correctly close objects created in code, and although Brent states >>VBA was designed in such a way the even Closing is not "needed"<< I've found cases where you do need to explicitly close an object, so my exit routine in all subs/functions that create object variables is always:
e.g.


This may not be strictly necessary but helps me sleep easier at night! (IMG:style_emoticons/default/smile.gif)

hth

Ok thanks. it all comes together from all of you then...Theory might lead to "it's not necessary", but in practice for the sake of safety, and the sake of someone still beginner/intermediate like me not being seen as sloppy coding, might be a good idea to continue.

I'm not 100% understanding on my question about whether in the examples I gave, the file is "really open", in the traditional sense of files on a computer. However, it probably doesn't really matter...I'll just make sure they get 'closed'!

Files opened via the FSO must be closed or they will not be accessible again until the lock times out.

The disputed issue is the need to explicitly clear the object variables from memory by setting them to Nothing.

But when I run my code with FSO to opentextstream, while the code has the file 'open', and is in Break mode, and I go look at the file in the folder, i actually can open it. Unlike if a person in my company actually had the file open, and then I went to open it.

This is partly why I was confused. I want to believe that, but it doesn't seem to be born out by reality so I'm confused.