Querying Access 2007 multi-valued fields using ODBC

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.


(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.)

-------------Problems Reply------------

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).

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

Category:sql Views:0 Time:2009-08-06

Related post

  • Access 2010 multi value field in report 2012-07-13

    I'm putting together a report based on two tables. The first table has: Name Address City, State, Zip and a multi Value field Called Codes (e.g. 1,2,3,4) Second table has two fields: Code (1,2,3,4) Code Description (text description for each code) I

  • Access 2007 - Display text fields in drop down list while bundling to an ID 2010-06-01

    in Access 2007 how can I display text fields in drop down list while bundling to an ID? not while selecting (we can do this by plying with the width field. but what I need after selecting? Do we create a hidden field that stores the ID? -------------

  • Excel 2007 - MS Query - Access 2007 2015-02-17

    I am using Excel 2007 and I am trying to get MS query working as it did with Excel 2003. On the data tab, if I select From Access I am given the ability to connect to Access 2007 databases (.accdb), but after I have connected the abilty to use MS Que

  • MS Access 2007: Add AUTOINCREMENT field from MAKE TABLE query 2012-02-07

    I am creating a table T2, from an existing table T1, using the MAKE TABLE query type and need to add an autoincrementing field as a primary key to the new table T2. I'm curious of the SQL required. For example: SELECT AUTOINCREMENT(1, 1) AS ID, T1.*,

  • Form missing data from table and query - Access 2007 2013-09-29

    I hope someone can help with this issue, I'm really new to Access. I basically combined 2 tables, and deleted the relationship between the 2 since it would just be duplicate info anyway. When I added new info to the table, it didn't carry over into t

  • Microsoft Access 2007 Populating a Field in a New Record 2013-12-31

    I'm sure I am overlooking something very simple but - I am working on a Medical Charting System that I am going to use in Cambodia. I am a retired surgeon that does volunteer work in Cambodia. What I have written is essentially an electronic record.

  • Access 2007 - "Too many fields defined" error message 2014-03-28

    I am running a 2007 Access database for a membership society. One of the tables wont let us add new fields as says "Too many defined". I have deleted many old fields in this table yet I still cannot enter the new fields we wish to add as the same "To

  • No current record error when excuting a SQL Query Access 2007 2014-10-29

    Hi , I am exeucting a query to generate the report from multiple tables. I am geting the 'No Current record" error whenever i include some fields , but i dont get those error when I remove those fields Query that works: ******************* SELECT Dis

  • Microsoft Access 2007: Problems with field size in Memo. 2015-03-09

    Hi, I'm using a microsoft access database, 2007 on Vista. As the majority of my database is text, I have put the fields for the most part into MEMO format. Having said this, having only typed in 44 words (203 characters, 245 with spaces) it is deleti

  • Return list of emp # NOT in a query, Access 2007 2011-07-22

    have a table of the list of problems people can have. These people are id'd by a number. These people can have any number of problems (usually 5-10). I want to get a list of the people that tobacco DOES NOT appear as a problem for them. My solution w

  • Why will access (2007) not display fields or data from table or form in print preview? 2013-02-14

    I have developed a table; however, whenever I select "print preview" in either the table or form view, the table or form in question is not visible. I do not have this issue with other tables and forms developed and the only difference between them i

  • Access 2007 - Value of field in subform equals Master Form & additional help with subform 2012-01-18

    Value of field in subform equals Master Form & additional help with subform Master Form FISA subform Pell Payments Fields - Table # 1 Data Type Fields - Table # 2 Data Type Key 1 FISA_ID AutoNumber Key 1 PP_ID AutoNumber 2 Pell_Award Currency Key

  • date-time parameter in query access 2007 2012-10-25

    i am trying to use a parameter in a query that will pull all transactions between 6:00am yeterday and 6:00am today. I tried using the following but it won't work: between date()-1 6:00:00 AM and date() 6:00:00 AM Any ideas? --------------Solutions---

  • Access 2007: Multi-select list box IF statement 2012-12-13

    Hi, I've created a Multi-select box with 3 columns on the data source. I wish to run an IF statement on a como box which will search the list for values equal to a pre=determined value. This worked fine on a normal combo box using : If (Me.DG_Class.C

  • Access 2007 - Multi Select Items 2013-06-28

    Tabels= Employees, Hire Information, Checklists I have the subform that pulls the duplicate records according to the ID# of the employee and list the checklists they are trained on. The form pulls the info, ID#, Name and then you insert the date and

  • Access 2007 Multi User Server Install - Limited # Users can import and export tables or XLS sheets 2013-09-24

    We are having an issue where our Access Install is on a network server that 5-6 of us can remote to and run database queries. We get an error when attemting to import or export tables. It is the.......... Access can't find the wizard error or incorre

  • Microsoft access 2007 amount of fields visible in form dependent on integer 2012-06-01

    The database I am working on is a database of clients. Im my database I would like to have the option of multiple addresses. This is because some clients/companies may have more than one owner. I would like to have address/contact details recorded fo

  • glitch with Access 2007 partial match/duplicate query 2012-09-05

    I have created a query in Access 2007 to find fields that partially match. The query shows duplicate titles of books we are ordering for the library. This is very helpful when it works correctly. Here is an example from the QUERY: Psychiatric Nursing

  • Access 2007 query does not yield results 2012-09-02

    Created and run the following query (Access 2007 on Windows XP SP3): SELECT DISTINCT Issue_Num, Issue_Date, Investigation_Steps, Investigation_Results FROM Investigation_Table WHERE (Issue_Date >= [Close Date] AND (Investigation_Steps = "Investiga

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.152 (s). 11 q(s)