Access OLE Export 4 and SQL Blob Export 4 have added support to extract files from attachment-type columns in Microsoft Access tables.

Say we have a table named table001 with the following structure:

Attachment-type columns in Access contain sub-columns that contains details of one or more attachments stored in that row.  If we just select the table during the export process like this:

Access OLE Export / SQL Blob Export (version 4.10 onwards) will automatically detect that the files column is a attachment-type column.  It will then retrieve the FileName, FileData, and FileType subcolumns, and export the content from the FileData column using the file name value from the FileName column.

However, if you want to name the exported files differently, you will need to use the Extract items using SQL query option.  What we need to do is to enter a query to retrieve the sub-column (FileData) that contains the actual file contents.  The other useful sub-column is FileName, which stores the original file name.

We select the sub-columns by entering the column name (files), followed by a dot (.), then the sub-column name.  In the example above, to retrieve the contents of the attachment column, we enter

files.FileData

and to retrieve the original file name, we enter

files.FileName

Now if we want to, we can name the exported files using the original file name.  Enter

<filename>

as the column to use to name all the files that are exported, in the Other types section.

Access OLE Export or SQL Blob Export will then export each attachment returned by your query, and name the files using the original file names.  Of course, you can still use a different naming convention if you need to do so.

But say we want to add the ID value to the file name, as there may be duplicate file names.  We could then enter <ID>_<filename> for the file naming convention, and our exported files will contain the ID value.

We could further format the ID value to 4 digits, if we want it sorted neatly in Windows Explorer.  For e.g. instead of exported files named 1_contract.pdf, 2_invoice.pdf, 3_quotation.pdf, we could enter the naming convention value:

<ID:0000>_<filename>

and our exported files would be named 0001_contract.pdf, 0002_invoice.pdf, 0003_quotation.pdf

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