Chapter 24. Creating Your Own Libraries

<feature><title>In This Chapter</title>
</feature>

Why This Chapter Is Important

As your knowledge of the VBA language expands and you become more proficient as a VBA programmer, you probably will 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 in 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 can be used 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. Instead of opening a library database to use it, you reference it from another database.

Access is highly dependent on library databases. The Table Wizard, Form Wizard, Report Wizard, Database Wizard, Database Splitter, Database Analyzer, and Database Documenter are all examples of tools that reside in library databases. In fact, all the wizards, builders, and menu add-ins you are accustomed to using while developing your applications are all contained within library databases. These tools are covered in Chapter 25, “Using Builders, Wizards, and Menu Add-Ins.” This chapter focuses on creating library databases and placing generic functions in a library database to make them available to all your application databases.

Preparing a Database to Be a Library

Creating a library database involves two steps:

  1. Writing the functions and creating the objects to be included in the library

  2. Loading the database as a library

You begin by creating the generic objects you want to share among your applications. To load the database as a library, you must reference it from another database. This process is covered in the next section.

Before you can reference a database as a library, you need to think about how to 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, you must structure your library databases effectively in order to achieve optimal performance.

Access 2.0 loaded all code modules the moment the application was loaded. This meant that, when developing an Access 2.0 application, it was not particularly important how you structured your subroutines and functions within the various modules of the database. This situation changed dramatically with Access 95 and all versions that followed it, all of which load code modules only if they are needed. In Access 95 and later, 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 is loaded. Therefore, it is crucial that you structure your modules to minimize what is loaded into memory, using these techniques:

  • Separate frequently used procedures from those that are called infrequently.

  • Place in the same module procedures that are used together.

  • Place in their own modules procedures that are called rarely.

  • If the same procedure is called by routines in more than one module, consider duplicating the routine and placing a copy of it in each module. This method prevents an entire module from loading just because a single routine within it is called.

  • Place in the same module procedures within a call tree. This is necessary because Access looks at the potential call tree when it loads a module. If a procedure in one module is called from a procedure in another module, both modules are loaded into memory.

Although you generally want to load into memory as little as possible, the opposite is true for commonly used functions. By placing frequently used procedures in the same module, you ensure that they are loaded into 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. Good examples are the CurrentProject object and the CurrentDB function. As you have seen throughout this book, the CurrentProject object is an object that refers to the current ADP project or Access database (mdb). The CurrentDB function is a commonly used function that enables you to reference the current database. You would think that both the CurrentProject object and the CurrentDB function reference the database in which the code is running, but this is actually not the case. They specifically reference the database that is active in the user interface. If a library function refers to either CurrentProject or 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 CodeProject object or the CodeDB function. The CodeProject object and the CodeDB function always refer to the database in which the code is running. You must decide whether CurrentProject, CurrentDB, CodeProject, or CodeDB is applicable for each situation.

Compiling the Library

Compiling a library database before you distribute it ensures optimal performance. If library code is not compiled, it will compile 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 17, “Optimizing Your Application.” After you complete all changes to the library database, select Debug|Compile. You must choose this command each time you make changes to the library database.

Creating a Reference

A reference is Access’s way of locating a library database so that it can use the code in it. You can establish references in four ways:

  • Create a library reference

  • Create a runtime reference

  • Create an explicit reference

  • Use VBA code

Tip

Much of the text that follows refers to the Windows registry. You can access the Windows registry using the RegEdit utility. To utilize RegEdit, select the Run option from the Start menu, and then type RegEdit.

Creating a Library Reference

You create a library reference by adding the library to the Menu Add-ins section of the Windows registry, as shown in Figure 24.1. The Menu Add-ins section is located in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice10.0AccessMenu Add-Ins key. This type of reference is limited because it allows the functions of the library database to be invoked only as an add-in. Add-ins are covered in more detail in Chapter 25.

Viewing the Menu Add-ins section of the Windows registry.

Figure 24.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, this technique does not require that additional modules in the call stack be loaded into memory unless they are called explicitly. Creating a runtime reference does have a few disadvantages, though:

  • The library database must have an MDA extension.

  • The library database must be located in the path specified in the AddInPath key in the Windows registry. The AddInPath key is located in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice10.0AccessWizards subdirectory of the Windows registry, as shown in Figure 24.2.

    Viewing the AddInPath key of the Windows registry.

    Figure 24.2. Viewing the AddInPath key of the Windows registry.

Calling a Function from a Library at Runtime

Calling a Function from a Library at Runtime

The code in Listing 24.1 illustrates how to call a function in a library. Notice that the IsLoaded function is being called from the library. This code is located in the CHAP24EX.MDB database on the sample code CD-ROM.

Example 24.1. Calling a Function in a Library

