To Compile or Not to Compile

Mike Wolfe -

I don't distribute compiled versions (.accde/.mde) of my Access applications to users.  I distribute the original uncompiled versions instead (.accdb/.mdb).  

I do this for a couple of reasons:

  1. Version compatibility
  2. Error logging

Advantages

Version Compatibility

If you use Access 2016 to compile an .accDB file into a .accDE file, then that compiled file won't run on older versions of Access, such as 2010 or 2013.  Access MVP Albert Kallal has a great post explaining the issue on UtterAccess:

"If an accDB works, and the compiled version (accDE) does not, then MOST of the time this comes down to the runtime NOT being the same release or version that you compiled the application with."
 - Albert Kallal

Albert goes on to point out that with an .accDB, "Access will detect the difference and re-compile your VBA code on the fly. However, with an accDE, no source VBA code is available."

Error Logging

As I've written about extensively in the past, I use vbWatchdog for my error handling.  One of the features of vbWatchdog is that it provides the actual code from the line that generated the error.  It also does this for each calling line all the way up the call stack.  It provides this information at runtime, so all of the details can be logged to an error tracking database.

The catch is that the original code is only available to the error handler if it's being run from an .accDB.  When logging errors in an .accDE, vbWatchdog is only able to log the line number where the error occurred.  This is not a limitation of vbWatchdog; the original code simply isn't there to log in a compiled .accDE.

For fully compiled projects (such as Access MDE / ACCDE applications), ErrEx.SourceLineCode will return a blank string since the source code is not available.  In this scenario, use ErrEx.SourceLineNumber instead to identify the line of error in your source code.
 - from Quickstart: Automatic line numbering

On the bright side, vbWatchdog does NOT require you to include line numbers in your .accDE code.  Rather, the vbWatchdog add-in can be toggled to display line numbers inside the VBIDE.  This is a nice feature, but I much prefer seeing the actual erroring code in my error logs.

Disadvantages

Source code tampering and software piracy

When you deploy an uncompiled .accDB file, there is nothing to prevent users from snooping around your code and even making changes to their copy of the front-end.  A motivated competitor wouldn't even need to reverse engineer your software, they could simply...forward engineer it?

I don't worry about this for two reasons.  Most of my clients are corporate clients or governments who have no interest in being in the software business.  And most of my applications are glorified CRUD (Create, Read, Update, Delete) apps that would be relatively straightforward to reverse engineer even with a compiled copy of the front-end.

Besides, if you're really worried about software piracy, a motivated competitor could decompile even a compiled version of your Access application.  If that possibility keeps you up at night, you should probably be writing web applications or desktop apps in a fully compiled language like C++ (even stock .NET apps can be relatively easily decompiled).

Direct user access to tables and queries

UPDATE: As Philipp Stiefel pointed out on Twitter, compiling to .accDE *does not* actually prevent users from gaining direct access to tables and queries.  I'm going to leave the following paragraphs in place, because I think it's good information on its own.  But bear in mind that compiling to .accDE is not a solution for this particular problem.

The biggest disadvantage to this approach is the possibility of your users launching your program with a full (non-Runtime) version of Access and having direct access to the data via tables and queries.  This is no small thing, as a well-designed Access application should provide structured access to this data via forms and reports.  Letting your users run wild with direct access to the source data is a recipe for invalid and unreliable data.

The best way to avoid that situation is to use a deployment tool with the ability to create program shortcuts that pass the /runtime switch as part of the application launch command.  I also like to include the /excl switch to prevent the user from accidentally launching two instances of the program on the same machine, as this can sometimes lead to front-end corruption.  Below is a sample of a shortcut that gets created when users install our TaxColl2k software.

This article was updated 1/13/2021 to clarify that compiling to .accde does not prevent users from directly accessing tables and queries.

Image by WikiImages from Pixabay