The CurrentDb() Function
Access always maintains a single permanent reference to the current database. The first member of the Databases collection is populated with a reference to the current database at startup. This reference, pointed to by DBEngine(0)(0), is fine under most circumstances, but when, for example, you are working on wizards, it is not always up-to-date. In these circumstances it is possible for the first database collection member to point to something other than the default database. The chance of this occurring in normal databases is negligible, but to ensure that you are working with the current database, you need to execute the Refresh method, which rebuilds the collection, placing the current database in the first position in the Databases collection. This can be annoying, of course, , but in addition, your code experiences a huge performance hit every time you want to use the current database.
DBEngine(0).Databases.Refresh Debug.Print DBEngine(0)(0).Name
The solution that Microsoft came up with was to provide the CurrentDb() function. CurrentDb (the parentheses are optional) is not an object; it is a built-in function that provides a reference to the current user's default database. Although they do refer to the same database, it is essential that you understand two important concepts.
CurrentDb and DBEngine(0)(0) are not the same objects internally. Access maintains a single permanent reference to the current database, but CurrentDb temporarily creates a new internal object - one in which the collections are guaranteed to be up-to-date.
When CurrentDb is executed, Access creates a new internal object that recreates the hierarchy and refers to the current database. The interesting fact is that immediately after CurrentDb executes and returns a pointer, the internal object is destroyed.
For example, the following code generates an error because the reference to the current database is lost immediately after the line containing CurrentDb executes:
Dim fld As DAO.Field Set fld = CurrentDb.TableDefs(0).Fields(0) Debug.Print fld.Name
This is the case for most DAO objects. One notable exception to this is the Recordset object, for which Access tries to maintain the database reference. To use CurrentDb effectively, it is always wiser to assign the reference to an object variable.
Dim dbs As DAO.Database Dim fld As DAO.Field Set dbs = CurrentDb Set fld = dbs.TableDefs(0).Fields(0) Debug.Print fld.Name dbs.Close Set dbs = Nothing
Of course, nothing is free, and CurrentDb is no exception. The price you pay for the convenience and reliability of a function like CurrentDb is a considerable performance hit. CurrentDb is (in my tests) roughly 60 times slower than DBEngine(0)(0).
The reason you would use CurrentDb in preference to DBEngine(0)(0)is that you can rely on its collections being up-to-date. For the majority of cases, the performance hit experienced using CurrentDb is not an issue because it is highly unlikely that you will ever call it in a loop. The recommended method for setting a reference to the current database is as follows:
Private dbC As DAO.Database Public Property Get CurrentDbC() As DAO.Database If (dbC Is Nothing) Then Set dbC = CurrentDb Set CurrentDbC = dbC End Property
This Property procedure can be used in both class modules and standard modules, and relies on the existence of a Database object variable declared at module level. If you want, you can change it to a function instead; it will work just the same. The reason it checks dbC is that variables can be erased (and thus the reference lost) when an error occurs somewhere in your application, or if someone hits Stop in the IDE (integrated development environment).
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields