Four of the DAO objects are considered temporary objects. That is, they exist only in memory and are never permanently saved as part of the database file. These are the Workspace, Database, Connection, and Recordset objects. QueryDef objects can be either permanent or temporary.
Note QueryDef objects are not typically temporary objects. As mentioned earlier, a QueryDef object represents a saved query in the database. However, unlike other permanent objects, if a QueryDef object is created by using DAO, the QueryDef object can be used without first appending it to the QueryDefs collection.
Another slight exception is the Database object. Although it’s a temporary object, it does refer to the database file on disk, which is permanent.
Because these objects exist in memory, you should close them explicitly when your code no longer needs them. To close an object explicitly, use the Close method and then set the object variable to Nothing.
Prior to Microsoft Jet version 2.0, you couldn’t close a Workspace object that contained any open Database objects. In Microsoft Jet 3.0 and 3.5, if you close a Workspace object, Microsoft Jet closes all the Workspace object’s Database objects and rolls back any pending transactions.
Microsoft Access Users In Microsoft Access, a default workspace, Workspaces(0), is automatically created when you open a database through the user interface. Microsoft Access ignores attempts to close this default Workspace.
If you open a database with the OpenDatabase method, you should close it explicitly when you are done using it. To close a database explicitly, first use the Close method to close the database, then set the Database object variable to Nothing.
In DAO 3.0 and 3.5, if you explicitly close a Database object that has an open Recordset object with pending edits or uncommitted transactions, the edits are canceled, the transactions are rolled back, and the Recordset object is closed. If you implicitly close the same Database object (by exiting a procedure that has the Database object as a local object variable), the Recordset object stays open.
Note This behavior is different from the behavior of DAO version 2.x. In DAO 2.x, if you explicitly close a Database object that has an open Recordset object, an error occurs. If you implicitly close the same Database object, the Recordset object is closed as well. DAO 2.x had Dynaset objects (replaced by dynaset-type Recordset objects in DAO 3.0). If you explicitly or implicitly close a Database object that has an open Dynaset object, the Dynaset object stays open.
You should always close a Recordset object after you finish using it. Close the Recordset object by using its Close method. Then set the Recordset object variable equal to Nothing.