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


Pictures in Access Forms & Reports -
OLE Object Photo & Image Problems & Solutions

  
Sample Newsgroup Questions 
OLE Object displays icon not content
Inserting Pictures in Access 2003
Images in Access 2003 forms
Access 2003 Photo Errors
Display image on form in Access 2003
Insert images in tables/forms Access 2003
OLE shows up as icon
Bound OLE Object Not Displayed
Showing photos in a form
Display content not icon in bound control
JPG picture not viewing in bound frame
Need pic, not icon, for OLE object
Picture shows icon in Bound Object Frame
Photo showing as an icon, not content
OLE Picture in Access Form
Using JPEG Images in Bound Object Fields
Images displayed as icons


Problems working with pictures, photos and images in MS Access Forms and Reports are common, and many of them relate to using OLE Embedding and Linking. This article examines these techniques, and identifies common problems, workarounds, solutions & alternatives. We'll also look at some new Access 2003/Office 2003 issues, and consider where Microsoft may be heading with this.

OLE Embedding
OLE Embedding in Access
OLE Embedding Images in Access
Issues OLE Embedding with images
 Access/Office 2003 Issues
Summary & Conclusions
Solutions & Alternatives
Resources & Links
Imaging for Access that's Easy, Efficient & Fast
  • NO OLE Bloat
  • NO App Dependencies
  • NO Complex Coding
  • NO Performance Penalty
  •  DBPix logo
    Read More

    OLE Embedding

    'OLE Embedding' is a technique that allows one application to store its data inside another applications data-file. A typical example is placing an Excel Chart in a Word document - the Excel data is contained within the Word document/file, and Word invokes/activates Excel to display or edit the chart.

    OLE Embedding in Access

    Access supports OLE Embedding and Linking, and this can be used to store Word documents, images, pictures, photos and other files or 'objects' in records in a database table. Access implements OLE Embedding in Forms and Reports with the 'Bound Object Frame'.  You can also use the 'Insert-Object' menu command, drag-and-drop, and copy/paste. 

    Any binary field can be used for back-end data storage, including the 'OLE Object' field-type in Access, 'Image' in SQL Server, or equivalents in Oracle, MySQL etc.  Note that these fields can all also store raw-binary/blob data (e.g. any type of file) without using OLE Embedding, OLE Linking or any other OLE mechanism (hence the name 'OLE Object' in Access is potentially misleading).  In fact, raw-binary storage avoids many, if not all, of the problems discussed here (see 'Conclusions' for more info).

    OLE Embedding with Images in Access

    When a user inserts a file using OLE Embedding (eg a jpeg picture), Access attempts to invoke the application that is registered for that file-type/extension, and have it store the 'Object' using OLE mechanisms.  Only a few graphics applications support this functionality, and if the registered application does not, a default 'Package' is created (which cannot be displayed directly on a form/report).

    In order to display the picture, Access attempts to invoke the application that was used to initially store the object.  If this application is not correctly installed & registered on the user's system, the image will display as an icon.

    Issues with OLE Embedding with images

    Configuration:
    Ensuring that all users have the same application (the particular 'OLE Server' app) installed and registered on their systems is one of the most common causes of problems with OLE Embedding. Problems can arise when users install other graphics software, or upgrade Access, Office or Windows (perhaps forgetting to install the relevant optional components), and Office/Access 2003 introduces a new variation, more below.  Maintaining this with any more than a very small user-base is difficult, at best.

    Storage Overhead ('bloat').
    MS Photo Editor (the typical OLE Server for several common image formats) stores the image uncompressed, and a second uncompressed 'preview' image may also be stored.Consequently, for JPEG pictures, OLE Embedded storage can require up to 200 *times* the size of the original image file. This is a frequent cause of applications hitting the 2GB Access file-size limit, when perhaps only a few hundred MB of image files have been added. 

    Interoperability.
    Since the data is stored in the private format of the OLE Server application, and further wrapped in OLE headers, it can be difficult or impossible to use the data with other applications & tools, such as Visual Basic, IIS/ASP etc, or even to extract it directly back to a file.

    Loss of Metadata (eg EXIF, IPTC & Color Profiles in JPEG). 
    Due to the uncompressed storage format all metadata in the original image may be discarded.

    Loss of compression/Lossy extraction (JPEG).
    Since the original compressed data has been discarded, extracting the data back to a file usually requires re-compressing/re-encoding the image.  In the case of JPEG this is 'lossy', ie the picture quality will degrade slightly (how much depends on the JPEG 'Quality' setting).

    Access 2003/Office 2003 Issues

    MS Photo Editor is commonly used as the OLE Server application in Access picture applications. Photo Editor was an Office component prior to Office 2003, however in Access/Office 2003 MS Photo Editor has been replaced, and Office no longer includes an OLE server for images.  In fact, reports indicate that installing Office 2003 may actually remove MS Photo Editor if it is already installed.

    This is behind many of the problems reported recently, such as some of those at the start of this article. Anyone experiencing this can usually get the application running again by passing the Office XP CD around, and installing Photo Editor wherever necessary, before hopefully thinking about a more long-term solution.

    Summary & Conclusions

    OLE Embedding/Linking can offer a useful solution in certain circumstances, but is best suited for 'private' file/document formats, where the associated application is almost always the same, and it is reasonable to expect that the application is correctly installed and registered in order to view/edit the document/data (such as Word .doc and Excel .xls). 

    In the case of more open/general formats, like JPEG, there are a wide range of applications that can be installed & registered for this file-type, and only a few implement the necessary OLE Server functionality for OLE Embedding to work.

    Microsoft ceased using OLE Embedding for images in 'Northwind' (the reference sample for Access) several Office versions ago.  Office/Access 2003 no longer includes an OLE Server application for images (and may remove existing ones), and Microsoft has indicated that this is due to reasons including those above. As applications are migrated to Access/Office 2003 (and presumably beyond) maintaining these applications will become increasingly difficult.

    Solutions & Alternatives

    The usual recommendation is to take the pictures out of the database and work with external files. This is valid, but in fact Access is also very capable of working with images stored in tables without any of the problems above, provided they are stored as raw-binary (blobs) and usual Access design-rules, guidelines & limitations are observed. 

    Raw-binary/blob picture storage can be implemented in Access code. This typically involves extracting the image to a temporary file then displaying it with the built-in Access Image control. Note, however, that this still has caveats and dependencies (eg Office Graphics Filters, 'Importing' dialog supression, 'Scroll-too-quick' crash), which apply equally to code-only external-files solutions. 

    Alternatively, commercial components such as DBPix can bind directly to binary fields, requiring no code and offering a range of useful extra functionality (as well as working with external files), simplifying development, deployment/configuration, and avoiding all of the OLE issues above.



    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
    Efficient Image Storage in MS Access - Store Photos, Graphics & Pictures in Access
    Access Image Database Techniques

    MS KB 832508: Images that are stored in OLE object fields do not appear correctly
    MS KB 177587: ACC: OLE Object Inserted into Object Frame Displayed as Icon
    MS KB 817095: Photo Editor is removed when you install Office 2003
    MS Office Online: What happened to Photo Editor?
    MS KB 103257: ACC: Reading, Storing, & Writing Binary Large Objects (BLOBs)
    MS KB 210486: ACC2000: Reading, Storing, and Writing Binary Large Objects (BLOBs)

    Tony Toews Access Image Handling Page
    The Access Web
    Larry Linsons Imaging Samples
    Stephen Lebans site