CODEWITHSUNDEEP
c++winapims-access-2007adoattachment-field
AlwaysLearningNewStuff
AlwaysLearningNewStuff

Reputation: 3031

Handling fields of Attachment type in MS Access using ADO

I have some fields in my database that will need to store images ( bitmap, JPG or PNG ) and PDF ( or Excel/RTF/TXT ) files. Browsing through Internet, I have learned that MS Access 2007 ( and newer versions ) have a field of type Attachment that can suit my needs.

However, I am filling database via GUI ( made in C++ and WinAPI ), using ADO. I was not able to find an example of inserting/loading such data into/from database using ADO. Searching here through some similar questions ( VB, Delphi, Python...) I have found out that my approach might not be the best, but seems possible to do.

Being inexperienced, and since this is my first time tackling this type of task, I ask community to help me solve this task. Therefore my question:

Can you provide small code snippet that illustrates inserting/loading such data into/from database using ADO?

Thank you.

Upvotes: 3

Views: 1504

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123419

If you are planning to only use the C++ application as the front-end and do not expect users to be opening the database in Access itself then I suggest that you avoid using the Attachment field type. Instead, use a separate child table named [Attachments] with a one-to-many Relationship (foreign key constraint) between the [Attachments] table and the parent table (where you were considering using the Attachment field). Then, save the documents as raw binary data in an OLE Object (long binary) field in the child table.

The Attachment field type offers several advantages for applications that use the Access UI. Support for multiple attachments to a single database record can be as simple as dropping an Attachment control onto an Access form. Attachments can also be accessed from Datasheet view, although all you see there is a "paper clip" icon.

Attachment fields can be manipulated from code, but only by using an ACE DAO Recordset2 object (example here). In order to be able to save multiple attachments per record, the Access Database Engine uses a hidden child table. It is possible to pull some information into a SELECT query using "magic" field name qualifiers (e.g., Field1.FileName) but neither ADO nor ODBC can INSERT or UPDATE Attachment field entries.

Since you will not be using the Access UI for your application

  1. you will not be able to use many of the advantages that an Attachment field has to offer, and
  2. you could still manipulate the Attachment field via ACE DAO from your C++ app, but it would be a nuisance.

The one (possibly) significant advantage that you might miss by not using an Attachment field is that the Access Database Engine automatically compresses files in an Attachment field but raw binary data in an OLE Object field is stored uncompressed. If the files you intend to save were all in compressed formats anyway (e.g., JPEG, .docx, .xlsx) then this would not be an issue. However, if you plan to store a lot of large documents in uncompressed formats (e.g., .txt, .rtf) then file bloat could be a problem. In that case you could have your C++ app automatically compress those documents (perhaps using GZipStream) before saving them and uncompress them on retrieval.

Upvotes: 6

Related Questions