Tag Archives: Access OLE Export

Why is SQL Image Viewer not displaying details of my blobs

If you are using a third-party application to upload images and/or files into your database, and SQL Image Viewer cannot identify the image or file type, then there’s a high probability that your application has modified the data.

Examples of such applications include the MAZE School Information System and the Financial Edge system by Blackbaud.  We had a user who had the following data stored in their Financial Edge database.  In SQL Image Viewer, the following is displayed:

SQL Image Viewer is unable to identify the data that’s stored in the fields (they’re actually PDF files).  If we look at the data using the SQL Image Viewer hex viewer:

we can see that the OLE wrapper (or the original source data) begins at offset 32.  This means that Financial Edge has added 32 bytes of data to the beginning of the original file, which is why SQL Image Viewer does not recognize the file format.

To identify and extract the data correctly, we need to skip the first 32 bytes, so that we only retrieve the original source file.  In SQL Server, we can use the following syntax:

Now, SQL Image Viewer is able to identify the file type correctly.

Ok, admittedly not everybody knows what an OLE wrapper looks like, or any of the other file headers, which is why if SQL Image Viewer cannot identify your blobs, please send us a couple of samples to analyze.  We need the data exactly as stored in your database, so to extract the data, please perform the following steps and send us the resulting files.

Select the column containing the unidentified blob data.

Right click the mouse button to bring up the context menu, and select the ‘Save item’ option.

Enter a file name, save the blob data, and send the file to us at support@yohz.com.

The same issue applies to Access OLE Export and SQL Blob Export too.  If these products cannot identify the file type because the original files have an additional header, they will be exported with a .bin extension.  Please send us a couple of those .bin files to analyze, or you can also use SQL Image Viewer to retrieve the data and follow the steps above to send us the samples.

Exporting and extracting images and files from Microsoft Access databases

→ This article refers to SQL Blob Viewer, which has now been renamed to SQL Image Viewer.  The techniques described in this blog is still applicable, as the functionality of the product remains the same.  Only the name has changed.

Using OLE Object column types, there are 3 ways you can store images and files in a Microsoft Access database, or in a database (e.g. SQL Server) used by a Microsoft Access front-end.

  • embedding

Embedding involves creating the image/file directly using the associated OLE Server application.  For example, if I chose to embed a bitmap image, Access will open Paint for me to create my bitmap image, and save that image into that field.

  • insert from a file

If you already have the image/file you want to store in your database, you can insert the file into your Access database directly using the ‘Create from File’ option.  Access then copies the file into the field.

  • link to a file

Similar to the above, but this time, the image/file is not stored in the database.  Access just creates a link to the external file, much like a hyperlink in your browser.

If you store your images/files using the first 2 methods, then Microsoft Access adds additional data to your image/file, so that it knows which application to use to open that particular image/file.  This means that if you extracted the data from your database, it will appear different from your original file.

For example, let’s insert an image into an OLE Object field.  The image file is 18.7 Kb in size.

Once inserted into the Access database, the size has increased to 19.192 Kb.

The increase is due to the additional data added by Access.  If you now extract the data as is from the database, it cannot be opened by Paint because of the additional data.  The PNG data is preceded by OLE headers.

This is a common issue faced by Microsoft Access users – when they want to extract images and files from OLE Object fields to their original format, there isn’t a way they can do this easily.  Every image/file has been modified by Access.  You have to open each image/file in Access, then save the item to disk manually.

This is the reason we created Access OLE Export.  In just 4 simple steps, Access OLE Export will extract and export your embedded images and files to disk, stripping out the additional data added by Access.  This means that the exported images and files can be opened ‘normally’, using the appropriate application.

If you require more control over what is exported, SQL Blob Viewer is another option.  SQL Blob Viewer allows you to inspect your data before exporting them, but requires that you be able to write SQL commands.

There may be situations where both products are unable to identify embedded images correctly.  This may be because the registered OLE Server for that content type is not yet supported.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Exporting images from OLE Object fields

→ This article refers to SQL Blob Viewer, which has now been renamed to SQL Image Viewer.  The techniques described in this blog is still applicable, as the functionality of the product remains the same.  Only the name has changed.

