The CurrentDb Function

Access only ever 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,

DBEngine(0).Databases.Refresh Debug.Print DBEngine(0)(0).Name that rebuilds the collection, placing the current database in the first position in the Databases collection. This of course can be a pain, not to mention the huge performance hit your code experiences every time you want to use the current database.

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 will generate 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, you get nothing for 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) is roughly 60 times slower than DBEngine(0)(0). So why would you use it?

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

+1 0

Post a comment