MS Access 2003 - Unable to shrink file

I built an Access front end this month that I am unable to shrink it.  I've tried the following:

Compact and Repair
Transferring all objects (tables, queries, forms...) from the current MDB file to a new MDB file.

No matter what I do, the smallest I can get the file is 50 MB.  If I ZIP the file, however, it shrinks it to 1.5 MB.  I've embedded some nice JPG images on some forms, but they're all small, around 30 to 40 KB.

It's a mystery.
jdanaAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So, you do have a split configuration ... backend with tables only?
What all is in the FE?

Meanwhile, try this - mainly the decompile part (skip the Reference check).


Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

AND ...
Once you get familiar with the Decompile idea (and ALWAYS make a BACKUP first!) ... you can add both Decompile and Compact/Repair to the Right Click menus in Windows Explorer, which I use multiple times daily:

Getting the Decompile and Compact context menu options
http://access.mvps.org/access/modules/mdl0039.htm

mx
0
mbizupCommented:
Embedded images are notorious for causing database bloat.

If Compacting/reparing hasn't made the file any smaller, that may be the best you can do.

If you have an Access 2007 machine available however, you might try making a backup and try compactig/repairing through Access 2007.

I'm not sure what the difference is, but I have seen some Access 2003 databases which were unaffected by Compact/Repair in Access 2003 significantly reduced in size when Compact/Repaired on Access 2007 Machines (I haven't tried this in Access 2010, as we do not have that available at my place of work)
0
peter57rCommented:
Agree..it's bound to be images.
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
<I've embedded some nice JPG images on some forms, but they're all small, around 30 to 40 KB.>

Does not matter, ...Access still stores the images internally as Bitmaps (.bmp, ...the MS Format) , to display them.
bitmaps are typically bigger than the corresponding jpg

So the size you see ma be just "what it is"

To be sure, delete the images and try shrinking the db (compact/repair)...
If the DB shrinks, then that is your issue.

How about linking to the files instead...?

Also note that to may , 50 MG might not be consider "Big", ...just to keep thinks in perspective here...

Is this 50GB encroaching on arbitrary size limit?

JeffCoachman
0

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
Jeffrey CoachmanMIS LiasonCommented:
Better wording:

Does not matter, ...Access still stores the images internally as Bitmaps (.bmp, ...the MS Format) , to display them.
bitmaps are typically bigger than the corresponding jpg

So the size you see may be just "what it is"

To be sure, delete the images and try shrinking the db (compact/repair)...
If the DB shrinks, then that is your issue.

How about linking to the files instead...?

Also note that to many, 50 MB might not be consider "Big", ...just to keep things in perspective here...

Is this 50GB encroaching on any arbitrary size limit?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" I've embedded some nice JPG images on some forms, but they're all small, around 30 to 40 KB."
Gee ... I missed that.  IF ... you really want to do this, then you need to use DBPix:

http://www.ammara.com/dbpix/access.html

It does *all* the work for you. Examples show how to add a simple 'control' panel to Load, Save, Zoom In/Out, Size To Fit and much more.  AND ... virtually eliminates BLOAT associated with storing images in an Access MDB. I have 3 clients who sell commercial run-time products that use DBPix.

Note. I have no connection with DBPix ... except I have used it many times ...

mx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<No matter what I do, the smallest I can get the file is 50 MB.  If I ZIP the file, however, it shrinks it to 1.5 MB.  I've embedded some nice JPG images on some forms, but they're all small, around 30 to 40 KB.>>

 That's quite possible and as the others have said, it's probably the images.

 But trying to compare a zipped file size to a compacted but non-zipped file is not the same thing.

  Depending on the way the data is stored, there maybe a large abount of empty space within the DB.  A compact won't removed this.

 For example, a page in a JET DB is 4096 bytes.  If you only use 100 bytes out of the page it doesn't matter; JET can't get rid of the page on a compact. But if you zip that page of data, it's going to compress down a lot because it's mostly empty space.

 But I would agree with the others that it's the images.   Take any .JPG on your computer and zip it and you will see that it compresses down extremely well.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"  Take any .JPG on your computer and zip it and you will see that it compresses down extremely well."
Ummm ... I don't think so.  A JPG is already a compressed image.

jpg
0
hnasrCommented:
Embeded objects have high impact on the size tof he database. If the database is not copyrighted or confidential, try to upload so experts can check their comments.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<"  Take any .JPG on your computer and zip it and you will see that it compresses down extremely well."
Ummm ... I don't think so.  A JPG is already a compressed image.>>

 Yes, your right.  I was thinking of a TIFF.

Jim.
0
hnasrCommented:
Try this:
Options
Current Database
Application Options
    Picture Property Storage Format
              Select Preserve Source image format
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Try this:>>

  Good thought!  However that's only on 2007 and up I believe and this is A2003.

  Microsoft added that to get around the OLE / DB bloat problem with graphics stored in the DB.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
TIFF ... big DIFF ... ha ha ... for sure
0
jdanaAuthor Commented:
Wow,

Lot's of comments.  

DatabaseMX - I tried the decompile trick.  I think the same thing can be acheived by tweaking a VBA module, and then changing the Project Name.  (When the current module disappears and reappears, you know it's decompiled.)  The trick got me down to 46 MB.

Jeff - I like the linked image suggestion.  I created a little class module to return the image path based on the file path of the MDB file, enabling me to drop the MDB on the production server w/o much headache.  

What was the final result, after I converted all the embedded images to linked images?  8 MB.  Not too shabby.

Thanks!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I think the same thing can be acheived by tweaking a VBA module, and then changing the Project Name. "
Umm. I don't think so.  Check out the Decompile info on Michael Kaplan's site.

btw ... FWIW ... I'm pretty sure you could achieve the same result using DBPix, in which case you do not have to worry about any linked images and the maintenance associated with such,.

mx
0
Jeffrey CoachmanMIS LiasonCommented:
jdana,

If it were me and I only had a few small images (as you stated), ...and the db did not need to be very "Portable", ...and the purpose of the DB might not need "hundreds" of megabytes of storage.....

I would just leave it as is.
The 50 MB will then be the new "Baseline" size.

As the database, and your needs, evolves, ...then re-evaluate...
0
Jeffrey CoachmanMIS LiasonCommented:
jdana,
Care to show us the contents of this file?
WTH?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Jeff ... ?
0
mbizupCommented:
Jeff,

That image was a screenshot originally posted by Joe, not jdana.
0
Jeffrey CoachmanMIS LiasonCommented:
Oh.
;-)
LOL
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.