Access 2k10 Bloat reduction

Ms Access app, front end(access 2010),  Bloat Reduction. This app has been running for years, has been compacted and repaired on a regular basis during that time.
it is now 390mb. It contains some of everything (i.e. tables,queries,forms,reports,macros,modules). If I create a blank database and import these items I get a nice reduction to about 14mb. However, if I do the same but include in the options all of the import items, I get very little improvement. This seems to be where the bloat is but don't know how to reduce this or ignore it on the import, don't know what I lose.
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should split your application into a Backend (tables only) and a Frontend (everything else). You place the backend on a shared network resource, then relink the Frontend to that backend location. You then make copies of the Frontend and provide each user a distinct copy of the FE.

Note that access has a "splitter" wizard, but you can this yourself fairly easily:

1. Create a new, blank database
2. Import everything EXCEPT tables from your old database to your new database.
3. In the old database, delete everything EXCEPT tables
4. In the new database, create links to the tables in the old database
5. Compact and repair both
John TsioumprisSoftware & Systems EngineerCommented:
To add ....also take a look at the Graphics/backgrounds you use...they can eat of space and they are not compacted..
if I do the same but include in the options all of the import items
What does this mean?  What exactly did you not copy the first time?
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can try running a /decompile on it:

 That sometimes will clear some junk out.

 Also, make sure the database options (Access Options, Current Database) are set correctly:

  If this is an old database brought forward, you may still be using the old picture format.   This assumes that you have pictures in the DB of course.   If not, forget this.

jsgouldAuthor Commented:
Scott pls note that issue description begins by referring to the front-end. The db has always been split.

Pat when importing there is an "Options" choice and under Import are a list of check boxes for items to import. when I did this I checked them all. This appears to make the difference between a very substantial down sizing to a very trivial downsizing. However, I don't know what all of these things are or how to figure out which one(s) are causing the problem.

Jim no pix in the db, only file paths
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This appears to make the difference between a very substantial down sizing to a very trivial downsizing.

if I do the same but include in the options all of the import items,

 Do that in multiple passes then; tables first, then queries, then code, then macro's, then reports, and then forms.

 Check the size of the DB each time.    Once you've have it narrowed down to a category, then you can work on just that category until you find what's causing the problem.

 And try a /decompile first.   Easy way to do that is with a one-line batch file:

"C:\Program Files (x86)\O2000\Office\msaccess.exe" /decompile "C:\EDI945Import.MDB"

changing the path to MSACCESS.EXE and the name of the DB of course.

The only large impact this could cause is if there are way too many indexes and one or more of them might even be corrupt.

Access has a default to "help" novice developers that adds indexes on every column that ends in ID and several other suffixes.  So you may have lots of unnecessary indexes.  

Turn the auto index feature OFF.  Also turn off Name auto correct while you're at it.

Create the new empty database again and import all the objects with other unchecked.  Then go through the tables and add back any indexes you think are necessary.  Keep in mind that Access automatically creates HIDDEN indexes on all foreign keys so do not create additional indexes on foreign keys.

Once you've finished with the indexes, open the relationships window and build the relationships.  Always enforce Referential Integrity.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I saw that, but I also saw this:

It contains some of everything (i.e. tables,queries,forms,reports,macros,modules).

In most cases, those two statements cannot both be true. That said, I have seen Backends with a very few forms/queries for maintenance.
jsgouldAuthor Commented:
tried decompile but doesn't work. opens the access app and cmd window and appears to be running (cursor blinking). let it run for about 2 hours, nothing happened. closed app and decompile.bat ended.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
based on that and what you outlined in your question, something is corrupted then.  Most likely, it's in the VBA project file inside the DB.

Again though, note that embedded images (say in a report) can chew up a lot space.   I've got one DB that was 6MB, and including one complex image on a bill of lading made it jump to 64MB.

But to figure out what's going on and what is causing the problem, you are going to need to break it down in steps:

1. Create a fresh empty DB.
2. Import tables, then check the size.
3. Import queries, then check the size.
4. Import modules, then check the size.
5. Import reports, then check the size.
6. Import the forms, then check the size.

 Once you have it pinned down to an object type (and it will most likely be a form):

1. Create a fresh empty DB.
2. Import some of the objects, then check the size.

 Repeat steps 1 and 2 until you figure out which is causing the problem.

  Now what you can try using is two undocumented functions:


 This will let you save an object as a file from the old DB, then import from that file into a fresh DB.

 If you can't carry out the operation, you'll have to re-create it.   Note also that you might get the form or report, but not the corresponding VBA.

jsgouldAuthor Commented:
I've done essentially all of the suggested step by step instructions. The bloat comes from a  single option in import objects options check box labelled "All images and themes". This one option takes the size from 16mb to 360mb. My problem is I don't know what I'm losing, if anything, if I just don't import this. Anyone with a clue?

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
Do you have embedded images on your forms or reports?  Did you make custom themes?

But most importantly, does the app work correctly if you don't import these objects?  If it does, I'd live with that for a while.  Keep a copy of the bloated database for a while just in case you might need to go back to it.
John TsioumprisSoftware & Systems EngineerCommented:
Just an addition...recently i saw a case of an clean Access with rather big size...eveything were minimal images no nothing...i deleted eveything and still the size was big....turned out in the hidden table MsysResources was filled with a lot of unused .jpgs....deleted them ...presto database to normal size.
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

From novice to tech pro — start learning today.