Library Databases | Beginning Access 2002 VBA (Programmer to Programmer)

All of the code (as well as the forms) we have written so far only works in the database in which it was written. There is nothing wrong with this, since in most cases it's exactly what is required, but given the advantages of code reuse, wouldn't it be good to share your hard work among other Access applications of yours, or even allow others to use it?

A library database is just a repository of these fragments of code, forms, and so on, which you would like to use elsewhere. It allows you to write normal VBA code, in a normal Access database, and then allow it to be used in any other Access database. The advantages of doing this are enormous . By inserting functions and classes that are generic (can be used in more than one project) directly into a project ends up causing maintenance nightmares. Imagine that you create a class that does some wonderful thing, perhaps calculating state taxes. You drop that directly into several database projects. When it is time to update the formulas, you are now faced with remembering which projects used that class, then opening each one of those projects and fixing the formula in each project. Had you placed the class in a library and distributed the library with your projects, all you have to do is update the formulas in your library and you are done. Since all of the projects use the library, you have fixed all of the projects with a single edit.

Creating a Library Database

One of the first things I do when starting a new Access project is to create some error logging routines. Instead of just flashing up a message telling the user what the problem is, they also log the error to a table. There are two really good reasons for this:

It's a great development tool. If errors occur during your development and testing they will be logged into a table, so you don't have to keep writing them down (and invariably losing the bit of paper).
It's a great feature during run time. Very often users fall into the habit of just clicking on an error message and trying again, or phoning you to tell you something is wrong, but not writing down the error message. With this sort of logging you don't have to worry because the details will all be stored for you.

Since this feature is pretty useful, let's create a library database to do this. If your fingers are feeling a little tired there's a ready-made database called ErrorLogging.mdb for you.

Try It OutCreating a Library Database

Start Access and create a new database. This should be a standard, blank database. Call the database ErrorLoggingXP .
Create a new code module and save it, calling it ErrorLoggingRoutines . Starting with Access 2000, the default method of dealing with records is ADO. This book teaches DAO, and so we have to unselect the ADO library and set the DAO library. From the Tools menu, select References . Unselect the Microsoft ActiveX Data Objects 2.1 Library , scroll down a little, and select Microsoft DAO 3.6 Object Library . Click the OK button to close the dialog.

Add the following globals to the module:

   Private Const m_ERROR_TABLE As String = "tblErrorLog"     Private m_UserDb As Database     Private m_recErrLog As Recordset

Write this procedure which creates a table to hold the error details:

   Private Sub CreateErrorTable()     On Error GoTo CreateErrorTable_Err     Dim tblE As TableDef     Dim strSQL As String     Set m_UserDb = CurrentDb     Set tblE = m_UserDb.TableDefs(m_ERROR_TABLE)     Set tblE = Nothing     CreateErrorTable_Exit:     Exit Sub     CreateErrorTable_Err:     If Err.Number = 3265 Then     strSQL = "CREATE TABLE " & m_ERROR_TABLE & " (" & _     "ErrorID      AUTOINCREMENT, " & _     "UserName     TEXT(50), " & _     "ErrDate      DATETIME, " & _     "ErrNumber    INTEGER, " & _     "Description  TEXT(255), " & _     "Source       TEXT(50))"     m_UserDb.Execute strSQL     Else     Err.Raise Err.Number, "ErrorLogging:CreateErrorTable", _     Err.Description     End If     Resume CreateErrorTable_Exit     End Sub

Now create a new procedure, to log the errors, with the following code:

   Public Sub ErrorLog()     Dim lngNum As Long     Dim strDesc As String     Dim strSource As String     Dim errE As Error     lngNum = Err.Number     strDesc = Err.Description     strSource = Err.Source     CreateErrorTable     Set m_recErrLog = m_UserDb.OpenRecordset(m_ERROR_TABLE)     If lngNum = DBEngine.Errors(DBEngine.Errors.Count - 1).Number Then     For Each errE In DBEngine.Errors     WriteError errE.Number, errE.Description, errE.Source     Next     Else     WriteError lngNum, strDesc, strSource     End If     m_recErrLog.Close     End Sub

