home   Java Script   MS Access   Perl   HTML   Delphi   C ++   Visual Basic   Java   CGIPerl   MS Excel   Front Page 98   Windows 98   Ms Word   Builder   PHP   Assembler     Link to us   Links    

Previous Page TOC Index Next Page Home


Leveraging Your Application: Creating Your Own Libraries

What Is a Library Database?

As your knowledge of the VBA language expands and you become more proficient as a VBA programmer, you will probably develop functions and subroutines that you would like all your databases to share. Without the use of library databases, the code in each of your databases is an island unto itself. Although the functions and subroutines within your Code modules can be called from anywhere within the same database, these procedures cannot be called from a different database.

Without a shared library of code and other standard objects, you will find yourself copying routines and other database objects from one database to the next. The library database, once created, can be utilized by all your applications and distributed to all your users. A library database is just like any other database. It is simply a collection of procedures and objects that you want to share among numerous databases. The only difference between the library database and other databases is in the way the database is referenced. Rather than opening a library database to utilize its functionality, you reference it from another database.

Access itself is highly dependent on library databases. The Table Wizard, Form Wizard, Report Wizard, Database Wizard, Database Splitter, Database Analyzer, and Database Documentor are all examples of tools that reside in library databases. In fact, all the wizards, builders, and menu add-ins that you are accustomed to utilizing while developing your applications are all contained within library databases. Wizards, builders, and menu add-ins are covered in Chapter 28, "Working with and Creating Add-Ins, Wizards, and Builders." This chapter focuses on creating library databases in general and specifically on placing generic functions in a library database, thereby making them available to all your application databases.

Preparing a Database to Be a Library

Creating a library database involves three steps:

  1. Writing the functions and creating the objects

  2. Renaming the database with an .MDA extension

  3. Loading the database as a library

You begin by creating the generic objects that you want to share among your applications. To follow standards, you should rename the database with the extension .MDA, the naming standard for all Access library databases. To load the database as a library, you must reference it from another database. This process is covered in the next section.

Before you are ready to reference a database as a library, you need to think about how to properly construct the database so that it best serves you as a library. Although a library database is just a normal database, planning the design of the library is integral to its success and usefulness. Improper planning can cause anything from extra memory being required to the database malfunctioning.

Structuring Code Modules for Optimal Performance

Library databases contain the general functions that you use in most of your applications. Because of the way Access loads Code modules, to achieve optimal performance you must structure your library databases effectively.

Access 2.0 loads all Code modules the moment the application loads. This means that when developing an Access 2.0 application, it is not particularly important how you structure your subroutines and functions within the various modules of the database. This situation has dramatically changed with Access 95, which loads Code modules only if they are needed. In Access 95, if no procedures within a particular module are called, the module is never loaded into memory. On the other hand, if a single subroutine or function is called or if a Public variable is referenced, the entire module loads. Therefore, it is crucial that you structure your modules to minimize what is loaded into memory. The following techniques help you to accomplish this task:

Although in general you want as little to load into memory as possible, the converse is true for commonly used functions. By placing frequently used procedures in the same module, you ensure that they are loaded in memory and can be accessed quickly when they are called. This improves the performance of your application.

Writing Library Code that Runs

Code that runs perfectly within a normal database might not run as expected when it is part of a library database. One good example is the CurrentDB function. As you have seen throughout this book, the CurrentDB function is a commonly used function that allows you to reference the current database. You would think that the CurrentDB function references the database in which the code is running, but this is actually not the case. The CurrentDB function specifically references the database that is active within the user interface. If a library function refers to CurrentDB, it does not refer to itself; instead, it refers to the application database that is calling the library function. If you want to refer to the library database, you must use the CodeDB function. The CodeDB function always refers to the database in which the code is running. You must decide whether CurrentDB or CodeDB is applicable for each situation.

Compiling the Library

It is important to compile a library database before you distribute it. This ensures that it provides optimal performance. If library code is not compiled, it compiles each time it is accessed, which significantly degrades the performance of your application. The compilation process, and its benefits, are discussed in detail in Chapter 23, "Optimizing Your Application." In short, when you have completed all changes to the library database you should select Run|Compile All Modules followed by File|Save All Modules. These two commands must be re-executed each time changes are made to the library database.

Creating a Reference

