Link to home
Start Free TrialLog in
Avatar of jdana
jdanaFlag for United States of America

asked on

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.
SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Agree..it's bound to be images.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
" 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
<<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.
"  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.

User generated image
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.
<<"  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.
Try this:
Options
Current Database
Application Options
    Picture Property Storage Format
              Select Preserve Source image format
<<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.
TIFF ... big DIFF ... ha ha ... for sure
Avatar of jdana

ASKER

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!
"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
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...
jdana,
Care to show us the contents of this file?
User generated image
Jeff,

That image was a screenshot originally posted by Joe, not jdana.
Oh.
;-)
LOL