Long Binary Data

Bookmark this page:

"Long Binary Data" is the text that Access displays in a binary field when the field contains a 'blob' (a copy of an image file, for example). DBPix stores images as blobs (and displays images that are stored as blobs) simply by binding a DBPix control to the field.

Long Binary Data (Blobs) vs OLE Embedding and Linking

Access provides 2 ways to store binary data such as image and document files:

OLE Mechanisms (Linking and Embedding).
Blobs.

Note that the binary field-type in Access, called "OLE Object", is simply a binary/blob field. The OLE functionality is actually part of the Access front-end, and can be used with binary fields in other databases such as SQL Server, Oracle, MySQL etc. The name "OLE Object" is therefore somewhat misleading.

Long Binary Data (Blobs)

Long Binary Data in an Access Table
Storing a file (such as an image) as a blob involves placing an exact copy of the file in the field. The data can be directly extracted back to the filesystem resulting in an identical copy of the original file. Blob storage is therefore efficient and portable. For example, in a web application you can stream the contents of a blob field directly to a browser in response to an image request.

Note that Access is widely thought to be very inefficient when storing any type of binary data, particularly images. This is not the case. When used to store blobs the only overhead is a small amount due to allocation granularity, comparible to the allocation overhead inherent in most filesystems. Using OLE Embedding or Linking, however, can cause huge overheads when storing compressed image formats such as JPEG.

OLE Embedding and Linking

OLE Embedding in an Access Table
Long Binary Data
OLE Embedding/Linking occurs when using a Bound Object Frame, the "Insert Object" menu, or when pasting a copied image into a field. Access invokes whatever application is registered as an "OLE Server" for the particular file-extension, and has that application save the image/document/file into a "Structured Storage Stream". The main drawbacks of this approach are as follows:

Different users may have different applications registered on their systems for the same file-types. An image inserted on one user's system may not be openable on another user's system. Installing new software can easily rearrange the file registrations, making OLE Embedding difficult to maintain on more than a few systems.
The OLE Server application can choose the format in which the image or document is saved. In many cases when using compressed images the image will be saved as an uncompressed bitmap. Additionally, up to 2 "Preview" images may be stored, also as uncompressed bitmaps. In the case of a JPEG image the database can grow by up to 200 *times* the size of the original JPEG file that was inserted. When displaying such images in a form or report the sheer size of the data can lead to a huge delay when simply scrolling from one record to the next.
Since the data is contained in a Structured Storage wrapper, with variable length headers preceding the actual image data, the data can't be extracted directly back to a file, or streamed to a web browser, or used in other applications. In the case of JPEG images, getting a JPEG file out of the database requires re-compressing the image causing a cumulative loss of quality due to Generational Loss.

Packages in an Access Table

A special case of OLE Embedding occurs when files are inserted as a "Package". A Package is created when no application is registered for the file-type, or if the user specifically selects a Package. A Package contains a copy of the original file, however the content can't be displayed on a form or report so it is if little use for most image requirements. Unlike blob storage the data in a package is still wrapped in proprietary headers, so it can't be used directly in other applications, e.g. web apps or VB apps.

Working With Long Binary Data - Using DBPix

DBPix binds directly to a binary field to store and display images, providing the easiest and most efficient way to work with long binary data/blobs. This avoids the issues present in OLE approaches, and offers a range of enhanced functionality for both developers and end-users. It also performs time-consuming image decoding in the background, so you can scroll quickly through records without your forms locking during decoding.

Working With Long Binary Data - Using Code

The Variant data-type can hold the contents of a blob field, for example:

Dim varImage As Variant
varImage = [ImageField]

You can load data from a file into a blob field, or extract a blob back to a file, using a few lines of Access VBA code.

Working With Long Binary Data - Copying and Pasting Fields

You can copy and paste the Long Binary Data contents of a field as follows:

Open the table.
Select the field by clicking the field, then shift-clicking the same field. Note that the text becomes inverted.
Copy (Ctrl+C, or use the menu).
Select the destination field (click, then shift+click).
Paste (Ctrl+V, or use the menu).

Note that blob data can't be opened directly from within a table view; you can't double-click a blob like you can with OLE Embedded or linked data. However, since end-users shouldn't interact with data directly through tables this is usually not a drawback.

Working With Long Binary Data - Queries and Joins

Tables containing Long Binary Data can be queried just like other fields, however, note that updating a blob field using an UPDATE query is usually not possible, since the binary values contained in binary data can't be represented directly as text, and the limited length of an SQL statement may not be long enough in most cases. Similarly, filtering records on blob fields using WHERE, LIKE etc is usually not possible.

Bookmark this page: