Access file size increased in size by 50%

[Mike99]

I have MS Access 2010 & have a database that has been about 45 MB in size for 2 years. About a month ago I noticed it now is 67 MB. I compact & repair every time I save it which is twice a month. I've gone back & did a compact & repair on the 67 MB file but it does not change.

I must have looked at & changed something without realizing it. The actual number of records appears to be correct. How can examine and/or compare tables or other internals between the two sizes? I've seen a program called AccdbMerge come up on a few Google hits & was wondering if anyone ever used it?

And & all suggestions would be appreciated.

 

[bbinnard]

The only reason I've seen for a database to get very large is when there is a picture (JPG file) used in a form. Apparently Access does not do a very good job with storing this type of data. Perhaps your database has an image stored as a data item in a table (or some other place.) Other than that...no idea why you should see such a large size.

Alternately, you can create a new empty database and then copy each object from your current database to the new one, one at a time, and check the size of the new database after each copy. That will at least identify which object is causing the problem.

 

[Mike99]

Thank you for the reply.

Short story - this database was handed down to me & I don’t know a lot about its internal workings.

At about the time the size increased I “printed” a report to a PDF file. I used the file but don’t recall saving it. Perhaps it was accidentally saved to Access.

I just now Googled how to find objects & it showed how to view tables. So I looked through several of the Tables & found a duplicate copy of a form letter which appears in the larger database. IIRC this was the one I was working with & is just a single page. I right clicked on Properties but it does not indicate if this a PDF or .doc file, nor does it indicate the size. I doubt it’s a 20 MB pdf, but how can I tell? I tried to copy/paste it to another folder but cannot. I can click on Copy, but when I go to Paste it the Paste function is grayed out.

I experimented on a copy of the database & deleted the duplicate form letter, and then compacted & repaired. The database size went back down to 45 MB so I’m presuming that was the problem.

But now I’m curious about what I deleted. It must have been more than just a single page form letter & I‘m guessing had some data associated with it. How can I see what it really is?

 

[bbinnard]

Look at the form letter in its final, printed format. If it has graphics in it - such as a logo or any sort of image, then that is the issue. Image data does not have to be stored in a table for Access to have problems - it can be part of a form or report and still have the same effect.

 

[Mike99]

Yes it does have a graphic logo on it & an outlined area where someone fills in information. It's just difficult to think that this form letter would be over 20 MB if size, but obviously it is.

Thanks again for pointing me in the right direction.

 

[Kaktussoft]

in a copy of database: open the problem from, delete the picture and save form. compact database afterwards. Is it smaller now?

 

[bbinnard]

Yup. believe it or not this bug has been in Access forever. There is a way around it, but it might not work for your situation:

The graphic can either be embedded in the Access object (which is what your situation is) or it can be linked to. In the latter case the graphic is NOT in the object; instead there is a link to where it is and when Access displays the object it pulls it from the link. This solves the problem of the huge database size, but it required the linked-to graphic to be on the hard drive from which Access is running. Access cannot pull the object from a link to a remote computer; the link has to point to a file on the local hard drive.

Depending on how your users are situated this can be impossible to make happen. But if you can control what's on their hard drives it is a way to get around the problem.

 

[Mike99]

in a copy of database: open the problem from, delete the picture and save form. compact database afterwards. Is it smaller now?

Yes, please see message #3.