Functions to Open and Close DAO Database

This post describes how to create functions to open and close MS Access database. The functions involve two common methods provided by MS Access, they are OpenDatabase and Close. Opening a database, especially  used in connecting to tables and queries is the first process in accessing database. Meanwhile, closing a database is the last process in accessing database.

Opening Access Database

To open Access database, we use letDbsOpen function. The letDbsOpen function has two options for database sources. Between the two options, namely local database and external database, we set which database should be opened. If local database is set, we open current Access database. Meanwhile, if external database is set, we open external Access database.

The letDbsOpen function is as follows:

Option Compare Database
Public daoDbs As DAO.Database

Function letDbsOpen(Optional boolUseLocalDbs As Boolean)
' Parameter: boolUseLocalDbs = Optional boolean expression whether using
' local database or not. The boolUseLocalDbs is set to True using
' CurrentDb.

  Dim strLocation, strConString as String 
On Error GoTo Err_Msg
  If boolUseLocalDbs Then
    Set daoDbs = CurrentDb()
  Else
    strLocation = getDbsLocation()
    strConString = getDbsConString()
  
    If daoDbs Is Nothing Then Set daoDbs = OpenDatabase(strLocation , False, False, strConString )
  End If
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function letDbsOpen, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

Before the letDbsOpen function is executed, we declare global variable namely daoDbs at the top of a module. This global variable will be employed when we manage recordsets and it is set using letDbsOpen function. In a one operation, we can only open one database, either local or external database. If we open local database, then we use CurrentDb method, as scripted in line number 8. Otherwise, we use OpenDatabase method to open external database. This method selection is set using boolUseLocalDbs parameter. If this parameter value is True, then we use CurrentDb to set daoDbs, otherwise, use OpenDatabase.

If we use OpenDatabase method, which mean to open external Access database, then we should get the file path name and its database connection string. Therefore, we set the file path name using strLocation variable and database connection string using strConString variable. The strLocation is set using getDbsLocation function, while the strConSting is set using getConString function.

After both strLocation and strConString are got, we set the daoDbs. At this point, the database is opened and ready for use.

Implementation:

Generally, we apply the letDbsOpen function in a form's Open event or at the beginning of a button's Click event. If we want to display a collection of recordsets, use form's Open event. If we want to display single recordset and edit it, use button's Click event. Example:

We can write the letDbsOpen in Immediate Window:

Suppose that we have two separated Access databases, back-end database and its application in front-end database. The file path name of Access back-end database, which contains tables and queries, is "D:\Access\Database_be.accdb" as mentioned in getDbsLocation. The current application behave as front-end database, which use Access database and contains no tables and queries in it, is named MeAccess.accdb and is located in the same folder with the back-end database. See the figure below.

Calling letDbsOpen will open database as stated in "D:\Access\Database_be.accdb":

?letDbsOpen

If the tables and queries are in the same file with Accss application, calling letDbsOpen True will open current database, it is "D:\Access\MeAccess.accdb".

?letDbsOpen True

Closing Access Database

To close a database, simply use the following function:

Function letDbsClose()
On Error GoTo Err_Msg
  If Not (daoDbs Is Nothing) Then
    daoDbs.Close
    Set daoDbs = Nothing
  End If
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function letDbsClose, Error # " & Str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.Description
  Resume Exit_Function
End Function

Implementation:
Executing the letDbsClose will close a database that has been set by daoDbs. If daoDbs is not set, then letDbsClose function will do nothing. Generally, we apply the letDbsClose function in a form's Close event, at the end of a button's Click event, or we do not need the database any more. Example:

?letDbsClose

Typing the above function in Immediate Window will close the database that was previously set by daoDbs.