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.
Creating a library database involves three steps:
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.
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.
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.
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.
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:
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 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" Else 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.
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:
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.
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:
Figure 27.4. 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:
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 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:
Figure 27.8. Using the Object Browser to modify a library database.
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."
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:
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:
Figure 27.9. The Report Selection Criteria form.
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 Else 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.