Decompiling VBA code in an Access .mdb file

mdbgeek had a problem the other day where one day a database was working and the next it isn't. I had totally forgotten to offer up the idea that she could decompile her VBA code, not that it might help in her particular case, but here it is anyway:

As you compile your VBA modules and class modules in 2000 and 2002, the old compiled code does not go away, even after compacting. Not only does this old psuedo-code (pseudo meaning it's symbolized code, sort of a short-hand way of describing your functions and subroutines, but it's not actually machine-level executable code).

Decompiling, compacting, and then compiling again ensures that the only compiled code in your Access database is the latest code you've introduced, and not all the code you've written before the last ice age.

1. Navigate to the Program Folders on your computer.

2. Open the MS Office Folder

3. Navigate to MSAccess.exe and make a shortcut of it.

4. Move the shortcut to where your database resides (makes life easier and you'll need to do this for each database to decompile).

5. (optional) Rename the shortcut to something like "decompiler for my database whose name is...." so you know that opening it blows away your compiled code :)

6. Right-click on the shortcut to view its properties. In the Target field of the properties dialog box for the shortcut you'll see something like "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"


7. In the target field, right-arrow to the end of the pathname, type a space, and then, in quotes, type (or paste) the full pathname of the location of your MS Access file to decompile (easiest way is to copy from the address bar on your window where the file resides, if you have the address bar showing).

8. Still in the Target field, type a space, and then with no quotes, type /decompile

9. Click OK to accept the changes and to close the dialog box.

10. Hold the Shift key down and keep it down.

11. Wiggle your ears

12. Double-click on the shortcut.

Your database will open, totally decompiled, but the space still taken up where the compiled code was.

13. Let go of the shift key.

14. Stop wiggling your ears.

15. Compact the database (Tools, Database Utilities, Compact Database)
After compacting, the database opens up.


16. Go to any module in your database. Compile it again (Debug, Compile).

All Done!


This is not a magic bullet for every situation, but if you've upgraded your database from 97 to 2000, or from 2000 to XP, you will clear out a lot of old crap that the database will never exploit, and it'll run faster.

Note: Every time you change the name of the file to be compiled, you'll have to change the pathname in the shortcut.


Note: Open the database with the shortcut ONLY to decompile your code, not use the database (you actually want your code to stay compiled if it's clean).

Decompile your MS Access.mdb file more frequently if you have tons of code (the code in one of my databases, from all modules, prints to over 100 single-sided 8x11 single-spaced pages).

If you have designed your database with an FE/BE architecture (with one MS Access file performing the front end gui work linked to another database that actually has the data) then you'll want to decompile/compact the FE (which contains all of your code) before deploying it among your end users.

The back end, containing no code, only needs to be compacted occasionally, not decompiled, since it contains no code (or by design standards, shouldn't have code).

--xordevoreaux

Never post to a forum before your first cup of coffee.

In the sentence where I interrupted myself and didn't finish ("Not only ..")
I wanted to say that old pseudocode is still parsed by Access during runtime, even if it isn't used, so not only does it take up space, it slows your application.