Periodically, Microsoft Access databases with VBA module code perform oddly. Code that previously worked suddenly fail or behaves incorrectly. Here are some examples of errors that come up:
This error is particularly confusing if your file is local and not on the network:
It's all very confusing, especially when Compact and Repair does not help.
The initial assumption is that the database is corrupt, which is partially true. People then resort to creating a new database and importing their objects into it which is time consuming and a pain. It solves the problem, but there's a better alternative to check first.
Fortunately, Microsoft Access offers a way to fix the VBA compiled state with the /decompile command line option. The Compact and Repair feature only applies to the tables and does not impact VBA code. The decompile command does.
When Access is started with this and you open your database, it discards all the old VBA compilations, and leaves just your VBA source code. You'll need to compile again to get the compiled state. After compacting, your database may be significantly smaller.
To decompile your database, follow these steps:
The database size should be reduced and the strange errors related to the VBA code should be gone.
The example above is for Microsoft Access 2016, but it also applies to Microsoft Access 2013, 2010, 2007. 2003, 2002, 2000, 97 and earlier.
To simplify doing this in the future (and you'll likely need this again), create a shortcut on your Windows desktop pointing to where your copy of Access is located:
C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE /decompile
Then, whenever your database is acting oddly, you can easily launch the decompile version of Access to open your database and decompile it. In a few minutes, you're back up and running with a smaller database.
Total Visual Agent manages an unlimited number of databases across your network and schedule database chores in the middle of the night. Perform compact, backups, data extracts, running macros, command lines, and other tasks. Schedule events hourly, daily, weekly, monthly or just one time. Complete audit trail with email notification if anything goes wrong. Try the Free Trial today!
Total Access Admin lets you monitor Access databases in real-time to see who's currently in it and when they leave. It flags users who disconnect in a suspect manner which may indicate a source of database corruption. It can also perform tasks after everyone has exited such as compact. Try the Free Trial today!