A reference is Access's way of locating a library database so that it can utilize the code within it. References can be established in three ways:

Creating a Library Reference

You can create a Library reference by adding the library to the Menu Add-ins section of the Windows Registry (see Figure 27.1). The Menu Add-ins section is found under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0. This type of reference is limited in that it allows the functions of the library database to be invoked only as an add-in. Creating a Library reference is covered in more detail in Chapter 28.

Figure 27.1. Viewing the Menu Add-ins section of the Windows Registry.

Creating a Runtime Reference

Creating a Runtime reference involves establishing a reference to the library at runtime using the Run method of the Application object. This method of creating a reference actually opens the library database and executes the specified function. It uses OLE Automation to accomplish this task.

The major advantage of this technique is that the library code is not loaded into memory until it is used. Furthermore, using this technique, additional modules in the call stack are not loaded into memory unless they are explicitly called. Creating a Runtime reference does have a few disadvantages, though:

Figure 27.2. Viewing the AddInPath key of the Windows Registry.

The following code is found in the CHAP27EX.MDB database on the sample code CD:

Sub AppRun()

   If Application.Run("Chap27Lib.IsLoaded", "frmCustomers") Then

      MsgBox "Customers Form is Loaded"


      MsgBox "Customers Form is NOT Loaded!!"

   End If

End Sub

This code uses the Run method of the Application object to call a function called IsLoaded, which can be found in the library called CHAP27LIB.MDA. This file must either be referenced with an Explicit reference (see "Creating an Explicit Reference" later in this chapter) or be located in the directory specified in the AddInPath key of the Windows Registry. Notice the Explicit reference to the name of the library in which the function can be found. When using this method of loading a library (without an Explicit reference), the library name must be explicitly specified.

The LoadOnStartup Key

You can add a key to the Windows Registry called the LoadOnStartup key. This key is not automatically created for you. To create the LoadOnStartup key and add an entry to it, follow these steps:

  1. Select Run from the Windows Start menu.

  2. Type RegEdit and Click OK. This launchs the Registry Editor.

  3. Open the Registry tree until you can see HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Wizards.

  4. Click on the Wizards entry.

  5. Select Edit|New|Key. A new key is added.

  6. Type LoadOnStartup as the name of the new key.

  7. With LoadOnStartup selected, select Edit|New|String Value.

  8. Type the full name and path of the library.

  9. Select Edit|Modify.

  10. Type rw for the value.

An example of a completed entry that references the library in the C:\Libraries directory called CHAP27LIB.MDA appears in Figure 27.3.

Figure 27.3. Adding the LoadOnStartup key to the Windows Registry.

The module and procedure lists of any library databases listed under the LoadOnStartup key are loaded when Access is started. It works like this: When you use the Run method, discussed in the "Creating a Library Reference" section of this chapter, Access searches for the specified procedure in the loaded or referenced libraries. If the procedure is not found, Access searches any databases listed in the LoadOnStartUp key. Because the module and procedure lists of databases listed under the key become available when Access is started, Access searches the list and then locates and loads the required library.

In summary, using the LoadOnStartUp key, you can reap the benefits of Application.Run by loading the type information from the library when Access is loaded but not loading the actual module until it is explicitly referenced through code.

Using the LoadOnStartUp key is not a panacea. The process of loading the type library when Access is loaded does slow down the initial load time for your application. Furthermore, the memory occupied by the type information is used regardless of whether the library functions are actually accessed. You must decide if either of these facts are issues.

Creating an Explicit Reference

The most common type of reference is an Explicit reference. This type of reference is created from within any Code module within the database that is referencing the library. The steps are as follows:

  1. Go into Design view of any module within the application database.

  2. Select Tools|References. The References dialog appears (see Figure 27.4).

Figure 27.4. The References dialog.

  1. Click on Browse.

  2. Locate the library database that you want to reference.

  3. Click OK to close the References dialog.

When you add a library database to the References dialog, Access loads the database as a library when you make a call to the library from your code. You can call a library routine just as you would call any subroutine or function. You can then use code in the library database to open forms and other objects stored in the library. Access does not actually load the library database into memory until code in the active application database calls a function or subroutine that is located in the library. Explicit library references impose a few limitations:

When a function is called that is within a library that cannot be located, the dialog shown in Figure 27.5 appears. This dialog indicates that Access cannot find the project or library containing the procedure. The References dialog shows the library as missing (see Figure 27.6).

