ACC: How to Create, Debug, and Use a Microsoft Access Library

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes a Microsoft Access library and discusses how to create and debug a library as well as some things to watch out for.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual in version 2.0.

MORE INFORMATION

Access Basic Library Definition

When you write a Microsoft Access application, such as the sample database NWIND.MDB, the application works only within the database in which it was created. This is satisfactory for many applications that specifically use the data that resides in the application's database.

However, many Microsoft Access developers write generic applications, programs, and utilities that are designed to work on any user database. An example of this is wizards. Wizards are Access Basic programs that reside in their own database but are available in any open database. If this were not the case, you could not use a wizard outside of the database that the wizard program and system objects reside in. In order to make a program's code and objects available to any user database, you must load the database containing the program and its objects as a library.

To load a database as a library, you must open the .INI file (MSACCESS.INI in Microsoft Access version 1.x, MSACC20.INI in version 2.0) and add a line to the Libraries section. When you open the .INI file initially, you will probably see a Libraries section with an entry for the Wizards library:

   [Libraries]
   wizard.mda=ro

The "ro" in the wizard entry means that the library is read-only. If you have an application that uses its own tables that are to be written to at any point in your program, you would specify "rw" rather than "ro." For example, to add an application called STOCKAPP.MDB that uses its own tables that can be modified, add the following line to the .INI file:

   stockapp.mdb=rw

   [Libraries]
   wizard.mda=ro
   stockapp.mdb=rw

Note that when a database is loaded as a library, it cannot be opened as a user database.

Writing and Debugging Access Basic Library Code

When you write an Access Basic application for use as a library, you are doing little more than writing the application in a user database with the intention of using it as a library at a later point. Because of this, a rule of thumb is to make sure the application works completely before you try to use it as a library.

Although this rule of thumb is enough to successfully create many types of library applications, there are some important pitfalls to watch for when writing a library application, even if the application works perfectly as a user database.

Debugging an Error in a Library Database

Microsoft Access version 1.x:

If the library database generates an error that only occurs while it is a library, it can be very difficult to locate. An error might occur that gives you some idea of the general area of the problem, but there may be little or no indication of the offending line. Because you cannot set and use breakpoints and stepping in library applications, you should design error traps that convey meaningful messages and indicate the location of the problem.

Another debugging tip is to place message boxes at milestone areas of the code so that you always have an idea of which code is being run.

Microsoft Access version 2.0:

Add the following line to the [Options] section of the MSACC20.INI file. If the [Options] section does not exist, add it to the bottom of the file.

   DebugLibraries=True

   [Options]
   DebugLibraries=True

CodeDB() Versus CurrentDB()

Access Basic includes the CodeDB() function for opening library databases. The CodeDB() function works identically to the CurrentDB() function if you are running the application as a user database. However, if you are running the application as a library, CodeDB() returns the database object for the library database from which it was called, while CurrentDB() returns the database object from the current open user database. Because of this, it is easy to confuse one for the other.

Domain Functions

Domain functions should be used with caution in library modules because they can only process user tables and not library tables.

Macros in a Library

Microsoft Access version 1.x:

Of all the objects you can create in a Microsoft Access database, macros are the only type of object you cannot use in a library application. The most obvious problem this presents is that forms require the use of macros in order to have menus. Because of this limitation, you must make sure to use only Access Basic code for programming.

Microsoft Access version 2.0:

In Microsoft Access 2.0, you can call a macro from a Library database. However, because you cannot use error trapping in macros, macros should be avoided except for implementing custom menus.

Keys May Be Redefined in User Database

Because the user database may contain an AutoKeys macro to redefine the meaning of keys, you should use caution when using SendKeys in your Library application. Use other macro actions where appropriate and use hotkeys and the TAB key rather than the ARROW keys when manipulating a dialog box.

USysAddIns Table