Sub AppRun()
   If Application.Run("Chap24Lib.IsLoaded", "frmCustomers") Then
      MsgBox "Customers Form is Loaded"
   Else
      MsgBox "Customers Form is NOT Loaded!!"
   End If
End Sub

Listing 24.1 uses the Run method of the Application object to call a function called IsLoaded, which is located in the CHAP24LIB.MDA library. This file must be referenced with an explicit reference or be located in the directory specified in the AddInPath key of the Windows registry. Notice the explicit reference to the library name in which the function is located. When using this method of loading a library (without an explicit reference), the library name must be specified.

Using the LoadOnStartup Key

You can add a LoadOnStartup key to the Windows registry. This key provides a means for Access to load a type library when the database is loaded. A type library is not an actual module, but more of a blueprint of what the module looks like. It displays the functions and constants for a specific module. This is helpful because Access can look up functions without having to actually load the module in which the function is located. This key is not automatically created for you. To create the LoadOnStartup key and add an entry to it, follow these steps:

  1. Choose Run from the Windows Start menu.

  2. Type RegEdit and click OK; this launches the registry Editor.

  3. Open the Registry tree until you see HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice10.0AccessWizards.

  4. Click the Wizards entry.

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

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

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

  8. Type the full name and path of the library as the name of the new string value.

  9. Choose Edit|Modify.

  10. Type rw for the value.

Figure 24.3 shows an example of a completed entry that references the library in the c:Libraries directory: CHAP24LIB.MDA.

Referencing a library using the Registry Editor.

Figure 24.3. Referencing a library using the Registry Editor.

The module and procedure lists of library databases listed under the LoadOnStartup key are loaded when Access is started. When you use the Run method (discussed in the “Creating a Library Reference” section), Access searches for the specified procedure in the loaded or referenced libraries. If it does not find the procedure, Access searches any databases listed in the LoadOnStartUp key and then locates and loads the required library.

As you can see, the LoadOnStartUp key can reap the benefits of Application.Run by using the type library. The functions can be checked without loading the actual module until it is referenced explicitly through code.

Note

The LoadOnStartUp key is not a panacea. 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 actually are accessed. You must decide whether either of these facts is an issue.

Creating an Explicit Reference

The most common type of reference by far is an explicit reference. This type of reference is created from any code module in the database referencing the library. To create an explicit reference, follow these steps:

  1. Choose a module from the database container window and click the Design button to open the Design view of the module.

  2. Choose Tools|References from the VBE menu. The References dialog box appears, as shown in Figure 24.4.

    The References dialog box.

    Figure 24.4. The References dialog box.

  3. Click the Browse button.

  4. Select Microsoft Access Databases (*.mdb) or Add-ins (*.mda) from the Files of Type drop-down.

  5. Locate the library database you want to reference.

  6. Click Open to close the Add References dialog box.

  7. Click OK to close the References dialog box.

When you add a library database to the References dialog box, 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 then can 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:

  • The references you add in a database are available only to that database. Therefore, you must add the library reference to each application database that needs to use the library.

  • The explicit path to the reference is stored in the database. This means that, if the library is moved, the reference cannot be resolved. Exceptions to this rule are covered later in this section.

When a function is called that is in a library that Access cannot locate, the message shown in Figure 24.5 appears. The References dialog box shows the library is missing, as shown in the sixth line of the Available References list box in Figure 24.6.

A warning message indicating that the library database cannot be located.

Figure 24.5. A warning message indicating that the library database cannot be located.

The References dialog box with a library flagged as missing.

Figure 24.6. The References dialog box 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 these places to attempt to resolve a library reference:

  • The absolute path of the library

  • The relative path to the library

  • The current folder

  • The directory where Access is installed

  • The Windows path (Windows and WindowsSystem folders)

  • The PATH environment variable

  • The path located in the RefLibPaths key of the Windows registry

A couple of these locations require further explanation. If the library is not located in exactly the same location on the user’s machine as it is on your machine, the relative path to the library is searched 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 found. Suppose your application database is located in c:AccessAppsSales. The library database is located in c:AccessAppsSalesLibraries. The user installs the application in c:SalesApp with the library installed in c:SalesAppLibraries. In this case, Access can resolve the reference to the library.

Another trick when dealing with library databases is to use 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 the HKEY_LOCAL_MACHINESOFTWAREMicrosoftOffice10.0Access subdirectory, if it does not already exist.

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

  3. Type the name of the library database as the name of the new string value.

  4. Choose Edit|Modify.

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

  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 even can create the registry entries programmatically by using Windows API calls or the VBA SaveSetting statement. Figure 24.7 shows the RefLibPaths key with an entry for the Chap24Lib.mdb library.

The RefLibPaths key of the Windows registry.

