Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access 2003 - Unable to shrink file

Posted on 2012-03-21
20
Medium Priority
?
467 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 320 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 320 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 320 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 58
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 31

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 58
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
 
LVL 31

Expert Comment

by:hnasr
ID: 37748886
Try this:
Options
Current Database
Application Options
    Picture Property Storage Format
              Select Preserve Source image format
0
 
LVL 58
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

783 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