Updating a multi value field in Access 2007 using Visual Studio 2005 RRS feed

  • Question

  • Hello,

    I'm working on Visual Studio 2005, on a project using window forms.

    As part of the project I'm supposed to read / update an Access 2007 file located in my computer.

    I've created the DataSource / DataSet to connect to this file, and also used an adapter to run the select / update queries.


    The problem is that while it works on regular fields, when I have to update a multi value field, the system is not working

    (or at least, I'M not getting it to work properly Smile  I wrote the sql code that update the field in the Access 2007, but when I tried to put it in the Query Builder, I got messages saying that the syntax is wrong, but when I ran the code it worked. This happened when I used a regular query without parameters. When I tried to insert parameters to the query, I've got a message saying: "No Value was given to one or more required parameters", although I defined parameters to fit the query.


    here's the query: INSERT INTO Calls (`Call Type`.`Value`) VALUES     (?)
    WHERE     ID = ?


    (I've got also a message saying that the '.' is not recognized, but as I said, I got the same message when I inserted actual values to the query, and in spite of this I was able to update the Access file.


    If you have any idea what Am I doing wrong, please let me know.




    Wednesday, February 13, 2008 2:15 PM

All replies

  • The syntax for this statement looks a little strange to me. The column names should not be enclosed in single quotes (use brackets instead) and should be separated by commas. I can't tell how many columns you are trying to insert into in your SQL statement but you would need a placeholder for every one in your VALUES statement.


    Wednesday, February 13, 2008 5:08 PM
  • Hi Paul,


    I know this is not a regular syntax. However you can't use the regular syntax to update a multi value field.

    i.e. if you try to write something like: insert into calls (Call type) VALUES ("123") where ID= 3 you'll get a message

    saying that you can't use UPDATE or DELETE to change multi value fields. in order to update a multy value field you have to use calls.Value - which represent an entry in the multy value field.


    Thursday, February 14, 2008 8:24 AM
  • OK, I had to find out what a multi-valued field is because they're new in Access 2007 and I've never used them before.


    I don't think you will be able to work with these in Visual Studio Query Builder. This tool is rather picky and doesn't support many of the Access specific features. You can probably build and save the query but you won't be able to execute it from the tool.




    Thursday, February 14, 2008 3:10 PM

    Hi Barak


    I am having the same problem as you. I am using ADO with C# and cannot work out how to update multivalued fields in SQL.


    Were you able to find a resolution or work around to this problem





    Sunday, April 6, 2008 1:57 PM
  • Here's the run-down on nulti valued fields in queries:

    So you query will look like:

    INSERT INTO Calls ([Call Type].[Value]) VALUES  (?)
    WHERE     Calls.ID = ?
    Monday, June 22, 2009 5:38 AM