Figure 27.5. This warning message indicates that the library database cannot be located.

Figure 27.6. The References dialog with a library flagged as missing.

Although Access might not be able to find a library database that has been moved, it does its best to resolve library references. By default Access looks in the following places to attempt to resolve a library reference:

Although most of these locations are self-explanatory, a couple of them require further explanation. If the library is not located in the exact same location on the user's machine as it is on your machine, Access searches the relative path to the library next. This means that if the library is placed in the same directory as the database that is referencing it, or in the same relative location, the library database is successfully found. For example, if your application database is located in c:\AccessApps\Sales and the library database is located in c:\AccessApps\Sales\Libraries, and the user installs the application in c:\SalesApp with the library installed in c:\SalesApp\Libraries, Access is able to resolve the reference to the library.

Another trick in dealing with library databases is to utilize the RefLibPaths key of the Windows Registry. If a key called RefLibPaths exists in the Windows Registry, Access also searches the paths specified under RefLibPaths in an attempt to resolve any references. To use this trick, follow these steps:

  1. Create a RefLibPaths key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0, if it does not already exist.

  2. With the key selected, select Edit|New|String Value.

  3. Type the name of the library database.

  4. Select Edit|Modify.

  5. Type the name of the path containing the library.

  6. Repeat steps 2 through 5 for each library you are referencing.

This is a good method to use if you will be distributing an application containing several library databases. You can select a location for the library databases and then reference that location in the Windows Registry. You can even create the Registry entries programmatically using Windows API calls or using the VBA SaveSetting statement. Figure 27.7 shows the RefLibPaths key with an entry for the CHAP27LIB.MDA library.

Figure 27.7. The RefLibPaths key of the Windows Registry.

You can use the Setup Wizard to create the RefLibPaths key in the Windows Registry. This is the easiest way to create the RefLibPaths entry, but it requires that you distribute your application using the Setup Wizard.

Debugging a Library Database

You can open a library database and test it just like any other database. Although you should always begin testing the library functions this way, it is also important that you give the database a test drive as a library. In other words, after you have eliminated any bugs from the database, you should reference it from another database and test it as a library database.

If you need to make changes to a library database while accessing it from another database, you can easily do so by following these steps:

  1. Make sure the library database is referenced from Tools|References.

  2. Click the Object Browser tool from the Module Design window.

  3. Use the Libraries/Databases drop-down to select the library database whose code you want to modify (see Figure 27.8).

Figure 27.8. Using the Object Browser to modify a library database.

  1. Select the module you want to modify from the Modules/Classes list box.

  2. Select the routine you want to modify from the Methods/Properties list box.

  3. Click Show. You are placed in the correct module and procedure of the library database. You can now make changes to the code within the database as required.

Securing an Access Library

Many people develop Access libraries for mass distribution in the retail market. Whether you are marketing a library database or just distributing it in your organization or to your clients, you should consider securing your library code. This protects the library code from being modified or copied by unauthorized individuals. Security issues are covered in detail in Chapter 30, "Database Security Made Easy."

Practical Examples: Building a Library for Our Application

Now that you are familiar with library databases and what they have to offer, let's extract all the generic functions from the time and billing application and place them in a library database. The following is a step-by-step roadmap to accomplishing this task:

This process has already been completed for CHAP27.MDB. The associated library database is called CHAP27LIBRARY.MDA. If you want to complete this process as an exercise, copy CHAP26.MDB and complete the steps outlined.

  1. Create a new database that will become the library database. Import the basUtils, basGenericErrorHandler, and basWinAPI modules as well as the frmError form into the library database.

  2. Remove three routines from basUtils within the library database: RunReport, GetCompanyInfo, and CreateInstances. Assume that these routines are specific to the application database and should not be moved to become a part of the library.

  3. Select Run|Compile All Modules to ensure that you do not get any compile errors in the library database.

  4. Open the application database.

  5. Remove basGenericErrorHandler, basWinAPI, and frmError from the application database.

  6. Remove six subroutines from basUtils in the application database: IsLoaded, FlipEnabled, ImportantKey, AreTablesAttached, LastOccurence, and TryAgain.

  7. Use Tools|References to reference the library database.

  8. Select Run|Compile All Modules to ensure that you do not get any compile errors in the application database.

  9. Test the application to ensure that it runs successfully. To properly check all aspects of the application, you need to introduce an error to test the error-handling routines. Rename the CHAP19DATA.MDB database to test the linking routines.

