Decompiling an Access Database

As a developer, it becomes necessary to decompile your database from time to time.  The frequency depends on a multitude of factors, but at the very least, one should decompile one’s application prior to release.  Decompilation is yet one more step a develpper has to take to ensure their database/code is clean and optimal for their end-users.  Also, keep in mind that the best approach is to decompile the database on the development machine and then recompile on the end-user machine.  This ensures that the database is compiled using the end-user’s libraries minimizing any surprises when put into production.

Before going any further on this subject, let me emphasize once more the importance of making a backup of your database prior to performing a decompile of your database!

 

One-time Decompile

The MSACCESS.EXE command line accepts several command line switches, one of which is to allow decompiling your database.  There is no other means to decompile a database.  So one merely needs to create a shortcut including the appropriate command line switch in order to decompile any given database.  The basic syntax would be:

"FullPath\MSACCESS.EXE" "FullPathAndDbNameWithExtension" /decompile

Examples:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

Or

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

 

Reusable decompilation method

On the other hand, it become tedious to create a shortcut for each database you create/manage and as such a more automated method may be a good idea to implement.

1. Using Windows Explorer (etc.) navigate your way to your MSACCESS.EXE and the copy the file.
2. Navigate to the %APPDATA%\ Microsoft\Windows\SendTo\ Folder
3.  Right-Click within the folder and select Paste shortcut from the context menu
4. Rename the shortcut as you wish, for instance MSACCESS Decompile
5. Right-Click on the newly created shortcut and select Properties from the context menu
6. On the Shortcut tab, edit the Target control by adding /decompile to the existing value.
   You should end up with something along the lines of:

"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile

Or

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile

1. Click Ok
2. Close windows explorer.

Now whenever you wish to decompile a database you need only right-click on the Access database in Windows Explorer and select “Send To –> MSACCESS Decompile.

 

Special Note

Since after decompiling we always perform a compact of the database, you can perform both in one step if you’d like by appending a /compact to your /decompile command.  This is not obligatory by any means.  Personally, I prefer the granularity of performing each step myself, but should you wish to automate things a little more you’d do something along the lines of:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact

Or

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact

"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

Or

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

Do note that if you do implement this variation for the reusable decompilation method, you’d probably be best to also change the name attributed to the command in step 4. to MS Access Decompile and Compact

 

Proper Steps to Follow when Decompiling an Access Database

I found the following instructions from David Fenton, and thought they fit right into this subject.

1. Backup your database.
2. Compact your database.
3. Decompile your database (per either method listed above. Be sure to bypass any startup code by holding down the shift key, so the code does not immediately recompile).
4. Close that instance of Access.
5. Open a new instance of Access and open the database you just decompiled, but BE SURE YOU BYPASS ALL STARTUP CODE (i.e., hold down the shift key). If you don’t do that, then you might as well go back to step 3 and try again, since if the startup code runs, your code will recompile before you are ready to do so.
6. Compact the decompiled database (and be sure you hold down the shift key so that it bypasses the startup code; see #5).
7. Open the VBE and on the Debug menu, choose COMPILE [name of project].
8. On the file menu, save the project.
9. Compact again.

Why are all these steps necessary?

Because you want to not just decompile the VBA, you want to make sure that all the data pages where the compiled p-code was stored are completely discarded before you recompile.

I also recommend:

1. In the VBE options, turn off COMPILE ON DEMAND
2. In the VBE, add the COMPILE button to your Toolbar.
3. Compile often with that button on the toolbar, after every two or three lines of code.

Another good resource on the subject is Michael Kaplan’s article on the subject.

Lastly, instead of creating various shortcut to perform these actions, you may prefer to use my utility that will add such functionalities directly into the Windows Context Menu. To learn more check out Windows Explorer MS Access Database Right-Click Context Menu.