Solved

MS Access 2003 - Unable to shrink file

Posted on 2012-03-21
20
395 Views
Last Modified: 2012-03-22
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.
0
Comment
Question by:jdana
  • 6
  • 5
  • 3
  • +4
20 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 80 total points
ID: 37748474
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 80 total points
ID: 37748486
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
 
LVL 77

Expert Comment

by:peter57r
ID: 37748508
Agree..it's bound to be images.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 80 total points
ID: 37748555
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37748564
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
 
LVL 75
ID: 37748575
" 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
 
LVL 57
ID: 37748580
<<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
 
LVL 75
ID: 37748630
"  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
 
LVL 30

Expert Comment

by:hnasr
ID: 37748763
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
 
LVL 57
ID: 37748781
<<"  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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 30

Expert Comment

by:hnasr
ID: 37748886
Try this:
Options
Current Database
Application Options
    Picture Property Storage Format
              Select Preserve Source image format
0
 
LVL 57
ID: 37749060
<<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
 
LVL 75
ID: 37749312
TIFF ... big DIFF ... ha ha ... for sure
0
 

Author Closing Comment

by:jdana
ID: 37752072
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
 
LVL 75
ID: 37753404
"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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37753926
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37753953
jdana,
Care to show us the contents of this file?
WTH?
0
 
LVL 75
ID: 37753972
Jeff ... ?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37754021
Jeff,

That image was a screenshot originally posted by Joe, not jdana.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754056
Oh.
;-)
LOL
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Outlook Free & Paid Tools
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now