3

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:

  • How should I handle inserting/loading this type of data ( images, documents...) into/from MS Access 2007 (or higher) database using ADO?

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

Thank you.

AlwaysLearningNewStuff
  • 2,939
  • 3
  • 31
  • 84
  • You say that you will be "filling [the] database via GUI ( made in C++" but will that C++ front-end be the sole mode of interaction with the database? Or, will other users be opening the database in Access itself (full version or runtime) and using Access forms and reports? – Gord Thompson Nov 18 '14 at 19:22
  • @GordThompson: *will that C++ front-end be the sole mode of interaction with the database?* Yes, end users have no programming background. *will other users be opening the database in Access itself (full version or runtime) and using Access forms and reports?* No, if anything unplanned happens, it will be me who will have to deal with it. It is important to mention that users want documents stored in database. I can't store file path as string. Currently I am analyzing [this example](http://msdn.microsoft.com/en-us/library/windows/desktop/ms676103%28v=vs.85%29.aspx) as it looks promising. – AlwaysLearningNewStuff Nov 18 '14 at 20:19
  • @GordThompson: I remember that you have successfully helped me in the past. If I recall correct, you use `VB` instead of `C++`. If you can't help in any other way, I will consider the solution in `VB` or as pseudo code... – AlwaysLearningNewStuff Nov 18 '14 at 20:26

1 Answers1

6

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.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you for answering. I am not using .NET but will look into WinAPI for compressing the files ( I think I found something earlier ). I will talk to my superiors to try suggesting them to use a folder in applications directory, so I can store the name of the file only, and then copy that file into directory. Then I could use `ShellExecute` to open the file, for example... As for inserting OLE Objects into Access via C++, should I start with [this](http://msdn.microsoft.com/en-us/library/windows/desktop/ms676103%28v=vs.85%29.aspx) example? Best regards. – AlwaysLearningNewStuff Nov 19 '14 at 16:51
  • @AlwaysLearningNewStuff Sorry, but I am unable to offer any advice that is specific to C++. (The basis of my answer was that your app *wasn't* Access, rather than the fact that it *was* C++.) I do a fair bit of C# work, but C++ is just too different. – Gord Thompson Nov 19 '14 at 21:07
  • I am almost close to solving my problem, thus officially accepting your answer. Since I strongly believe the problem is MS Access related, I ask you to help me with [this](http://stackoverflow.com/questions/27234746/can-not-insert-file-into-variant) question, specifically how to remedy the error MS Access reports. Thank you. Best regards. – AlwaysLearningNewStuff Dec 02 '14 at 05:47
  • Since I have taken your advice and switched from `Attachment` to `OLE Object` I have decided to officially accept and upvote your answer. Thank you. Best regards until next time. – AlwaysLearningNewStuff Dec 02 '14 at 23:24