One more database element should be moved to the library database— the Report Selection Criteria form shown in Figure 27.9. This form is generic and can be used by many of the applications you create. Follow four steps to move the frmReportDateRange form to the library database:

  1. Open the library database and import the frmReportDateRange form.

Figure 27.9. The Report Selection Criteria form.

  1. Create a module called basGenericForms and add the subroutine called OpenReportDateRange to the module. The OpenReportDateRange procedure is given ahead. Because you cannot open a form in a library database directly, you must create a routine within the library database that opens the form.

  2. Open the application database and remove the frmReportDateRange form.

  3. Modify the appropriate objects within the application database like this:

    Sub OpenReportDateRange(strOpenArg As String)
    DoCmd.OpenForm "frmReportDateRange", , , , , acDialog, _
    End Sub

Three reports within the application database need to be modified to accommodate the movement of the frmReportDateRange form to a library database: rptProjectBillingsByWorkCode, rptClientBillingsByProject, and rptEmployeeBillingsByProject. The Open event of rptProjectBillingsByWorkCode needs to be modified to look like this:

Private Sub Report_Open(Cancel As Integer)

    Call OpenReportDateRange("rptProjectBillingsByWorkCode")

    If Not IsLoaded("frmReportDateRange") Then

        Cancel = True

    End If

End Sub

Rather than opening the form directly, which would not work because the form is in a library database, the OpenReportDateRange library routine must be called. The OpenReportDateRange library routine is responsible for opening the form. The strOpenArg parameter to the OpenReportDateRange subroutine is used as the OpenArgs parameter for the frmReportCriteria form. Similar changes need to be made to the rptClientBillingsByProject and rptEmployeeBillingsByProject reports. The Open event of the rptClientBillingsByProject report should be modified to look like this:

Private Sub Report_Open(Cancel As Integer)

    Call OpenReportDateRange("rptClientBillingsByProject")

    If Not IsLoaded("frmReportDateRange") Then

        Cancel = True


        Select Case Forms!frmReportDateRange!optDetailLevel.Value

            Case 1

               Me.Caption = Me.Caption & " - Summary Only"

               Me!lblTitle.Caption = Me.lblTitle.Caption & " - Summary Only"

               Me.Detail.Visible = False

            Case 2

               Me.Caption = Me.Caption & " - Detail Only"

               Me!lblTitle.Caption = Me.lblTitle.Caption & " - Detail Only"

               Me.GroupHeader0.Visible = False

               Me.GroupFooter1.Visible = False

               Me!CompanyNameDet.Visible = True

            Case 3

               Me.Caption = Me.Caption & " - Summary and Detail"

               Me!lblTitle.Caption = Me.lblTitle.Caption & " - Summary and Detail"

               Me!CompanyNameDet.Visible = False

        End Select

    End If

End Sub

The Open event of the rptEmployeeBillingsByProject report should be modified to look like this:

Private Sub Report_Open(Cancel As Integer)

    Call OpenReportDateRange("rptEmployeeBillingsByProject")

    If Not IsLoaded("frmReportDateRange") Then

        Cancel = True

    End If

End Sub

After you have successfully moved the generic features of the application to the library database, you can try building another application database and utilize the same library features.


Library databases enable you to create libraries of code, forms, reports, and other objects that will be shared by multiple databases. They facilitate the application development process by allowing you to easily centralize the development of common code libraries. They can also be used to incorporate add-ins, wizards, and builders into your applications and development environment (covered in Chapter 28).

This chapter began by explaining what a library database is. It continued by walking you through all of the steps required to prepare a database to become a library database. There are several methods that you can use to reference a library database. The chapter discussed each method, highlighting the pros and cons of each. When you have properly referenced a library database, the debugging process begins. This chapter highlighted how easy it is to debug an Access 95 library database. Finally, it provided you with practical examples of how you can employ library databases in your applications.

Library databases can greatly facilitate the application development process, allowing you to easily implement sophisticated functionality into all of your applications. Although the process of designing library databases can be intimidating at first, a well-planned library database can shave hours of time off of the application development and maintenance processes.

Previous Page TOC Index Next Page Home