Libraries | Beginning Access 2007 VBA

So far, all the code you have written works only from within the database where you created it. There may be times when you want to reuse code across multiple Access projects, such as when performing a common task that multiple databases might need to perform. You can create libraries based on a specific Access databases that can then be referenced from and used by other Access databases. However, in order to be a good candidate for a library, the database should contain only generic procedures and functions, and not specific references to forms or other objects that may not be present in the other applications or databases from which the library might be used.

Try It Out-Creating and Using a Code Library

In this example, you will create a new Access database containing a sample error-handling procedure that you will make available to other Access databases. This error-handling procedure was described in Chapter 2. You will add the new database as a Library and then use the Library from another database.

Create another new database, by selecting the Office Button New. From the list of available templates, select “Blank Database.” Name the new database ErrorHandlerLibrary and click the Create button.
Insert a new module into the ErrorHandlerLibrary database. Name the module modError.

Add the following procedure to the new modError module:

  Public Sub GeneralErrorHandler(lngErrNumber As Long, strErrDesc As String, strModuleSource As String, strProcedureSource As String) Dim strMessage As String 'build the error message string from the parameters passed in strMessage = "An error has occurred in the application." strMessage = strMessage & vbCrLf & "Error Number: " & lngErrNumber strMessage = strMessage & vbCrLf & "Error Description: " & strErrDesc strMessage = strMessage & vbCrLf & "Module Source: " & strModuleSource strMessage = strMessage & vbCrLf & "Procedure Source: " & strProcedureSource 'display the message to the user MsgBox strMessage, vbCritical End Sub

Save the procedure by selecting the Save button on the toolbar.
Select Debug Compile ErrorHandlerLibrary to make sure there are no compiler errors.
Close the ErrorHandlerLibrary database.
Reopen the Ch11CodeExamples database. From the Visual Basic Editor, select Tools References and click the Browse button. Change the Files of Type filter to Microsoft Office Access Databases (*.mdb). Navigate to the location of the ErrorHandlerLibrary database, as shown in Figure 11-15. Click the Open button.

Figure 11-15

You should now see the new ErrorHandlerLibrary in the list of available references, as shown in Figure 11-16. Click the OK button to add a reference to this library to the Ch11CodeExamples database.

Figure 11-16

Add the following procedure to the existing module in the Ch11CodeExamples database and replace the word module1 on the next to last code line below with whatever your module is named.

  Sub TestLibrary() On Error GoTo HandleError     'declare variable to store result     Dim intResult As Integer     Dim intValue1 As Integer     Dim intValue2 As Integer     intValue1 = 5     intValue2 = 0     'calculate result by dividing first value by second value     intResult = intValue1 / intValue2 Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, "module1", "TestLibrary"     Exit Sub End Sub

Execute the TestLibrary procedure from the Immediate Window by typing TestLibrary and pressing Enter.
You should see a screen similar to that shown in Figure 11-17 displaying an error message.

Figure 11-17

How It Works

First, you created a new Library by placing a GeneralErrorHandler error-handling procedure into a new database called ErrorHandlerLibrary. The procedure accepted some parameters regarding the error and then used the parameters to display a meaningful message to the user.

 Public Sub GeneralErrorHandler(lngErrNumber As Long, strErrDesc As String, strModuleSource As String, strProcedureSource As String) Dim strMessage As String 'build the error message string from the parameters passed in strMessage = "An error has occurred in the application." strMessage = strMessage & vbCrLf & "Error Number: " & lngErrNumber strMessage = strMessage & vbCrLf & "Error Description: " & strErrDesc strMessage = strMessage & vbCrLf & "Module Source: " & strModuleSource strMessage = strMessage & vbCrLf & "Procedure Source: " & strProcedureSource 'display the message to the user MsgBox strMessage, vbCritical End Sub

After creating and saving the new procedure, you closed the ErrorHandlerLibrary database. You then returned to the Ch11CodeExamples database and added a reference to the ErrorHandlerLibrary database. After adding a reference to the library, you then added the TestLibrary procedure to make use of the GeneralErrorHandler procedure contained in the library. The TestLibrary procedure simply caused a divide-by-zero error so the error-handling code executed.

 Sub TestLibrary() On Error GoTo HandleError     'declare variable to store result     Dim intResult As Integer     Dim intValue1 As Integer     Dim intValue2 As Integer     intValue1 = 5     intValue2 = 0     'calculate result by dividing first value by second value     intResult = intValue1 / intValue2 Exit Sub

The error handler code that follows called the GeneralErrorHandler procedure that is contained in the separate ErrorHandlerLibrary Access database that you added as a Library.

 HandleError:     GeneralErrorHandler Err.Number, Err.Description, "module1", "TestLibrary"     Exit Sub End Sub

That’s all there is to creating a new code library and using the library from other Access databases! You could extend the above example by adding additional error-handling procedures to the library. For example, you may want to create a logging procedure that logs all or certain errors to the database. You may want to create a procedure that e-mails the administrator when certain types of errors occur. These procedures are beyond the scope of this chapter, but I wanted to give you some additional ideas on how to expand the ErrorHandlerLibrary for use in your other projects.