Home
DBPix
Download
Order
Testimonials
Support
Tutorials
Samples
KnowledgeBase
Links
Revision History
Documentation
Search
Contact
Site Map

Graph: DBPix image storage vs OLE Embedding and Linking in Microsoft Access


Efficient Image Storage in MS Access -
Store Photos, Graphics & Pictures in Access

    
DBPix Image Control 
DBPix Developers Kit
Download V2.0
For Windows XP, 2000,
NT, ME, 98 & 95


Buy Now!

DBPix Resources
Samples
Support
About DBPix


Introduction - Example - Other Issues - Solution - Implementation - Resources 

Any suggestion of storing images in MS Access (such as jpeg photos & other graphics/pictures) usually meets with a response such as:
"Don't ! Access is terribly inefficient at storing images".

This is not the case. The Access 'OLE Object' field can store any binary data, including images, with negligible overhead compared to the file-system.

This perceived inefficiency relates to the specific cases of OLE-Embedding and OLE-Linking. These techniques can cause a significant storage overhead when used for images, particularly with photos or scanned pictures in jpeg format. It is not uncommon for an Access database to hit the size-limit for the mdb file (1 or 2 GB, depending on the version) after as few as 100 jpg images, when in fact the database could store many thousands of pictures.

Imaging for Access that's Easy, Efficient & Fast
  • NO OLE Bloat
  • NO App Dependencies
  • NO Complex Coding
  • NO Performance Penalty
  •  DBPix logo
    Read More

    Example

    Empty database size = 112 KB (Access 2000).
    OLE Server for jpg: Microsoft Photo Editor 3.0 (default/typical configuration).


    Example 1 - jpeg photo 2240 x 1680, low compression - file size = 2357 KB (2.30 MB)

    Size of Database + 1 Image:
    OLE Embedded33,572 KB(32.79 MB)1319 % overhead
    OLE Linked22,392 KB(21.87 MB)845 % overhead
    Raw Binary (Blob)2,488 KB(2.43 MB)1 % overhead

    Example 2 - jpeg photo 2240 x 1680, high compression - file size = 183 KB (0.18 MB)

    Size of Database + 1 Image:
    OLE Embedded33,572 KB(32.79 MB)18222 % overhead
    OLE Linked22,392 KB(21.87 MB)12100 % overhead
    Raw Binary (Blob)300 KB(0.29 MB)3 % overhead


    OLE Embedding and Linking clearly cause a very significant storage overhead, and the more highly compressed the original image, the higher the overhead. In the worst case the database grew by over 180 *times* the size of the image file that was inserted. The raw-binary overhead, however, is negligible.

    Other OLE Embedding Issues

    Because the data is stored in the private format of the OLE Server application, we cannot simply extract it to a file or stream it to a web browser etc. In most cases the only way to 'extract' the image involves obtaining an uncompressed bitmap version of the image, and then re-compressing it to a file of the original format. In the case of JPEG, the lossy nature of JPEG compression will cause a further reduction of image quality, and any metadata will be lost (e.g. EXIF digital camera info or jpeg IPTC info).

    The relevant 'OLE Server' application must be installed and correctly registered on any machine that will use the database. This can be difficult to configure and maintain and is a common source of problems, for example if a user installs new software which 'takes-over' the relevant file registration, (e.g. for ".jpg"), or when new versions of Windows or Office/Access are installed.

    Update: Office 2003 no longer includes an OLE Server application for various image formats (including jpeg) and may remove/replace existing OLE Server applications, usually causing existing images to display as icons, and new ones to be inserted as a package.

    The Solution

    Storing the images as raw-binary (BLOB) data avoids all of these problems. By storing the image in it's original compressed format the overhead is avoided, and the image can easily be extracted directly back to a jpg file (without loss), or streamed to a web browser, used with other development tools such as VB, etc. Metadata is preserved, and the dependency on an OLE Server application is removed.

    Implementing Raw Binary Storage

    Images can be loaded from files into an OLE Object/binary field (and extracted directly back to files) by using the ADO GetChunk and AppendChunk methods in VBA or other languages. In this way it is possible to display an image by extracting it to a temporary file and then using conventional file-based approaches, such as using the Access Image Control (though there are caveats with this control).

    A far easier and more powerful solution is provided by DBPix, which can bind directly to binary fields (including an Access Form or Report dataset, or an ADODC in VB) and provides most commonly required functionality without a single line of code. As well as ease-of-use and efficiency, DBPix overcomes numerous potential problems with Access image handling and provides enhanced functionality, such as asynchronous decoding, EXIF viewing, resampling & thumbnailing, Zoom, pan and alignment control, rotation (including lossless jpeg rotation) and more.


    Imaging for Access that's Easy, Efficient & Fast
  • NO OLE Bloat
  • NO App Dependencies
  • NO Complex Coding
  • NO Performance Penalty
  •  DBPix logo
    Read More

    Resources & Links

    DBPix Image Control
    DBPix Samples
    Pictures in Access Forms & Reports - Access 2003 OLE Object Photo Image & Picture Problems
    Access Image Database Techniques

    MS KB 123151: ACC: Why OLE Objects Cause Databases to Grow
    MS KB 103257: ACC: Reading, Storing, & Writing Binary Large Objects (BLOBs)
    MS KB 210486: ACC2000: Reading, Storing, and Writing Binary Large Objects (BLOBs)