{"id":1203,"date":"2021-02-11T23:53:34","date_gmt":"2021-02-11T15:53:34","guid":{"rendered":"https:\/\/www.yohz.com\/blogs\/?p=1203"},"modified":"2022-01-04T13:10:40","modified_gmt":"2022-01-04T05:10:40","slug":"extracting-attachments-from-an-access-database-using-sql-image-viewer","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2021\/02\/11\/extracting-attachments-from-an-access-database-using-sql-image-viewer\/","title":{"rendered":"Extracting attachments from an Access database using SQL Image Viewer"},"content":{"rendered":"

Access has a curiously interesting data type – attachment.<\/p>\n

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

For starters, some file types seem to be compressed automatically by Access.\u00a0 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.<\/p>\n

Also, a single attachment-type column contains 4 sub-columns in them – FileName, FileType, FileData and FileURL (for Sharepoint databases).\u00a0 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.<\/p>\n

Say we have a table defined as such:<\/p>\n

\"\"<\/p>\n

with the following data:<\/p>\n

\"\"<\/p>\n

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

\"\"<\/p>\n

The files<\/strong> column, which is the attachment-type column, displays only the names of the attached files, and is returned as a text column.\u00a0 If however we selected the sub-columns:\"\"<\/p>\n

Access returns 3 rows instead of 2.\u00a0 Because our 2nd row contained 2 attachments, Access returns 2 data rows, one for each attachment.<\/p>\n

Thus, to extract the data from the attachment-type columns in SQL Image Viewer, we need to select the filedata<\/strong> sub-column of the attachment-type column.<\/p>\n

Since we added support for attachment-type columns in SQL Image Viewer 11, the same query above now returns this:<\/p>\n

\"\"<\/p>\n

Download<\/a> a 14-day trial of SQL Image Viewer<\/a> now and experience how easy it is to extract attachments from your Access databases.<\/p>\n

<\/fb:like><\/div>
<\/div><\/div>","protected":false},"excerpt":{"rendered":"

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.\u00a0 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.\u00a0 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[11],"tags":[12],"_links":{"self":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1203"}],"collection":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/comments?post=1203"}],"version-history":[{"count":4,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1203\/revisions"}],"predecessor-version":[{"id":1573,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1203\/revisions\/1573"}],"wp:attachment":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/media?parent=1203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/categories?post=1203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/tags?post=1203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}