Microsoft Access and Image Handling

(Updated 2007-11-26)

Microsoft Access is very inefficient at storing images in tables.  A 50 kb jpg file can actually consume 1 Mb in an MDB.  Access is storing a thumbnail copy of the image but in bitmap (bmp) format. ACC: Why OLE Objects Cause Databases to Grow - 123151.  Thus you really, really want to investigate some alternatives.  In Access's defence bmp format files are uncompressed and thus require no CPU to decompress them.  This was very important back in the days of 80386 processors.

If you really, really need to keep the images in the MDB consider using VBA code and BLOBs, binary large objects, thus avoiding creating the bmp thumbnail copy.  See AppendChunk and GetChunk in the online help.

Downloadable sample VB code

Tips and Tricks

What I also do is put incoming images in an "import" directory, quite often the default directory chosen by the camera or other graphical input device, and move them to another directory when they have been matched. This way you know what photos you have left to process.  I also give the users an option of deleting the photo's.

I then display this list of photos in a list box in the top half of the form using a call back function.  Whenever the user clicks on a photo I display the photo in the bottom half of the form and unlock some command buttons and fields.

Alternatively if the image is fairly large, that is larger than a photograph, or other files involved such as PDFs, I then use the ShellExecute API call to run whatever viewer is associated with that file extension.

Depending on the circumstance I will also rename the file. I might rename the file to something useful such as TOEW0001.jpg. In other cases where each photo had a caption and other info associated with it I just name the photo the same as the autonumber physical key of the caption with leading zeros, i.e. 00000123.jpg.   Or if the file name supplied is meaningful, ie tony_toews.jpg, then I might parse out that info and use it as part of the data in my record.

Problems with Reports

Stephen Lebans recently informed us that using the OnPrint event rather than the OnFormat event has been suggested by Microsoft's tech support to alleviate running out of memory or system resources when printing images. 

Stephen Lebans has stated that Microsoft has determined that the long-time problem of leaking memory in reports using image controls does not occur if the image files are .BMP.

Performance

BMPs load a lot faster than other formats as they are not compressed especially on older slower systems.  This then becomes a trade-off between increased file size and performance.  This may no longer be true on 2 Ghz CPU systems with ATA 133 hard drives.

Loading Image dialogue

Getting tired of seeing that Loading Image dialogue flicker?  Getting errors by users who click on things before this is finished displaying? Try creating/changing the following registry key as per the Access WEB at API: Suppress the "Loading Image" dialog

As always make a backup of your registry and make changes to registry entries at your own risk. 

Joe Fallon, fellow MVP, states "I use the Tab control to "hide" the image on a different "page". I also move the code to the OnGotFocus event of the ImagePath text box which is on the next page of the Tab control. This way, the only time the picture loads is when the user clicks the tab to see it".

Microsoft stuff

(Thanks for Frank Miller of Microsoft for supplying the original version of the following info and links.)

For Access to be able to store or display any images, it relies on having a valid OLE Image Server installed on the local machine. So, if you have a product such as Photo Editor or Paint Shop Pro installed, your limitation is based on what is available in the Image application that you are using by way of the operating system's file association for a specific image type.

For more information on how Access stores and displays images

Q294255 ACC2000: Err Msg When You Attempt to Display Images on a Form
Q158941: How to Load OLE Objects from a Folder into a Table
Q198466 ACC2000: How to Load OLE Objects from a Folder into a Table
Q114214: How to Programmatically Embed or Link an Object in a Form
Q210100 ACC2000: How to Display an Image from a Folder in a Form
Q177587 ACC: OLE Object Inserted into Object Frame Displayed as Icon

In some cases, linked images can use graphics filters that may or may not be installed on your system to display an image. Because Word install most of the Office Graphics Filters, the following articles may also be of Interest:

Q210396 OFF2000: Descriptions, Limitations of Shipped Graphics Filters
Q212271 WD2000: Graphics Filters Installed in an "Install Now" Setup
Q212265 WD: Additional Converters/Filters Available in Converter Pack
Q235928 WD2000: Supported File and Graphics Formats

Kodak Imaging Controls which come with various versions of Windows up to and including Windows 2000.

I wouldn't bother trying to work with these controls given that they don't come with Windows XP and, last time I found them on a website, they wanted you to purchase them for each user.

A set of graphic handling controls which comes with Win NT4, Win 95, 98 and Windows 2000 are the Kodak Imaging controls (formerly the Wang Imaging controls).  These controls do not come with Windows XP.  They are an optional install which can be verified by looking for Imaging under Accessories. 

These controls cannot be redistributed as per the KB article INFO: Cannot Distribute Eastman or Wang Imaging Controls [Q190036].  However this article no longer exists.  The product is now know as eiStream Imaging for Windows.  No, that website redirects to Global360.com (I used to have a direct link to the product but these people insist on changing URLs so you're going to have to search for them.  And with all those name changes who really cares about such a product.) It would also appear that you must purchase licenses for each user of your product.  Thus you will want to consider purchasing another product which does allow royalty free redistribution.

I have no idea what versions of these controls come with which version of Windows and no idea on their functionality. You could try searching at support.microsoft.com or msdn.microsoft.com. Information on these controls is rather sparse. 

 

[ AccessMain ]