Next, create another procedure, to actually write the errors to the Error table:

   Private Sub WriteError(lngNum As Long, strDesc As String, strSource As String)     With m_recErrLog     .AddNew     !UserName = Trim$(CurrentUser())     !ErrDate = Now()     !ErrNumber = lngNum     !Description = Trim$(strDesc)     !Source = Trim$(strSource)     .Update     End With     End Sub

Save the module, and from the Debug menu select Compile ErrorLogging .

Close the database and open IceCream.mdb . Create a new module and add the following code. You should recognize this from our debugging code earlier in the book:

   Sub TestErrorLogging()     Dim db   As Database     Dim recT As Recordset     Dim errE As Error     On Error GoTo TestErrorLogging_Err     'open current database     Set db = CurrentDb()     'set a property on a form that isn't open - this will generate a VBA error     Forms!frmCompany.Caption = "A new caption"     'open a table that doesn't exist - this will generate a DAO error     Set recT = db.OpenRecordset("NonExistentTable")     recT.Close     TestErrorLogging_Exit:     Exit Sub     TestErrorLogging_Err:     ErrorLog     End Sub

From the Tools menu select References . Select the Browse button and change the Files of type drop down to Microsoft Access Databases (*.mdb) .
Pick ErrorLogging from this dialog:
Click the Open button, and you'll be returned to the References dialog showing the ErrorLogging database selected:
Press the OK button to close this dialog. From the Immediate window, run the procedure. Nothing visible will happen.
Now comment out the line (by placing a single apostrophe at the start of the line) that sets the Caption (see line in bold in step 8), and run the procedure again.
Switch back to Access and you should notice a new table tblErrorLog . If it's not there, press F5 to refresh the window.
Notice that although the code is actually in another database, it has created a table in the local database and added rows for each error.

How It Works

Let's look at the global variables first, and then the CreateErrorTable routine, since this is what actually creates the table in the database:

For the globals, we define a constant, which defines the name of the error logging table. Using a constant means we can easily change the name if required. Then there are two global variables. The first will point to the database that the user currently has open, and the second will be a recordset, pointing to the error logging table, that we will use to add records to the error logging table.

 Private Const m_ERROR_TABLE As String = "tblErrorLog" Private m_UserDb As Database Private m_recErrLog As Recordset

In the CreateErrorTable procedure, the first thing to do is to set some error handling. This is because we need to see if the error log table already exists, and the simplest way to do that is to just set a variable to point to it. If it doesn't exist, and an error is generated, we can trap that error:

 On Error GoTo CreateErrorTable_Err

Once the error handling is active, we define a couple of variables. The first will point to the error table, and the second will hold the SQL string that creates the table.

 Dim tblE As TableDef    Dim strSQL As String

Now we need to open the database. Notice that we use the CurrentDb function here; as CurrentDb points to the database currently open in the database window in Access. We want to build the error table in the client's database, not in the library database. So, even though this code is in a different (library) database, we still use the active database. If we wanted to access tables and so on in the database where the code is, we could use the CodeDB function.

 Set m_UserDb = CurrentDb

Now we need to see if the table exists. The TableDefs collection holds all of the existing tables, so we simply set our variable to point to the error logging table. If it exists then we can clear the reference, and exit:

 Set tblE = m_UserDb.TableDefs(m_ERROR_TABLE)    Set tblE = Nothing CreateErrorTable_Exit:    Exit Sub

If the table doesn't exist, then the error handling comes into place. We check for error number 3265 , which means that the object we were looking for ( tblErrorLog ) wasn't found in the collection (the TableDefs collection).

 CreateErrorTable_Err:    If Err.Number = 3265 Then

