Extracting attachments from an Access database using SQL Image Viewer

Access has a curiously interesting data type – attachment.

I say interesting because it does so much, but much of it is done behind the scenes.  To the user, it’s just so easy to attach one or or more files to that column.

For starters, some file types seem to be compressed automatically by Access.  I attached a 23 MB text file, and the database size only increased by 3 MB, which is about the size of what my text file would have compressed to.

Also, a single attachment-type column contains 4 sub-columns in them – FileName, FileType, FileData and FileURL (for Sharepoint databases).  These are the sub-columns we need to include in our query in SQL Image Viewer when we want to extract the files from the attachment-type columns.

Say we have a table defined as such:

with the following data:

Row 1 has 1 attachment, and row 2 has 2 attachments.  If we run a query that selects just the 3 columns from the table in SQL Image Viewer, we get this:

The files column, which is the attachment-type column, displays only the names of the attached files, and is returned as a text column.  If however we selected the sub-columns:

Access returns 3 rows instead of 2.  Because our 2nd row contained 2 attachments, Access returns 2 data rows, one for each attachment.

Thus, to extract the data from the attachment-type columns in SQL Image Viewer, we need to select the filedata sub-column of the attachment-type column.

Since we added support for attachment-type columns beginning SQL Image Viewer 11, the same query above now returns this:

 

Leave a Reply

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