B.3. Building Code Libraries

Aside from the four types of reference libraries listed above (OLB, TLB, DLL, and OCX), there is a set of "library" types that are often overlooked. These are library types you can use to link to Access projects you have created. They include:

• MS Access Databases: MDB and MDE

  Figure B.3. Figure B-3

  

• MS Access Add-ins: MDA

• MS Access Projects: ADP and ADE

With these types of references you can develop your own Code Libraries that contain routines to share in all of your applications. An example might be something like a common Error Handling routine.

Since you can reuse these routines over and over, you can justify putting a little more effort into them. Take error handling for example. Commonly, we develop code to display a message to the user and request that the user report the error to us. Have you ever been watching a user use your application and expose an error you hadn't found in testing? They click OK on the message without giving it a second thought. You ask why they didn't wait to review the message and they say, "Oh, that happens all the time. I was told to just ignore it."

Suppose that instead of just depending on the users to call to report errors, you write routines to track the errors in a table. Then you could investigate what is happening. Perhaps your tables could even maintain some trace data to help find out what causes the problem. Suppose you also realize that the main reason the errors don't get reported is that it is too difficult for the user to report them. So you add some functionality that builds an e-mail message for the user to send through Outlook.

Figure B.4. Figure B-4

Of course there are always so many things to do when building the current application, we don't have time for tasks like these. But if you could find time to write these routines on time and reuse them in all of your applications with all your customers, would that make it worthwhile? That's what Code Libraries are for.

Note: If you have a particular piece of code that you do not want people to be able to read easily you can make an MDE from it. This removes all the editable code and compresses the database. See the "Chapter 16, Database Security," for more information.

Office XP Developer edition included a component call Code Librarian to help manage these code libraries. However, Office 2003 does not include that component. For those who have not previously enjoyed using Code Librarian from Office XP, the bad news is that the Code Librarian can only be installed if you install the full Office XP package. So unless you want to buy Office XP, you won't be able to try it. For those who have Code Librarian installed on their machines, the good news is that it is still compatible with Office 2003. So don't remove it.

B.3.1. Visual Basic and References

There are a few techniques that make working with Access References easier. And there are a few techniques that you should be aware of to avoid problems when using references to your own code libraries. These techniques are listed in this section.

B.3.1.1. The Reference Object

The Access Application object includes the References collection. The References collection can be easily used for a number of purposes. You can determine the number of references in the project using

Application.References.Count

You can walk through the references to be sure that all the references are found using something like this:

For Each ref in Application.References
      If ref.IsBroken then
            Debug.Print ref.name & " is broken."
      End if
Next Ref

You can add references using the AddFromFile or AddFromGUID. For AddFromFile, you simply specify the file specification. For AddFromGUID, the reference library must be registered in the Windows System Registry and you must know the exact GUID for that library.

B.3.1.2. Using CurrentDB versus CodeDB

One thing to consider when developing Code Libraries is that CurrentDB refers to the database that is open in the Access user interface. So, if you want your Code Library project to refer to objects that are in its own database, you need to use CodeDB. Likewise, there are the CurrentProject and CodeProject properties so be sure that you are setting a reference to the correct objects.

B.3.1.3. Running a Procedure from a Library Database

If you need to call routines in your Code Library from the database referencing the code database, you can call the routine using the Call statement in Visual Basic. You can also use the Application.Run method. For example say you have an Errors.MDB that contains the HandleError procedure. You may call that procedure with code that looks like this (depending on the parameters needed in the routine, of course):

Call Errors.HandleError _
          (Err. .Number, Err.Description, Err.Source)
Application.Run("Errors.HandleError", _
    Err.Number, Err.Description, Err.Source)

Note: The qualifier does not have to be the same as the name of the MDB. In the above example, Errors. was used to qualify where the HandleError procedure is located. You can change the name of the qualifier by changing the Name property of the project.

B.3.1.4. Compiling to Validate References

An easy way to be sure that the References in your project are not broken is to use the Debug | Compile menu option. This quickly finds declarations that use classes that are not available to your project.

Be aware that since Access uses late binding, types are not checked until the code is executed. That means that until a procedure is run, you may not know that a variable has been defined using a class from a missing library. You can avoid having the users find these problems later by checking the IsBroken property of the References in your application during start up. This would be a good routine to write and put into your code library so that you can use it with all of your applications.

B.3.2. Fixing Broken References

If your code suddenly stops working after you have installed your database on another computer, it's a good idea to check the References. One of the first things to check for is the MISSING referenced type library. As you can see in Figure B-5, MISSING stands out at the left of any missing type library.

To fix the missing references, open the References dialog box from the Visual Basic Editor and update the References.

Note: There are two problems you typically run into here. One, if you have delivered the database as an MDE, you cannot modify references. Two, if the library you are referencing doesn't exist on the computer. In either case, the most likely solution is to get the library into the right place on the computer. For that, see section Avoiding Broken References.

When you have fixed missing references, it's a good idea to compile the module using Debug | Compile, that is if you're not working with an MDE. Compiling will help make sure that the library on the new computer matches the one on the computer where you did your testing. Meaning, it will indicate if the library has the same classes and type definitions you used in your code.

If you have a broken or missing reference, compiling may report an error incorrectly. In particular, you may find that Visual Basic functions (for example, Right or Ucase) are reported as undefined. If this occurs, fix the missing references first, then proceed with other fixes.

B.3.3. Avoiding Broken References

Can you claim that you've never had a problem delivering an application to one of your users? Then we're betting that you've never used references. Either that or you are one of the fortunate developers who get to develop on a machine with a configuration that is identical to your users. But even if the machine is identical, you probably haven't taken the opportunity to develop a Code Library. You know what we mean—the one you forgot to take with you when you went to the user's machine to install your database.

Figure B.5. Figure B-5

Of course the first thing to do to avoid broken references is to be sure that you are delivering all of the components that go with your application. And don't forget those DLLs that you acquired from a vendor to improve the features in your application. And just delivering them isn't all that there is to it, you need to be sure you've installed those DLLs in the right folder.

So how do you know what the right folder is? When Access searches for referenced libraries, it first searches based on the file specification provided when the library was added. If the library is not found, it then searches as follows:

If Access still can't find the reference after performing this search, you must fix the reference manually.