Microsoft Access (MSACCESS) is well known for it’s All-American ability to become obese without a moments hesitation. I had a accdb file that was around six times larger than I was expecting it to be.
The usual solution is a compact and repair.
In the old days before Access 2007, you would also look for embedded images and change them to runtime linked images because Access would store all images as uncompressed Device Independant Bitmaps.
Next would be the decompile, a secret and arcane ritual involving either a command window or the accursedly tiny Target box in a shortcut properties window.
Last would be the create-a-new-database-and-import-all-the-old-objects ploy and let Access start again.
However, my friend discovered another step you may want to check before that last dramatic step. Open a form in Design View, switch to the Format tab and see if you have any images in the Background Image dropdown. This is a history of images embedded in your database.
But it’s not just a history. It’s also a copy of every image that has been embedded in your database. Ever.
Now if you’re thinking that you never embed images in your database, well, Access has an unpredictable habit of occasionally saving your runtime linked images when you save a module and you have a form open with runtime linked images. And these become embedded until you notice and set the Picture property back to none. And no matter how fast you noticed, Access may now have a copy of that image.
Also, this contains every image you selected from a Browse button on a picture control or that Background Image selector.
The reason they are there is that Access introduced a new PictureType: Shared. This is the default when you use the Insert Image button instead of the Image control button. When you use the Image control button, the PictureType is set to Embedded.
This is entirely reasonable and a nice feature. It means that you can design an interface using shared images and easily update the design by updating the single shared image. Also, if you are using the image on more than one form, the image is only stored once.
So where are they?
- Go to Options, Current Database, Navigation Options and tick Show Hidden Objects and Show System Objects.
- Open the table MSysResources and you will see all the images listed with a type of img. The attachment is the original full size image file.
Access itself never deletes these images.
- After making a backup of my database, I deleted all the records with Type img. (There are other items in here as well, so be careful not to delete them.) After that, my obese 157MB database became a svelte 14MB.
- If you’ve only got a couple, you can go to Form Design, drop down Insert Image and right-click an existing image to delete or rename it. If it was being used as a Shared PictureType, those Image controls will no longer contain an image.
If you have a Microsoft Access 2007 onward database that is much larger than you would expect it to be and none of the usual recommendations do anything to shrink it, this may be the cause.