MS-Access / Getting Started

---

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).