If the table wasn't found we need to create it, so we construct a SQL statement to do this, and then Execute the SQL statement. We used the CREATE TABLE method here because it's quite simple some other methods for creating tables were discussed in Chapter 8.

 strSQL = "CREATE TABLE " & m_ERROR_TABLE & " (" & _          "ErrorID      AUTOINCREMENT, " & _          "UserName      TEXT(50), " & _          "ErrDate      DATETIME, " & _          "ErrNumber      INTEGER, " & _          "Description   TEXT(255), " & _          "Source      TEXT(50))"       m_UserDb.Execute strSQL    Else

Since it is possible to get an error and not yet have a table to log errors into, if any other type of error is found then we use the default VBA error handler.

 Err.Raise Err.Number, "ErrorLogging:CreateErrorTable", _          Err.Description    End If    Resume CreateErrorTable_Exit

Now that you've seen how the table is created, let's look at the error logging routine itself, ErrorLog :

The first three variables will hold the error details, from the Err object. We need to save them because as you've just seen, the table creation routine uses error handling and using On Error clears Err . The last variable will hold the error details if the error is a data access error.

 Dim lngNum As Long Dim strDesc As String Dim strSource As String Dim errE As Error

The first thing to do is to store the error details, and then call the routine that creates the error table.

 lngNum = Err.Number strDesc = Err.Description strSource = Err.Source CreateErrorTable

Now we know that the error table exists, we can open it.

 Set m_recErrLog = m_UserDb.OpenRecordset(m_ERROR_TABLE)

Now we can use the same routine you saw in the debugging chapter, where we check to see if the error number matches the last error in the Errors collection. If it does, then this is a data access error, so we loop through the collection, calling the WriteError routine for each error. If it isn't a data access error, we just call the WriteError routine with the details from Err .

 If lngNum = DBEngine.Errors(DBEngine.Errors.Count - 1).Number Then    For Each errE In DBEngine.Errors       WriteError errE.Number, errE.Description, errE.Source    Next Else    WriteError lngNum, strDesc, strSource End If m_recErrLog.Close

The routine that actually writes the error details to the table is quite simple:

 Private Sub WriteError(lngNum As Long, strDesc As String, strSource As String)    With m_recErrLog

Since we are adding a new record we use the AddNew method this creates a blank record for us to add the details to.

 .AddNew

We then set the user details. The CurrentUser function returns the name of the current user. If you're not using security then this will always be Admin , but if security is set up this will be the name of the user that logged into Access. For more details on this, see Chapter 17.

 !UserName = Trim$(CurrentUser())

Next we set the date and time using the Now function:

 !ErrDate = Now()

And now the actual details of the error:

 !ErrNumber = lngNum       !Description = Trim$(strDesc)       !Source = Trim$(strSource)

Finally, we can update the record with the new details.

 .Update    End With End Sub

So that's it. Let's just summarize the concept:

A library database is a normal Access database.
Any procedures in the library database that you want other databases to use should be Public procedures. All other procedures should be Private .
To use a library database you create a Reference to it, from the Tools menu in the VBE.

Considerations

One thing to beware of when using library databases is name clashes . It's quite possible that you might have a public function in your library database that has the same name as a function in a database that is using the library database. If this happens then Access uses the local procedure, and not the procedure in the library database.

There are two ways to get around this problem. The first is to qualify the procedure name with the module name when you use it. So, assume that ErrorLog existed in both the local database and the library database. Calling ErrorLog like this:

ErrorLog

would use the local procedure. But calling it like this:

[ErrorLoggingRoutines].ErrorLog

would call the one in the library database, because we have put the module name in front of the procedure call.

The second method is to make sure your procedures won't clash , by uniquely naming them. For example:

Public Sub logErrorLogging()

This puts a unique identifier in front of each public procedure. If you make sure that each library database you create has a unique identifier then you should never get clashes, unless someone else is using the same naming convention in their database libraries, and you use it!

On the book's CD, in the Error Handling folder, you can find a wizard that automatically inserts error handlers into existing VBA code. To install the wizard, follow the instructions in the ReadMe.txt file.