Creating, Debugging, and Using an Access Library

Summary:

This article describes a Microsoft Access library and discusses some
basics on 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 Access applications with the programming tools provided with
Access.

More Information:

Access Basic Library Defined
----------------------------

When you write an Access application, such as the NWIND application
with the package, 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 Access developers will write generic applications,
programs, and utilities that are designed to work on any user
database. An example of this is Wizards. Access Wizards are Access
Basic programs that reside in their own database, but are available to
the user in any database the user has open. If this weren't the case,
you could not use a Wizard outside of the database that the Wizard
program and system objects reside in. In order to use a program (such
as a Wizard) so that its code and objects are 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 ACCESS.INI and add an
entry to the Libraries section. When you open ACCESS.INI initially,
you will probably see a Libraries section with an entry for the
Wizards library:

   [Libraries]
   wizard.mda=ro

Note: If there is no Libraries section, add it to the end of the file
and continue. The ACCESS.INI file can be found in your Windows
directory.

The "ro" in the Wizard entry means that the library is read-only. If
you have an application that uses system tables that are to be written
to at any point in your program, you would specify "rw" rather than
"ro". For example, suppose you have an application in a file called
STOCKAPP.MDB that employs the use of system tables that can be
modified. You would add the following entry to use the MDB file as a
library:

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

Given these library entries, the WIZARD.MDA library will be loaded
read-only, and the STOCKAPP.MDB library will be loaded as read- write.
You can now open an Immediate window in a user database and invoke sub
and function procedures from STOCKAPP.MDB, or open tables, queries,
forms, or reports with DoCmd commands. Even though you can access the
code and the database objects, you cannot see them in the Database
window.

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 trying 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
----------------------------------------

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 MsgBox's at milestone areas of the
code so that you always have an idea of which code is being executed.


CodeDB() Versus CurrentDB()
---------------------------

Access Basic includes the CodeDB() function for opening library
databases. CodeDB() works identically to CurrentDB() 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
CurrentBD() returns the database object from the current open user
database. Because of this, it is easy to confuse one for the other,
which results in logical errors that do indicate this is the problem.


Domain Functions
----------------

Domain functions include a parameter that is used as criteria for
applying the function to a specified set of records. The criteria is
in the form of a SQL WHERE statement and assumes first that any table
in the criteria is located in the user database. This could pose a
problem for your application if you intend to perform a domain
function on a library table that happens to have the same name as a
user table. Of course, this does not happen frequently; but it becomes
very important when you work with Microsoft Access system tables which
will have the same name in both databases. If you are to include a
table within your library, you should  give this table a unique name
that you do not expect a user to duplicate such as "My Library
Table1."

Macros in a Library Cannot Be Called from a User Database
---------------------------------------------------------

Of all the Access objects you can create in an  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.