Applies to: Access 95 and up Level: Intermediate.
Ever had one of your Access databases stop executing code correctly or not want to compile? Or are unable to create a MDE no matter what? Or do other strange things related to code? An undocumented command line switch exists within Access that may help.
Access uses VBA (Visual Basic for Applications) for its code support. The VBA project file, which contains all the code for your Access application is stored by Access in a JET database and used when needed. In that project file is a copy of the source code and also a copy of the same code in a compiled state (if you have compiled your application). This is done so that at run time, code can be executed faster because the code has already been compiled.
TIP: Always make sure your Access applications are compiled when released. Your code will execute faster, your app will be snappy, and as a result you'll look better! Also be aware that there are things that you can do in your application that will cause the app to become un-compiled at runtime (like switching a form into design mode and altering it). If you want to make sure your app is always compiled, consider distributing it as a MDE; MDE's lack source code, so the DB must run in a compiled state. There are drawbacks to MDE's though, so be aware of those issues when developing.
Often because of bugs, JET database corruption, or other issues, the source and compiled copies get out of sync with one other. So your code in the VBA editor says one thing, but what gets executed is something else.
One way to fix this is with the un-documented command line switch /Decompile. Decompile was introduced during the Access 95 beta. It was required because Access 95 was the first release to use VBA instead of Access Basic. At that time, Microsoft developers were still hooking up Access to VBA and internally, some of those interfaces would change from release to release within the beta. That meant that a developer sitting with a database flagged as compiled could possibly have code that would not execute correctly when switching to a new release. Back then, it was a chore to create a fresh DB container and import everything into it (compiled code is not coped when you import into a new DB). Since this was time consuming (beta releases come fast and furious at times), everyone was complaining that a faster method needed to be found to invalidate all the compiled code in a database. The result was the /Decompile switch and that's what it does; invalidates all compiled code in a DB.
You use /Decompile from the command like this:
C:\...\MSACCESS.EXE C:\...\MyDB.MDB /Decompile
You can do this from the command line (run box), or if your like a lot of us and hate typing, from a batch file (which you would need to edit each time for different DB's). But if you really want a slick way to handle this and you don't mind fooling around with the registry a bit, take a look at the following:
Download the file and then double click on the file from Explorer. You'll be asked if it's OK to import into the registry. Say yes. Now when you right click on an Access DB, you will have the compact and decompile options available right from the shortcut menu.
here are also a couple version handlers as well with /Decompile options:
I have not used either product and can't vouch for either.
It should be mentioned that this is an un-documented and un-supported switch by Microsoft. By using it, you do so at your own risk. Databases have
been messed up by using it. While this doesn't happen too often (it's only happened twice to myself in the past fourteen years), it does
happen. Make sure you have a backup before using it.
Personally, given the ease in which you can now import into a fresh DB, if I suspect corruption, either in code or the DB, I always import into a fresh DB. I may have wasted a couple of hours over the years in doing so, but I like the extra sanity check I get as part of the import process. After all, you don't know if a problem with code execution is related to issues in the VBA project file or is somthing more systemic.