6.5. The Databases Collection
Using DAO, you can have more than one database open in Access at any time. If you're using an Access .mdb, you already have one database open (called the current database). Using the Workspace object's OpenDatabase method, as shown in the example in section on Using Transactions, you can open more than one database, and operate on them under the same workspace context. Indeed, if you were to define more than one Workspace object, you could have several databases open, each operating under a different workspace context. The choice is yours. The Databases collection contains and manages these databases.
6.5.1.
6.5.1.1. The Default (Jet) Database
Unless you're working with an Access Data Project, when Access starts, it creates a default Microsoft Jet database for you to work with. This default database is automatically added to the Databases collection.
Among its properties and methods, the Database object contains five collections: TableDefs, Containers, QueryDefs, Recordsets, and Relations. Each of these collections and their respective objects and properties are discussed in later sections. In most cases, you will be working with the default Microsoft Jet database, which you can refer to using any of the following syntaxes:
DBEngine.Workspaces("#Default Workspace#").Databases(0)
DBEngine.Workspaces(0).Databases(0)
DBEngine(0).Databases(0)
DBEngine(0)(0)
CurrentDb()The current user's default database is an object that you will use quite a lot. Although you can work with it using any of the reference methods listed above, in most cases it is often more convenient to assign it to an object variable.
Dim dbs As Database Set dbs = DBEngine(0)(0)
But far and away the most common method is to use the CurrentDb() function, described below.
6.5.1.2. 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 byt 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 PropertyThis 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).
6.5.1.3. Opening an External Database
Sometimes you need to work with data in another Access database, a dBase IV database, or Excel spreadsheet, but you don't want a permanent link. You can do so by opening a temporary connection to it with the Workspace object's OpenDatabase method.
Although this method belongs to the Workspace object, I am describing it in this section because the end result is that a new (albeit temporary) Database object is added to the Databases collection.
The OpenDatabase method is fairly straightforward.
Set dbs = wrk.OpenDatabase(dbname, options, read-only, connect)
The following table describes the OpenDatabase method arguments.
The following example code demonstrates how to open several different databases using different techniques. Specifically, it opens the following databases from the following five sources:
A second instance of an SQL Server database, using an existing connection
After opening each database, you'll notice that the code prints the name of the database, and a count of the respective Databases collection. Take particular notice of the database names and collection counts for the ODBCDirect databases.
You can see that the database name is that of the connection—not the database. This is because ODBCDirect has established a connection to the DSN, rather than the database; so it is the DSN's name that is returned.
Public Sub OpenSeveralDatabases(strUsrName As String, strPwd As String)
Dim wsJet As Workspace
Dim wsODBC As Workspace
Dim dbJet As Database
Dim dbdBase As Database
Dim dbODBC As Database
Dim dbODBCDirect As Database
Dim dbODBCDirect1 As Database
Dim cn As Connection'Create the Jet and ODBCDirect workspaces
Set wsJet = DBEngine(0)
Set wsODBC = DBEngine.CreateWorkspace( _
"", strUsrName, strPwd, dbUseODBC)
'Print the details for the default database
Debug.Print "Jet Database "; wsJet.Databases.Count & _
" - " & CurrentDb.Name
'Open a Microsoft Jet database - shared - read-only
Set dbJet = wsJet.OpenDatabase("C:Tempdb1.mdb", False, True)
Debug.Print "Jet Database "; wsJet.Databases.Count & _
" - " & dbJet.Name
'Open a dBase IV database - exclusive - read-write
Set dbdBase = wsJet.OpenDatabase( _
"dBase IV;DATABASE=C:Tempdb2.dbf", True, False)
Debug.Print "Database "; wsJet.Databases.Count & _
" - " & dbdBase.Name
'Open an ODBC database using a DSN - exclusive - read-only
Set dbODBC = wsJet.OpenDatabase( _
"", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN")
Debug.Print "Jet Database "; wsJet.Databases.Count & _
" - " & dbODBC.Name
'Open an ODBCDirect Connection using a DSN - read-only
Set cn = wsODBC.OpenConnection( _
"", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN")
'Get a reference to the default ODBCDirect database
Set dbODBCDirect = wsODBC.Databases(0)
'This could so be written as: Set dbODBCDirect = cn.Database
Debug.Print "ODBCDirect Database "; wsODBC.Databases.Count & _
" - " & dbODBCDirect.Name
'Open a second database reference using the ODBCDirect connection
Set dbODBCDirect1 = wsODBC.OpenDatabase( _
"", dbDriverComplete, True, "ODBC;DATABASE=myDB;DSN=myDSN")
Debug.Print "ODBCDirect Database "; wsODBC.Databases.Count & _
" - " & dbODBCDirect.Name
'Clean up
cn.Close
wsJet.Close
wsODBC.Close
Set dbJet = Nothing
Set dbdBase = Nothing
Set dbODBC = Nothing
Set dbODBCDirect = Nothing
Set dbODBCDirect1 = Nothing
Set cn = Nothing
Set wsJet = Nothing
Set wsODBC = Nothing
End Sub6.5.1.4. 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. In this section, we will try to lay that argument 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. However, although you can call Close if it gives you a warm fuzzy feeling inside, 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 since 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.