Extracting attachments from an Access table using Access OLE Export or SQL Blob Export

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:

we would not be able to extract the attached files.  What we need to do is to select the Extract items using SQL query option, and 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.

Leave a Reply

Your email address will not be published. Required fields are marked *