If you use OLE Object fields in your Access or SQL Server databases to store images, you know it’s convenient to just be able to click on the item and have Windows open the image using the registered viewer on your computer.  However, it becomes difficult to extract the images because Access adds additional OLE data to the stored images, thus changing their original form.

Take a Windows bitmap image,

and store it in an OLE Object field in a SQL Server linked-table.

The original file was 2,002,182 bytes in size, but has increased to 2,002,298 bytes when stored in the OLE Object field.  Access has added 116 bytes to the image.

and that the data stored in the field is different from the original bitmap file.

How can we then export this image?  One way is to open the table using Access, then double click on the data, and Access should then open the stored image in the registered OLE server for that image type, in our example Microsoft Paint.

However, this is a tedious process if we need to export a lot of images.  SQL Blob Viewer and Access OLE Export are two applications that we developed to easily export images and other data stored in OLE Object fields.  Exporting the items using SQL Blob Viewer or Access OLE Export is just a matter of writing the appropriate SQL query or selecting the right table.  SQL Blob Viewer is for users who are comfortable writing queries and want more control over how images are exported, while Access OLE Export is for users who just want to be able to select a table or write a simple query and export the images as is.

SQL Blob Viewer also displays a preview of the image when we query the table:

So if you have a ton of images that you need to export from OLE Object fields, give SQL Blob Viewer or Access OLE Export a try.

There may be situations where both products are unable to identify embedded images correctly.  This may be because the registered OLE Server for that content type is not yet supported.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Extracting files and data from OLE Object fields

OLE Object fields are commonly used by applications using Access as the back-end database to store files and common document formats.  It is convenient because Windows handles how the files or documents are opened, modified, and saved via OLE servers.  However, it is difficult to extract the data from those fields because of the additional OLE information embedded together with your data.

For example, let’s create a table in Access, and store a simple Excel workbook, both containing the same data.

We create the first record by directly embedding an Excel workbook.

For the second record, we simply attach an existing Excel workbook.

In Access, we can open both workbooks easily simply by double-clicking on them.  This is the OLE servers at work.

Now if we take a look at the size of the 2 records using SQL Blob Viewer, we can see that the size of the embedded workbook is larger than the attached file.  The size of the attached file record is also larger than the original file size, because Access needs to add additional data to the file.

If you try to export the data as is, you will not be able to open the exported files, because the format itself is not Excel-compliant.  Both records in the OLE Object fields have had additional OLE wrappers added to them.

The usual way to extract the content is to open each item individually in Excel, and save them to files.  This is a tedious process if you have a lot of records you need to extract.

We have 2 products, SQL Blob Viewer and Access OLE Export, that can remove the OLE wrappers for data stored in OLE Object fields, and export them to disk.

SQL Blob Viewer is for technical users who are comfortable writing SQL queries to retrieve the required data, and Access OLE Export is for less technically inclined users who just want to be able to select a table and export their data quickly.  Both products can identify embedded Office document content, images, PDF content, Open Document content, and other common binary types.  Both products can also extract data from OLE Object fields used in other database engines like SQL Server, MySQL, Oracle, PostgreSQL, Firebird, SQLite, and ODBC data sources.

There may be situations where both products are unable to identify embedded content correctly.  This may be because the registered OLE Server for that content type is not yet supported.  For e.g. PDF files can have different OLE servers like Adobe Acrobat, Foxit PDF Reader. Nitro, etc.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Why are both 32-bit and 64-bit versions of Access OLE Export installed

When you install Access OLE Export, you find that both the 32-bit and 64-bit versions are installed.

This is because when you access an Access database, you will need to use the 32-bit version of Access OLE Export if you have installed the 32-bit version of Microsoft Office on your machine, or if you have the 32-bit version of the Access Database Engine 2010 Redistributable installed.  If you have installed the 64-bit version of Office, or the 64-bit version of the Access Database Engine 2010 Redistributable, you will then need to use the 64-bit version of Access OLE Export.

The Access Database Engine Redistributable itself needs to match the version of Office that’s installed.  For example, if you have the 32-bit version of Office installed, and you try to install the 64-bit version of the Engine, you are prevented from doing so.

If you selected the wrong version of Access OLE Export to access your Access database, the following message is displayed.

Simply use the other version then.  Alternatively, you can use the Access OLE Export (Access mode) shortcut which will start the appropriate version of Access OLE Export depending on which version of Office is installed on your machine.