Multi tool use

Save MS ACCESS attachments with python

This seemed pretty simple but it wasn't.
The goal was to create an offline database with forms so Users could fill data that will later be put into reports. The catch is that this reports have complex formating and pictures so my idea was to pull data from MS ACCESS (can create tables and forms that manage attachments pretty well) and insert it into word templates using doctpl.

My problem is that I can't manage to pull the attached pictures in the ACCESS tables. I've tried using win32com.client like this:

import win32com.client
daoEngine = win32com.client.Dispatch('DAO.DBEngine.120')
db = r"C:UsersPDocumentsdb.accdb"
daoDB = daoEngine.OpenDatabase(db)
query = "SELECT picture FROM Galery WHERE ID=13"
daoRS = daoDB.OpenRecordset(query,2)
daoRS.Edit()
daoRS.Fields["picture"].SaveToFile("C:UsersPCA037Documents\")
daoDB.Close()


But it returns error:

(-2147352567, 'Exception occurred.', (0, u'DAO.Field', u'Invalid field data type.', u'jeterr40.chm', 5003259, -2146825029), None)


Can someone please give me a hand with this?

Alternatively, I know I'm not allowed to ask for opinions but does anyone know a better way to accomplish an offline database with "pretty" forms and attachment management that can be accessed with ease?

1 Answer
1

Attachment file data is stored in a subrecordset inside the field data.

You need to first access that subrecordset, and then call .SaveToFile on that, not on the main recordset:

import win32com.client
daoEngine = win32com.client.Dispatch('DAO.DBEngine.120')
db = r"C:UsersPDocumentsdb.accdb"
daoDB = daoEngine.OpenDatabase(db)
query = "SELECT picture FROM Galery WHERE ID=13"
daoRS = daoDB.OpenRecordset(query,2)
daoAttachmentRS = daoRS.Fields["picture"].Value
daoAttachmentRS.Fields["FileData"].SaveToFile("C:UsersPCA037Documents")
daoDB.Close()


The File data is always saved in a field called FileData. There are other fields present, like attachment type, flags and filename, and multiple attachments can exist for a single record (currently, this code saves the first one, throws an error if there are none, and ignores all other ones. You can use .EOF and .MoveNext on the subrecordset to check for more attachments and save them.

FileData

.EOF

.MoveNext

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.