{"id":1213,"date":"2021-02-04T21:44:09","date_gmt":"2021-02-04T13:44:09","guid":{"rendered":"https:\/\/www.yohz.com\/blogs\/?p=1213"},"modified":"2022-08-11T22:23:58","modified_gmt":"2022-08-11T14:23:58","slug":"extracting-attachments-from-an-access-table-using-access-ole-export-or-sql-blob-export","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2021\/02\/04\/extracting-attachments-from-an-access-table-using-access-ole-export-or-sql-blob-export\/","title":{"rendered":"Extracting attachments from an Access table using Access OLE Export or SQL Blob Export"},"content":{"rendered":"

Access OLE Export<\/a> 4 and SQL Blob Export<\/a> 4 have added support to extract files from attachment-type columns in Microsoft Access tables.<\/p>\n

Say we have a table named table001<\/strong> with the following structure:<\/p>\n

\"\"<\/p>\n

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

\"\"<\/p>\n

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

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

\"\"<\/p>\n

We select the sub-columns by entering the column name (files<\/strong>), followed by a dot (.), then the sub-column name.\u00a0 In the example above, to retrieve the contents of the attachment column, we enter<\/p>\n

files.FileData<\/strong><\/p>\n

and to retrieve the original file name, we enter<\/p>\n

files.FileName<\/strong><\/p>\n

Now if we want to, we can name the exported files using the original file name.\u00a0 Enter<\/p>\n

<filename><\/strong><\/p>\n

as the column to use to name all the files that are exported, in the Other types<\/strong> section.<\/p>\n

\"\"<\/p>\n

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

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

\"\"<\/p>\n

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

<ID:0000>_<filename><\/strong><\/p>\n

and our exported files would be named 0001_contract.pdf<\/strong>, 0002_invoice.pdf<\/strong>, 0003_quotation.pdf<\/strong><\/p>\n

So if you have a ton of images that you need to export, give Access OLE Export<\/a> or SQL Blob Export<\/a>\u00a0a try.<\/p>\n

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

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.\u00a0 If we just select […]<\/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":[17,44],"tags":[133,18,48],"_links":{"self":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1213"}],"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=1213"}],"version-history":[{"count":4,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1213\/revisions"}],"predecessor-version":[{"id":1807,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/posts\/1213\/revisions\/1807"}],"wp:attachment":[{"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/media?parent=1213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/categories?post=1213"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yohz.com\/blogs\/wp-json\/wp\/v2\/tags?post=1213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}