Figure 24.7. The RefLibPaths key of the Windows registry.

Tip

You can use the Packaging 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 Packaging Wizard.

Creating a Reference Using VBA Code

With Access 2000 came the capability to create library references using VBA code. You use the AddFromFile method to accomplish this task. The AddFromFile method is applied to the References collection, which is similar to other collections used within Access, and provides a hook to the references associated with a database. The AddFromFile method of the References collection accepts a string as a parameter. The string contains the name of the library reference you are adding. Listing 24.2 shows the code to pass in a library name and then add a reference to it.

Example 24.2. Locating and Referencing Libraries in Code

Function CreateLibRef(strLibName as String)
    Dim ref As Reference
    On Error GoTo CreateLibRef_Err
    'Create new reference
    Set ref = References.AddFromFile(strLibName)
    CreateLibRef = True
Exit_CreateLibRef:
    Exit Function
CreateLibRef_Err:
    Dim intAnswer As Integer
    Dim strLocation As String
    intAnswer = MsgBox("Library Not Found, Attempt to Locate?", _
        vbYesNo, "Error")
    If intAnswer = vbYes Then
       strLocation = InputBox("Please Enter the Location of the Library")
       Resume
    Else
        CreateLibRef = False
        GoTo Exit_CreateLibRef
    End If

End Function

The routine begins by invoking an error handler. A reference object is then set to the result of the AddFromFile method being executed on the References collection. If the AddFromFile method executes successfully, the reference is created, and the function returns a True condition. Otherwise, the user is asked whether he wants to locate the library database. If he responds affirmatively, he is prompted for the location of the library database and the code attempts once again to establish the reference. If he opts not to supply a location, the routine terminates, returning a False condition.

Debugging a Library Database

You can open a library database and test it just like any other database. Although you always should begin testing the library functions this way, it also is important that you give the database a test drive as a library. In other words, after you eliminate 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 do so easily by following these steps:

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

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

  3. From the Project/Library drop-down menu, select the library database that contains the code you want to modify. (See Figure 24.8.)

    Using the Object Browser to modify a library database.

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

  4. Select the class you want to modify from the Classes list box.

  5. Select the member you want to modify from the Members list box.

  6. Click View Definition (the button with the arrow pointing toward the box), or double-click the member whose code you want to view. You are placed in the correct module and procedure of the library database. You now can make changes to the code in 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 within your organization or to your clients, I highly advise you to consider securing your library code. This protects the library code from being modified or copied by unauthorized individuals. It also avoids the headache of some user who tries to alter the code and then calls you for support. Security issues are covered in detail in Chapter 27, “Database Security Made Easy.”

Building a Library for Your Application

Now that you are familiar with library databases and what they offer, try extracting all the generic functions from an application and placing them in a library database. This section presents a step-by-step roadmap for accomplishing this task.

Note

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

To extract the generic functions from the Time and Billing application and place them in a library database, follow these steps:

  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 two routines from basUtils within the library database: RunReport and GetCompanyInfo. Assume that these routines are specific to the application database and should not be moved to become a part of the library.

  3. Choose Debug|Compile 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. Choose Tools|References to reference the library database.

  8. Choose Debug|Compile 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 CHAP24DATA.MDB database to test the linking routines.

You should move one more database element to the library database: the Report Selection Criteria form. This form is generic and can be used by many of the applications you create.

Follow these steps to move the frmReportDateRange form to the library database:

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

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

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

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

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

You must modify three reports in the application database to accommodate the movement of the frmReportDateRange form to a library database: rptProjectBillingsByWorkCode, rptClientBillingsByProject, and rptEmployeeBillingsByProject. The Open event of rptProjectBillingsByWorkCode should 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

Instead of opening the form directly, which would not work because the form is in a library database, you must call the OpenReportDateRange library routine to open the form. The strOpenArg parameter to the OpenReportDateRange subroutine is used as the OpenArgs parameter for the frmReportCriteria form. You must make similar changes to the rptClientBillingsByProject and rptEmployeeBillingsByProject reports. You should modify the Open event of the rptClientBillingsByProject report to look like Listing 24.3.

Example 24.3. Modifying the Open Event of the rptClientBillingsByProject Report

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

Modify the Open event of the rptEmployeeBillingsByProject report 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 move the generic features of the application to the library database, you can try to build another application database and use the same library features.

Summary

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

This chapter began by defining a library database. It then walked you through all the steps required to prepare a database to become a library database. The chapter discussed the several methods to reference a library database, highlighting the pros and cons of each.

After you reference a library database, the debugging process begins. This chapter highlighted how easy it is to debug an Access 2002 library database. Finally, it provided you with practical examples of how you can use library databases in your applications.

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

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.143.248.211