Library Databases - The What and Why

Do a Google search for "MS Access Library Database," and what you're going to see is a lot of suggestions for writing databases that manage libraries, one of the initial databases designed by beginning Access developers. This article is not about coding a library management database, it is about MS Access's intrinsic ability to use another MS Access database as a code library.

In the VBE, go to Tools -> References, click Browse, then hit the drop-down under "Files of type:" Along with .dll's and ActiveX controls, you'll discover that you can link any of several MS Access file types as a Reference. Now, normally I'm against references, but in this case I make an exception.

Say you have 10 interconnected MS Access front-ends running as part of a modular system. You probably want them to have a similar look and feel, with the same basic functionality. This means that a lot of the code procedures used in each database are going to be the same. Now, you could import and export modules across each database, but this has the potential to introduce serious version issues. Instead, create a code library database.

The library database is a convenient place to put all of the code that you will share across the databases you build - whether it be a password-masking procedure, the hash code you use for encrypting the passwords, or something as simple as a custom function for putting a Select Case statement onto a single line (like an If-Then to an IIf). Whenever you have code that you will want to reuse, put it into the library database. Then, instead of hunting multiple db modules for the right function to import, simply attach the library database as a Reference and - voila! - instant access to all of your custom classes and procedures.

A word of caution, however - library databases are very sensitive to environment changes. If you rename a library database or move it to a different folder, you will break each reference. If your database is accessed by multiple users, follow best practice and have them run local copies, with the library database in the same folder on the users' systems as the front ends, in order to mitigate that issue.

And a further note - even though some of Microsoft's documentation appears to hint that you can open objects, such as forms and reports, in a library database using code in the library database, the fact is that you can't. The application environment simply doesn't allow for it. If you do want to create a forms or reports library that is accessible from all of your other databases, you can handle it by using an Access automation object.