Programatically Rebuild Access ACCDB To Remove Bloat

Hi Experts,

I am referring to bloat from extensive development.  The data is all in backend databases.  I delete any objects (Modules, Forms, Reports, Queries, Tables) that are not required and Compact & Repair often.

Am I correct that to use the decompile suggestion that I would:
- Save As an accde.
- immediately decompile the accde.

Is there anything else I need to do after decompiling?  e.g. Update Tools > References, Options, etc?

BTW.  I have Total Access Analyzer.

Thanks,
Bob C.

Hi Gustav,

After doing the following command (located in a shortcut) the file was still the same size as before.  I then opened it, did a Compact & Repair and closed it.  It has now shrunk by about 60% which probably makes sense.

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "M:\Pgm\01-Install Design\Decompile\SAS.accdb" /decompile

So am I correct that after the decompile I need to do the Compact & Repair to complete the process?

Thanks,
Bob C.

Hi Pat,

Thanks for the additional insight.

So am I correct that I need to at lease do the Compact & Repair following the Decompile?

Is there any downside to doing this?

Thanks,
Bob C.

Hi All,

Thanks for the suggestions / insight.

Bob C.

Just a couple of add on comments:

1. /Decompile does not remove all bloat (although in most cases, it's a good size chunk of it).  It only involves code.  To remove all the junk, you need to create a new db as well, and to really dig everything out, you need to do a savetotext / loadfromtext on objects to remove everything.   There is some code and one utility I'm aware of  (EatBloat) to do this.

2. /Decompile  was not created for removing the -p-code because of any short comings in compact and repair..   It was created when Microsoft was hooking up VBA to Access to replace Access Basic.   They were changing the interfaces from week to week and back then, creating a new DB was a time  consuming process.   So everyone needed a quick way to invalidate any compiled code.

Jim.

Hi Jim,

Thanks for the additional insight.

Since decompile does most of it I'll use it most of the time with a periodic manual rebuild.

I'm not aware of what 'savetotext / loadfromtext' is or how to do it.

Thanks,
Bob C.

Bob,

<<I'm not aware of what 'savetotext / loadfromtext' is or how to do it.>>

https://blogs.msdn.microsoft.com/thirdoffive/2006/08/31/templates-quick-overview-of-saveastext-and-loadfromtext-aka-lets-get-boring-again/

From that blog post:

This mechanism was never meant as a import/export entry point, but as a simple way to create form, macros, report and queries from a pre-canned source, such as a number of the wizards shipped with Access.

 So again, it's something that was designed for something else, but you can use it to repair forms and reports, and possibly reduce bloat.  

 For both, it's the fact that you are creating an object from scratch within the current version and not carrying any baggage over from previous versions.

If you look around, you will see various places that have written utilities to utilize this:

https://access-programmers.co.uk/forums/showthread.php?t=99179
https://www.experts-exchange.com/questions/28450832/SaveAsText-all-forms-or-objects.html
https://sourceforge.net/projects/access-srcutil/

 Prior to all these was a utility written called "Eat Bloat", which did the same thing and has been floating around for a considerable number of years.   I'm not sure where it stands at the moment.   It was written by one person, then taken over by another who really never did anything with it that I'm aware of.

 There is enough code floating around though that you could get something working fairly quickly.  The code in the first link is fairly complete if I remember correctly.

Jim.