Access 2007/2010 Attachment Data Type

[ScottDobbins1]

Access 2007/2010 allows a data type called Attachment, which allows attaching actual documents (even multiple documents) in a field of a database.  The database actually contains the documents, not just a link to a document elsewhere.

My question: does anyone know of a way for LabVIEW to programmatically attach/recall documents from an Access attachment field?  I found a good piece of code written by Eric_B, but it only shows how to recall the first document attached in a field (there may be many).

Too bad the DB Connectivity Toolkit does not support this.  The workaround code that Eric_B wrote is not trivial.

Thanks.

[mikeporter]

Actually Access has nothing to do with it. Jet -- the actual DBMS involved -- has been able to handle BLOBs (binary large objects) for some time. The good news is that inserting things like the contents of files is not hard.

Basically, at the ADO level you create a recordset that defines the data you want to insert by calling a query that is guaranteed to not return data. Something like

SELECT field1, field2 FROM myTable WHERE 1 = 0;

Once you have the record set reference there is a method that lets you add records to the recordset and then save the recordset back to the database.

Because the data input to the method is an array of variants, the data can be just about anything -- as long as the field is of the right datatype, which if I remember right is Long Char. This is all well documented online.

Mike...

[mikeporter]

The drivers I posted in the forum sometime ago supported this process:

https://forums.ni.com/t5/LabVIEW/Extracting-one-column-from-Database/td-p/544341/page/5

Mike...

[ScottDobbins1]

Mike:

I followed the link and took a look at your set of drivers.  Nice job.  It looks like you encapsulated and simplified a lot of the work.  But as a new user, I'd need an example, showing your drivers in use.  For example, what is a "Connection string"?  I assume that's where the Access DB file name goes, somehow.  But without an example, I wouldn't get very far.  Do you have some code that could help out?   What I'm specifically interested in is attaching files to an Access DB field, and recalling them (all other DB functionality I need to do is covered by the DB connectivity toolkit).  I'm sure your drivers could be used to do this, based on your prior message.  Thanks.

[mikeporter]

For details check my blog. I did a series recently that will.answer most of your questions.

Specifically related to the BLOB operation the method itself actually needs two arrays of variants. One is an array of column names the other is an array of data values. I encapsulated it to create what I hope is a simpler interface.

The thing to remember is that an array of variants is not the same thing as a variant that contains an array.

Mike...

[ScottDobbins1]

Thanks so much Mike.  I've read a few of your posts.  You have a talent for... calming the reader.  More proof I'm a newbie: where do I find your blog?

[mikeporter]

Check the link in my signature. Thanks.

Mike...