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 involves creating the image/file directly using the associated OLE Server application. For example, if I choose 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. The exported images and files can then be opened ‘normally’, using the appropriate application.
If you require more control over what is exported, SQL Image Viewer is another option. SQL Image Viewer allows you to inspect your data before exporting them, but requires that you be able to write SQL scripts.
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 email@example.com) a sample of the embedded data, and we will add support for that OLE server type.
Download a FREE 14-day trial of Access OLE Export or SQL Image Viewer now to extract embedded objects from your Access databases the easy way.
If instead you need to insert or update OLE-Object data in your databases, have a look at Access OLE Import. Using Excel spreadsheets as the input data, you can easily add and update your embedded or linked OLE-Object data.
You can purchase our products on this page.
It was really helpful when you said that every image has been modified by Access so there are a few extra steps to export it. I’ve been thinking about getting this for my business but I wasn’t sure how to work it. I want to make sure I understand how it works before I get it. Thank you for this useful article!
We have a need for a tool like Access OLE Export. But, it’s important that we control the name of the exported files. We would like to assign one of the value of one of the other fields as the file name. Is this possible?
Yes, you can name the exported files using values from the other columns in your table or data set. Please see this post for details:
Will your product also work with embedded sound files like .wav and .mp3? I have exported them to SQL Server, and so will your product work on them in SQL Server too?
Hi, yes, both Access OLE Export and SQL Image Viewer can recognize mp3 and wav files from their file signatures, and support SQL Server databases too.
This is an extremely dangerous method longer term. I have lost images permanently this way because the OLE link is dependant on an unchanging system. If you move the db to another system, back it up, update the underlying software, etc MS will deny that the item can sent to the OLE server (because it is not the same version/location etc etc) and nothing, cut/copy/change item will give the file back. I managed to fudge enough to get image out of a document but they are unusable muck instead of crisp photographs. I followed similar instructions to the above once in a MS Access database that I am currently working on, and that image is the only one that I don’t have the original file backed up.
MS stores a great deal in the files to go-fast access, e.g., and these are all relying on absolute addresses. OLE locks you in.
Could you please explain what you mean by ‘This is an extremely dangerous method longer term.’? This article explains how to extract images/files from an OLE-Object field. It does not explain how to back up a database containing OLE-Object-linked items, which is what your comments seem to be directed towards.