Programatically Rebuild Access ACCDB To Remove Bloat

Hi Experts,

Is there code that I can use to rebuild an existing accdb to remove the bloat resulting from extensive development?

Compact & Repair only removes some but there is still a lot left.

Currently I do it manually and often see a difference from say 100 Meg to 30 Meg.

Thanks,
Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Define: "bloat resulting from extensive development" ?
Is that temporary datas or objects not removed ?
Dead code ?
0
PatHartmanCommented:
You can get rid of additional bloat by using the /decompile switch.

In the Windows Run box type:
C:/fullpath/yourdatabasename.accdb /Decompile

Of course if you are talking about junk data and unused queries, etc., how would you expect an automated process to decide what is no longer useful.

If you want a tool to help you decide what is being used and where, look at Total Access Analyzer by FMSINC.com
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob CollisonSystem ArchitectAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
No, not an accde, an accdb.
And after the decompile, it is an immediate compress/repair.

Another method is to create a new file and import all objects from the old. This will, however, not transfer special settings or properties.

/gustav
0
Bob CollisonSystem ArchitectAuthor Commented:
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.
0
PatHartmanCommented:
Am I correct that to use the decompile suggestion that I would:
- Save As an accde.
- immediately decompile the accde.
Please read my directions again.  I didn't say anything about making an .accde.

Access "compiles" every procedure (if it is nor already compiled) prior to running it.  Making an .accde takes this one step further in that it compiles all code at once (rather than waiting for the first execution) AND it then removes the source code.  Since VBA is an interpreted language rather than a true compiled language, the code is "compiled" to what is called  p-code.  The interpreter takes the p-code at runtime and converts it to executable code in order to run it.  Compact and repair does not remove all p-code and so the MS Access development team came up with their own undocumented method of removing all remnants of the p-code and this is it.

I generally do the decompile and then compact and do the decompile again.  This method can sometimes also fix corruption so you will see it referenced in conjunction with those questions also.
0
Bob CollisonSystem ArchitectAuthor Commented:
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.
0
PatHartmanCommented:
No, it is not required.  I do it because sometimes it takes more than one /decompile to exorcise all the old p-code.

The recommendation is to backup prior to /decompile since the decompile is an undocumented feature and technically not supported by Microsoft.  So, I zip the database first.  Then decompile the .accdb
0
Bob CollisonSystem ArchitectAuthor Commented:
Hi All,

Thanks for the suggestions / insight.

Bob C.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Bob CollisonSystem ArchitectAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.