May 5, 2011, 5:53 a.m.
posted by osi
Create a Code Library
Make your favorite custom functions available in all your databases.
As a developer, I find I often create and use the same functions on a number of different projects. Too many times I have had to hunt down some code I wanted to reuse. This has taken a good amount of time because not only do I have to recall where I used the code before, but I also need to remember which computer it is on!
The solution to this chaos is to put all the code in one database and use the database as a code library. A good way to do this is to clean up the code and assemble it in one or more code modules, in a single database. Make all the code routines be functions so that they return values. Then, just save the database and close it. Finally, change the extension of the database to.mda.
Figure shows a module filled with functions. This module is in the Access file that has the extension changed to .mda.
With a changed extension, now the file is recognized as an add-in, as shown in Figure.
From a regular Access database, the code library is referenced. In the VB editor, use Tools References to open the References dialog box. From the dialog, browse to the location of the saved code library. It might be necessary to change "Files of type" in the Add Reference dialog to Add-ins, as shown in Figure.
A module of code functions
Recognizing the file as an add-in
Now, the functions in the code library are available in the regular database. Figure shows how they are listed in the Object Browser.
Over time, as you create more and more routines, the value of keeping them in one place becomes a real timesaver. You never know when you will need to use an algorithm you created years ago.