CODEWITHSUNDEEP
pythondatabaseformsms-access
Paulo Castro Da Silva
Paulo Castro Da Silva

Reputation: 113

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:\Users\P\Documents\db.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:\Users\PCA037\Documents\\")
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?

Upvotes: 2

Views: 1120

Answers (3)

Parfait
Parfait

Reputation: 107737

To retrieve attachments from MS Access database tables, consider a pure SQL solution using pyodbc which requires removing the first 20 bytes per the great discovery by @ChristianJunk!

import os
import pyodbc

# CONNECT TO DATABASE
conn = pyodbc.connect(    
    "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    "DBQ=C:\Path\To\My\Database"
)
cur = conn.cursor()

# PREPARED STATEMENT WITH PARAMETER PLACEHOLDER
sql = "SELECT picture.filename, picture.filedata FROM Galery WHERE ID=?"

# RUN SQL WITH PARAMETER
cur.execute(sql, [13])

# FETCH SINGLE ROW
row = cur.fetchone()
pic_name = row[0]
pic_data = row[20:] # REMOVE PREPENDED BYTES

cur.close()
conn.close()

# SAVE BYTES DATA TO DISK
pic_file = os.path.join("C:\\", "Users", "PCA037", "Documents", pic_name)

with open(pic_file, "wb") as f:
    f.write(pic_data)

# CLOSE CURSOR AND DATABASE CONNECTIONS
cur.close()
conn.close()

Upvotes: 0

ASH
ASH

Reputation: 20342

As Erik pointed out, save images as attachments with a fully qualified path pointing to the file; don't try to save actual images inside your database.

https://support.office.com/en-us/article/attach-files-and-graphics-to-the-records-in-your-database-d40a09ad-a753-4a14-9161-7f15baad6dbd

https://www.cimaware.com/expert-zone/working-with-attachment-data-type-in-microsoft-access

Important attachment related information:

The maximum size of an attached data piece cannot be larger than 256Mb.

Adding, deleting, and editing of the attached items is only possible through an Attachments dialog box.

In the design of Forms and Reports there is new item in the toolbox: the Attachment control (looks like a paper clip). This control may be used at design time.

Editing an attachment is possible if the program used for creating the attachment is available on the local computer. The program will edit the attachment and when it is saved the attachment gets saved to its own field.

Access will compress uncompressed files in the attachments before storing them.

Attachments may originate from any location on the disk drive or the network.

VBA can be used to work with attachments programmatically using the new Attachment Object. It has several properties and methods, and it supports events.

Upvotes: 0

Erik A
Erik A

Reputation: 32682

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:\Users\P\Documents\db.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:\Users\PCA037\Documents")
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.

Upvotes: 1

Related Questions