Closing and Destroying Database Object References

There has been a great deal of confusion about whether to explicitly close and destroy object references to the current database. Some of the most highly regarded experts in the field have publicly clarified this issue many times, but many still seem to cling to the fear that doing so will blow their database up. This section attempts to lay that fear to rest once and for all.

The problem stemmed from the fact that in Access 2.0, if you called the Close method against DBEngine(0)(0)or CurrentDb, the call would fail, but problems would occur with any open objects, specifically recordsets. This resulted either in an application hang, or with Access refusing to close. Following the fix to this bug (where the internal "OK to close?" check routine was moved from the end of the method, to the beginning), calls to dbs.Close issued against either DBEngine(0)(0)or CurrentDb now do absolutely nothing to the permanent internal database object. Many people still believe that this long dead bug still exists, and warnings about it still resound in the halls of UseNet. If it gives you a warm fuzzy feeling inside, you can call Close but any attempt to do so against DBEngine(0)(0)or CurrentDb will literally do nothing. Therefore, dbs.Close is redundant.

Some people have experienced bugs with the DAO Recordset object, in particular, the RecordsetClone object, where an orphaned reference sometimes prevents Access from closing. There has never been any such bug with the Database object.

Destroying object references is a different affair. For the present, you still should set Database object variables to Nothing when you have finished with them, as you would with any other object reference. It is perfectly safe to do so, regardless of whether the reference came from DBEngine(0)(0)or CurrentDb. Setting myObj = Nothing decrements the internal object reference count by one. When the reference count reaches zero, the object is destroyed. But because Access maintains a permanent internal reference to the current database, this will not destroy the internal object, and thus will never have any effect on it.

DAO Object Properties

Every Access object (such as forms and reports) has a collection of properties. This section examines some of those properties, and describes how to use them to change Access and DAO object behavior.

All the properties associated with an Access object exist from the moment you create the object. DAO object properties, however, exhibit quite different behavior. In DAO, depending on the object, not all its properties exist until you set its value. It is quite important, therefore, that you understand the differences between the types of properties used in DAO.