USING ACCESS CODE LIBRARIES

Access, by using references and VBA, enables developers to create their own libraries of VBA modules. As with add-ins, each library can then be used in multiple or distributed applications. There are a number of pros and cons to using code libraries.

Looking at the Pros and Cons of Code Libraries

Here are some of the pros of using code libraries:

• Code libraries enable you to create routines once and then use them in multiple applications. This reduces redundant code.

• When changes are made to the code for fixes or additions, they have to be made in only one place.

• If you want to distribute your library to clients without letting them have the source, you can make an MDE from the library, but still give them the source (which they pay for) of the main application.

As convenient as using a library may seem, there are some disadvantages as well:

• You must be careful where library databases are placed in the system so that references can be maintained.

• Possible performance issues can arise.

• When changes are made to the library, you have to take precautions against messing up a call from an application that uses the library. Using the Optional keyword and setting defaults on the way into the routines can take care of this possibility.

• If using an MDE for the library, issues arise when using the library with some non-U.S. versions of Access.

• When writing routines to be used in a library, be sure to determine when the code is addressing the application objects instead of the objects located in the library.

Other issues to be aware of are discussed later in the section “Looking at Some Library Coding Issues.”

Considering Where to Put the Library Database

Some consideration should be given to where to place the library database. If you move or distribute the application after the library is referenced, you can break the reference.

To have Access “fix up” the reference to the library when starting the application, you can

• Place the library file in the application's, Access's, Windows, or WindowsSystem folders.
  
  
• Create an entry in the Registry with the key
  
  

  HKEY_LOCAL_MACHINESoftwareMicrosoftOffice9.0AccessRefLibPaths
  

  
  
• Create a reference yourself by using the AddFromFile method of the reference collection. Here is some code that demos this method:
  
  

  Function AddReferenceFromFile(strFileName As String) As Boolean
           Dim refCurr As Reference
  
           Set refCurr = References.AddFromFile(strFileName)
  
  End Function
  

  
  In this code, strFileName would be the full name and path of the .mda file. You want to delete the broken reference by using the Remove method from the References collection.
  
  

Setting a Reference to a Library

Setting a reference to a library is the same as setting a reference to any other type of library, such as the Word 9.0 Object Library.

To illustrate using a reference, I've created a small library with several functions in it. You can find this library, Chap17lib.mda, in the ExamplesChap17 folder on the CD-ROM.

To set a reference while in the VBE, follow these steps:

Viewing Library Routines in the Object Browser

To check out the routines in the library, choose Object Browser from the View menu and select Chap17lib from the Project/Libraries list. You then can see the library's modules and routines (see Figure 17.14).

Looking at Some Library Coding Issues

When working with a library, you need to be cognizant of how you're creating your code. Some objects can be used to make life easier as you're building the library.

Using CurrentProject to Reference the Application

Access 2000 lets you use CurrentProject so that you can specify the location of the objects you want to deal with. An example of this is the classic IsFormOpen function:

Function ap_FormIsOpen(strFormName As String) As Boolean

    ap_FormIsOpen = CurrentProject.AllForms(strFormName).IsLoaded

End Function

By specifying CurrentProject, you tell Access exactly which Forms collection you want to deal with. Another example of using the current project is to print out all module names in the application database to the Debug window.

Sub ListAppMods()

    Dim aoCurr As AccessObject

    For Each aoCurr In CurrentProject.AllModules
        Debug.Print aoCurr.Name
    Next

End Sub

To access objects in the library database, use CodeProject.

Using CodeProject to Reference the Application

To show how to use CodeProject, the ListAppMods routine has been modified by simply replacing CurrentProject with CodeProject and changing the name:

Sub ListLibMods()

    Dim aoCurr As AccessObject
    For Each aoCurr In CodeProject.AllModules
        Debug.Print aoCurr.Name
    Next

End Sub

Between these two objects, you can clearly identify which database—the application or library—you want to deal with.

Executing Application Routines from the Library

One other issue to consider is when you want to run a routine located in the application database from the library database. This could be the case if you have a routine that's run in all your applications, but you have some custom action that needs to be performed. To accomplish this, you could have a standard name for the custom routine and call the routine from the library:

Function TestApplicationSub()

    Application.Run "ApplicationSub"

End Function

You can also explicitly run routines from the library by using the Application.Run command.