Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times

[ Access | Main ]     (Last update 2010/10/23)

A little known and almost undocumented (see note) feature which can help dramatically shrink the size of front end databases thus reduce bloating and decrease start-up times is the /decompile option. As you compile VBA code various bits of old compiled code get left behind. The /decompile command line option cleans out these and allows for a fresh start.

As a general rule my experience has been, in the absence of a specific error message, that you don't need to run the decompile more often than once every month or so to clean things out. You will see the biggest improvement, of course, if you've never done this before on an MDB.

Symptoms

I find I decompile when:
   1) I get an error involving VBA332.DLL in Access 97;
   2) When really weird things start to happen in code;
   3) When the startup form takes several seconds to load.

Error 49: "Bad DLL calling convention" can occur for no apparent reason. Sometimes commenting the code where the error occurs, compiling, then uncommenting that code, and recompile again fixes this problem, at least for a while.  However other times only a /decompile will fix this problem.

One newsgroup posting stated Access 2000 crashes with invalid page fault, or IPF, in VBE6.DLL at 015f:650ad7f2 when he tried to compile one of his projects. Q223206 says to reinstall Office 2000 Professional because DAO360.DLL is damaged or missing but that didn't solve the problem.  Decompile did.

I've personally seen msaccess.exe - Application Error "The instruction at "0x001e543e" referenced memory at "0x00000000". The memory could not be "written". When I tried it again I saw "0x021a0625" referenced memory at "0x0a358fd0".    Thus I strongly suspect the eight digit addresses are not meaningful.  Decompile fixed this problem.

Whenever I see a newsgroup posting mentioning an invalid page fault (IPF) involving VBA332.DLL I always suggest decompile as the first thing to try. Most of the time that works.

An email to me from GW had the following slightly different message.  msaccess.exe - Application Error "The instruction at "<subroutine or module name>" referenced memory at "0x00000000". The memory could not be "read'" 

Error number 16 - Expression too complex.   I just had this problem.  I have no idea as to what caused this.   This one is really weird as I'm using the following code to execute startup code after verifying the references.

Access.Application.Eval ("fcnAutoExec2()")

The above was successfully executed but it returned the above error. See Subject: INFO: How to guarantee that references will work in your applications for why I'm using this.

Note that putting a stop statement in the fcnAutoExec2 and removing it also fixed this problem.

How to do a decompile

Do a backup before the /decompile. Of course you're doing daily offsite backups so this isn't a problem anyhow, right?!?!?!  I, once, had a decompile make things much worse.   Fortunately I had just made a copy of the MDB.   A decompile on a copy of the backup then worked just fine.

1a) Create a shortcut with the following
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "d:\My Documents\access\mayapp.mdb" /decompile
As appropriate for your environment of course.  Note that if your path to your MDB contains a space it must be enclosed in double quotes just as the MSACCESS.EXE component.   You can create a short cut by right clicking in the directory of your choice, highlighting new and creating

or

1b) Create a one line .bat or .cmd line with the above text in it it using notepad.  

Hint:  Right click in your directory of choice, highlight new and select Text Document.  You will be presented with a new file named "New Text Document.txt" or similar.   Rename that file to something like "decompile.bat".   Then right click that file and select edit.  Notepad should then run opening up that file.

2) Click on the short cut or click on the .bat or .cmd file and, in Access 97, you'll get the message "... has converted the code in ... to your current version of Visual Basic". This message does not appear in Access 2000 or newer.  Hold the shift key down when you click on Okay so the MDBs autoexec macro won't execute or the start-up form will not be opened.  If you have any class modules exit the MDB and re-enter it.  This will keep the class modules intact.

3) Now compact the MDB to clean out the old VBA code..

4) Hit Ctrl+G to open the debug/immediate window or go into any module and click on Debug.  If Access 97 click on Compile and Save All Modules. If Access 2000 or newer click on Compile.   Compiling is particularly important to see if there were any problems and for performance reasons. 

5) Then compact again.

Assuming you had compiled your original MDB the new MDB should be significantly smaller.

Access 2002. 

Decompiling by itself is no longer sufficient.   Decompiling can increase the size of the MDB.   According to Microsoft once you've decompiled you will need to import all the objects into a new MDB.   Office 2002 SP3 solves this problem.

Technical Information

For more technical information see the Microsoft Access FAQ page and Michael Kaplan's detailed information.

Database properties

I would recommend that you set all database properties using some code you run after importing.  See Setting Startup Options Programmatically in Microsoft Access for more details. 

Testimonials

I have seen five Mb files shrink to 2.5 Mb files. Jürgen Welz reports that "Decompile cut FE size down from 13 Mb (compiled and compacted) to 3.7 Mb." I've also seen MDE's shrink as well after decompiling.

A client was wondering why it was taking so long before the first screen was displayed. A little bit of testing showed that this was before any code was being executed. After doing a decompile the MDB was only reduced by 1 Mb. However the start-up time was reduced from 2-3 seconds to nearly nothing. Mind you these are Pentium 400s with 128 Mb Ram running Windows NT so RAM wasn't a problem.

I've repeatedly noticed that start-up time keeps getting longer and longer.  Once it gets real irritating, at about two seconds or so, I do the /decompile and compact routine as above.  Then startup times are back to instantaneous.  This is usually takes about a hundred to two hundred hours of working in the MDB.

RSK reports "My mdb file size was 42 mb, the mde size was 36 mb (after compact).  After I ran this command the mdb size went down to 17 mb and the mde size went down to 12 mb and let me tell you I'm very bloody happy.  Load time is instant now, was 5-10 seconds, and at least a 20% increase in general speed.  Yeah I was rather surprised myself, but I can't deny what I see and my customers have commented on it, especially the end of sale process"

GR reports "The app had grown to 32 MB without the decompile and compact. Now its 3 MB!"

DM states "Thanks for the tip - worked a treat and the speed is amazing. I shall now do the other front-ends and see if i can get them up to speed as well - omigod: i'm getting user emails stating the speed is great!!!" and "... shocked!!!  yeh, startup on the LAN used to take anywhere from 20secs to a full minute. Of course this is also dependant on traffic but it is now a blink of the eye."

Another posting stated "Me FE dropped from 60 MB to 35 MB".

Class Modules

Immediately after doing the decompile exit and re-enter the MDB.  Holding down the shift key so your code doesn't start executing.  Then do a Compile and Save All.

Otherwise you can get the following message "Compiler Error. Only valid in Object Module."

Thanks to Albert D. Kallal for the above suggestion.

Toolbars

An undocumented problem which can cause bloating and increase start up times is custom toolbars. Along the way the sysObjects table seemed to get quite bogged down over this. Create new custom toolbars in a new container MDB  and imported all the objects into the new container. One report was the file size dropped from a compacted 55 MB to 16MB. So now the loading time is instantaneous and they have wonderful resource savings.

Notes

Decompile is now officially documented at ACC97: How to Repair a Damaged Jet 3.5 Database - 279334 in the Recovering a Damaged Database section point 9.  As well as the equivalent A2000 and A2002 articles.

"Insufficient project information to load project on platform or with version now being used." error message.   Try doing a compile and save all and then doing a decompile.

Decompile Anomaly by Peter Miller

[ Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available