Reader Question: Do DAO Objects Need to be Closed?
Let's explore the conflicting opinions and scarce documentation on whether you need to close your recordsets and databases in Access.
Reader Jörgen Rindstedt writes in with the following question (shared with permission, emphasis mine):
You have a while ago stated that that it is unnecessary to explicitly close a recordset as it will automatically close when the sub or function closes. Does this mean that the statement:
Symptoms
A Microsoft Access database has begun to bloat (or grow rapidly in size) after you implement Data Access Objects (DAO) to open a recordset.
Cause
If you do not release a recordset's memory each time that you loop through the recordset code, DAO may recompile, using more memory and increasing the size of the database.
Resolution
To avoid consuming unnecessary resources and increasing database size, use the Close method of the Recordset object to explicitly close the recordset's memory when you no longer need the recordset.
If the database has increased in size because you did not use the Close method of the Recordset object, you can reduce the size of the database by running the Compact and Repair utility (on the Tools menu).
on How to prevent database bloat after you use Data Access Objects (DAO) is wrong?
Great question, Jörgen!
In general, it is true that objects in VBA do not need to be explicitly set to Nothing, as I wrote about here:
Mike Wolfe
Apparently, the DAO objects, however, exhibit "poor teardown behavior." I have not seen this manifest itself in my own applications, but the comment comes from a reliable source (Matthew Curland, author of Advanced Visual Basic 6) who writes:
... Data objects, such as DAO, provide [an] example of poor teardown behavior. DAO has Close methods that must often be called in the correct order, and the objects must be released in the correct order as well (Recordset before Database, for example).
Regrettably, I've never been able to find any more detailed information about what Matthew was referring to with regard to DAO's "poor teardown behavior."
Furthermore, Matthew's book was published in 2000 and the article Jörgen references was likely posted sometime before 2010 (based on the fact that the "Applies to" section of the article lists Access 2007/2003/2002). It's possible that whatever "teardown" problems existed in the DAO library have been addressed.
All that said, I'm generally in favor of the Zen of Python approach that "Explicit is better than implicit." And explicitly closing and disposing of your DAO objects is not likely to cause problems, though it may help avoid problems.
As I said, I'm not personally in the habit of closing and dereferencing (i.e., Set MyVar = Nothing) my DAO objects. I haven't been burned yet.
I see three possible reasons for that:
- Explicitly closing DAO objects is unnecessary and simply an example of cargo cult programming
- Explicitly closing DAO objects was necessary at one time, but improvements to the library have fixed its earlier bad behavior
- I have been lucky to this point and one day my luck will run out
Honestly, I think all three possibilities are equally feasible.
Given that, the least risky approach would be to explicitly close and release all of your DAO objects.
As for me? I probably won't change. After all, I said the least risky approach was to close and release all of your DAO objects, not all of my DAO objects.
I prefer to live dangerously!!!!
