Querying Access 2007 multi-valued fields using ODBC

Question

I have an Access 2007 database that uses the "Attachment" datatype (new in Access 2007) for one of its field. This field is a magical "multi-valued" field that can contain several attachments.

I would like to be able to easily see the contents of this field, for all the rows in the database, using a single ODBC query. Ideally, each row of the original table should be exactly one row, and I'd like all the attachments returned as a single database cell. In a perfect world, it would be possible to reinsert that set of attachments into another table using a single INSERT INTO query.

I think that might be a tall order, so if I have to, I'd settle for:

• An SQL query that tells me the number of attachments in a given attachment field

or worst case:

• An SQL query that tells me whether or not the attachments in a given row are empty or not.

I can't seem to find any good docs about multi-valued fields around on the net. Perhaps they're too new.

Thoughts?

(P.S. Don't bother telling me that multi-valued fields are evil. I already know. I'm not the one who made the database.)

Answer 1

See http://www.access-freak.com/tutorials.html#Tutorial07 for some information although he doesn't have a good screenshot or SQL of the query. His website isn't the best formatted or readable either.

This assumes you're running the query from inside Access 2007. If you want to run it via ODBC see http://groups.google.ca/group/microsoft.public.data.odbc/browse_thread/thread/d0ee29cc5e54e0fb

Answer 2

If you can use ADO then connect to the data source without using OLEDB:Support Complex Data=True in the connection string and query the column: you should get a column of type adLongVarWChar (i.e. MEMO) where the value is the file names delimited by semicolon characters. Therefore, getting a list of files will merely involve a simple parse.

It may be possible to get the attachments using ADO but I've yet to see it done. Using OLEDB:Support Complex Data=True means you will get a column of type adIDispatch i.e. an object. What that object is, I do not know (I hoped it would be a an ADODB.Recordset but no go).