Building Your Library

Don't Repeat Yourself (DRY).  It's a core concept in programming.  It's an admonition against copying and pasting code.  It's also a call to create reusable code.

Within a project, this means creating functions and classes to encapsulate your logic.  When you start a second project, there will be functions from the first project you want to include in your new one.  The obvious thing to do is to copy those functions into the new project.

Then you get to your third project.  You find yourself copying many of those same functions from the first two projects into the third project.  Everything is going smoothly so far, but then you find a bug in one of the functions that exists in all three projects.

You open each project, search for the function with the bug, and copy and paste in the new code.  It's about this time that you realize you need a more structured approach to managing these functions that you keep reusing.  You do some research and decide that you have three options:

1. The status quo: copying and pasting functions among projects as needed
2. A code library: a set of text files where you maintain copies of common functions
3. An Access library: a common .mdb or .accdb file that you reference from your other projects.

The status quo: here there be dragons

Don't go this route.  Rather, don't go this route over the long term.  

You almost certainly will take this approach in the beginning.  That's fine.  That's normal.  One might even argue it's preferable, since you want to avoid "premature optimization."  However, this is not a good long-term strategy.  Copying and pasting bug fixes scattered among a few projects is annoying.  Trying to do manage that over dozens of projects is not feasible.

Code library: if it ain't broke don't fix it

This probably sounds fancier than what it is.  It is nothing more than a folder full of exported Access objects: standard modules, class modules, forms, reports, etc.  This folder would be kept under version control.

Access library: only one file to update

This is a common Access database file that you reference from other Access database files.  Daniel Pineault has a good article about how and why you might take this approach.

My Preference: Code Library

I will start by saying that options 2 and 3 are both good approaches to take. I agonized over this decision when I made it in 2007, early in my Access development career.  Daniel's article covers the advantages to option 3.  I'll cover the details and advantages of option 2, which is the approach I have taken.

Naming convention

One of the advantages to the code library approach is that debugging functions in the context of the custom application is easier.  That's because you can easily step in and out of the library functions when stepping through code.  The downside is that you can end up with a lot more Access objects in your custom application.  

Since you don't want to make program-specific changes to your library code modules, you need a way to set them apart from program-specific modules.  To do this, I use a two-letter prefix when naming my program-specific modules.  My library modules get no such prefix.  For example, DateFunctions and StringFunctions are two modules from my code library, while glFiltering and glPostBatch are standard modules from my "General Ledger" application.

Saving files

I use the "Export module..." command ([Ctrl] + [E]) in the VBA IDE to save copies of the standard and class module files to a folder.  For forms, reports, and macros, I use the undocumented DoCmd.SaveAsText function.  

Backwards compatibility

Making this approach work requires a commitment to two things: 1)  version control and 2) backwards compatibility.  You should be able to remove a module ([Alt] + [F],[R],[N]) and replace it with an updated version from the code library without having to worry about breaking anything in your application.

On the very rare occasion that I do break backwards compatibility, I highlight the fact in a list of change notes at the top of the module.  For example, here's an excerpt from my "clsSQL" class module:

'---------------------------------------------------------------------------------------
' Module    : clsSQL
' DateTime  : 1/31/2009 - 6/11/2020 15:43
' Author    : Mike
' Purpose   : Improves readability and consistency of SQL statements written in code.
' Notes     - Count property is much more efficient than DCount in all tested situations
' Changes   1/30/09: Added ability to keep some SetValues when performing ClearValues
'           5/25/11: Expose workspace to allow for transactions (BeginTrans, CommitTrans)
'           6/ 8/11: Re-raise errors after logging in the following functions:
'                       - SelectInto(), InsertInto(), Update(), Delete(), Clone()
' !!! COMPATIBILITY CHANGE !!!  - prior to this update, all errors would be handled within these procedures
'                               - care should be taken to ensure that these re-raised errors do not go unhandled
'           6/ 8/11: Add Reset() procedure to reset SQL-building variables while maintaining a single workspace

Version control

If my application does break as a result of an update to a library module, it is easy to identify the problem.  All of my Access applications are under version control.  When I replace a library module, any changes made since the last time I updated the module in this particular application are glaringly obvious when I commit my changes to version control:

Advantages over an Access library file

There are many reasons why I went this route versus implementing a single library file.

Do no harm

My biggest concern with a shared Access library file was that introducing a bug into the library file would immediately propagate that bug to all of my other projects.  Many times over the years I introduced a bug into one of my library functions.  Most of the time I caught it in the first or second application that got the update.  I fixed the bug, re-exported my library module to a text file, and updated the module in the one or two other programs that had gotten the buggy update.

Deployment

With this approach, there is nothing extra to deploy.  All the code is internal to the main application database.  This does increase the size of the front end file, but not very much.  

With a library database, you also have to decide whether to share that library database among multiple Access applications or let each Access application have its own copy (there are tradeoffs to consider).  Then you have to figure out whether to store the library database at an absolute location or relative to your custom application.  

Version control

Sure, you can put your library database in version control.  And you absolutely should, if you are going that route.  But there is still a major problem.  It violates the second rule of my Access version control philosophy:

2. Anything that can lead to a bug in our software belongs in version control.

Even though the library database is in version control, changes to the library database will not appear in our application database.  That means that if a client suddenly starts complaining that something is broken, you can't just look through the repository for the application database to see what caused the breakage.  That's a situation you want to avoid at all costs.

Modularity

Over the past 13+ years, I have accumulated over 100 standard and class modules in my code library.  There are some that I use in every project, like StringFunctions and FileFunctions.  There are others that I rarely use, like clsMultiPageTiff.  If I went the library database route, I would have three options: 1) a single, bloated database file with everything in it; 2) multiple library database files; or 3) a small database file with only the essentials and everything else managed some other way (like with a folder of text files :-) ).  None of those options sounds particularly appealing to me.

Final thoughts

The two approaches to managing your code library--a folder of files versus a library database--are both valid.  Each has advantages and disadvantages.

Reading between the lines of Daniel's post, it sounds like he may allow his clients direct access to portions of his Access applications.  For example, he may grant them full access to the source code so that they can maintain the application with in-house developers moving forward.  In that case, having a compiled Access library file to keep proprietary code separate from code you expose to clients is a big advantage to that approach.  I personally don't work that way, so that feature is not important to me.  It may be important to you.

Whatever you choose, it is worth the upfront time investment to choose the approach that will work best for you.  It's difficult to switch approaches once you've established one way of working or the other.

Thirteen years ago, I chose to maintain a folder of text files rather than a single Access database library.  I've never once regretted that decision.

Image by Falkenpost from Pixabay (Peabody Institute in Baltimore)