Why has my form grown to over 100MB?

[dwalker]

I have a form which imports jpg files and displays them as thumbnails in image controls. 34 images are displayed at any one time, each jpg is about 100k and none of the jpg's are embedded - each is linked and referred to by its path that is stored in a table.

The form's On Current event runs code to find the path for each image control's image and sets the Picture property to that path. Straight forward so far.

After opening the form maybe 20 or so times my database has quickly grown to over 200MB. Remembering that no images are embedded in the database I assumed it just needed compacting but this only reduced it to about 100MB. There are no other objects in the database - one 40-record table and one form with 34 image controls.

I imported the form into an empty database and that new database grew to 100MB. The system table called MSysAccessObjects has over 25,000 records so this is clearly where all the size comes from.

I'm guessing that as a jpg is imported it's probably converted to a bitmap, then all the image data is stored in the aforementioned system table like a type of cache - just a guess but there must be a reason all that data is there.

So, my question is, what can I do to prevent the database getting so big? Can I safely delete data from the MSysAccessObjects system table?

 

[Newposter]

Let me know if you find out. I've had the same trouble in the past. Linked .jpgs, but the database treats it as if they were embedded. My guess is another M$ bug. Newposter
"Good judgment comes from experience. Experience comes from bad judgment."

 

[cruford]

Don't know if it will help but somewhere you could add the code to compact the DB, say when the form is closed or something. I don't know the syntax off hand but a seach on compacting should bring some threads up.

 

[gusbrunston]

[tt]
Hi:

I found I had to compact and repair any Access database on a daily basis to keep a reasonable size. My Options include Compact and Repair On Close. Everytime a user closes his/her front end, the front end compacts and repairs. On a regular basis, everyone logs off for "database maintenance" and we compact and repair the back end.

If not, even without extensive use of images, an Access database will expand (never contract). [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[illuzioner]

Hi dwalker,

A couple of things:

Remember to close all recordsets you may open with

rs.close (where rs is your recordset variable)

Also, I got the following from another website which will probably help in your case (sorry, I don't remember the website -- but the credit is theirs)

Regards,

Lou


My experience has been that you don't need to run the decompile more often than once every month or so to clean things out. You will see the biggest improvement, of course, if you've never done this before on an MDB.

Do a backup before the /decompile. Of course you're doing daily offsite backups so this isn't a problem anyhow, right?!?!?!

Create a shortcut with the following '"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" d:\1access\voluntr\brier\vol_app.mdb /decompile' without the single quotes. As appropriate for your environment of course. Note that if your path to your MDB contains a space it must be enclosed in double quotes just as the MSACCESS.EXE component.

Click on the short cut and, in Access 97, you'll get the message "... has converted the code in ... to your current version of Visual Basic". This message does not appear in Access 2000 or newer. Hold the shift key down when you click on Okay so the MDBs autoexec macro won't execute or the start-up form will not be opened. Now compact the MDB. Then go into any module and click on Debug and Compile and Save All Modules. This last step is particularly important for performance reasons. Then compact again.

For more technical information see the Microsoft Access FAQ page and Michael Kaplan's detailed information.

 

[Newposter]

I think you've all gotten off the track a bit here. The original poster has an issue with file size that has nothing to do with compacting. I have found the same problem he has:

When using OLE links in a table to .jpgs, the file size just explodes and will not compact ever to a reasonable size. To give you an example, I created a simple address book, so it had about 10 fields and 60 records. I linked (NOT embedded) to some .jpgs that were 2K to 6K in size each. The database blew up to over 20 MB in size and wouldn't compact below that.

Even if the .jpgs were all embedded, how is it that linking a total of 250K or so of picture files results in a text-only database growing from 100Kb to 20Mb???? Linking seems to result in embedding, regardless of what the menu says.

It's just more crappy performance from M$. Newposter
"Good judgment comes from experience. Experience comes from bad judgment."

 

[dwalker]

Hi all

Newposter is right, I'm not sure everyone has read my original post fully, but thanks for your thoughts.

The problem surely lies in the MSysAccessObjects system table as this quickly grew to 25,000 records and I'm guessing it's "caching" the image data.

Any more ideas about how to keep the db size under control would be greatly appreciated.

Regards

Damien

 

[markphsd]

DWalker,

I think you answered you post in your last question. Do this experiment: Open your db without access to the jpgs, I think that you'll find that the very first jpegs that you loaded into the form appear (i hope i'm right on that). The db does "cache" the file. So you have jpgs loaded into that form permanently... but only the orignial ones.

I think that the form is so large because you have 38 images. I have a db that won't reduce from 20mbs, but the maximum linked images on my form is 4. I think had I allowed more pictures I'd be running into a larger size like you are. Mark P.

I write code in cuneiform, what about you?