Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all!

    I have a question regarding the message "Must use an updateable query."

    I built a very simple update query from two tables. The table I am updating is local, the table I'm using to update it is in DB2. Simple join between the two. Select query is successful, no problems. But when I try to run the update query, I keep getting the message "Must use an updateable query." I have other update queries that are more complex than this, but all were written before we upgraded to Access 2003.

    I know I can do this in code, but the db is shared, and other people don't understand the code I write and get upset with me when I do stuff that way, so I'm trying not to. I made sure the database isn't corrupt, and after not succeeding when other people were logged in, I waited until everyone was out to run the query, still with no success.

    Any ideas?


    Sample of the query I'm using:

    UPDATE tblMyTable INNER JOIN DB2_TABLE ON tblMyTable.RecordID = DB2_TABLE.RECORDID SET tblMyTable.SpecialCode = Trim([DB2_SPECIAL_CODE]) WHERE (((DB2_TABLE.DB2_SPECIAL_CODE)<>""));

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    For two table Inner Joins like your example, there are a couple of possibilities. One is that one of the tables does not have a primary key. The other possibility is that you are trying to update multiple rows in one of the tables, or you are trying to put multiple values into a single record. For more information on the subject check out John Viescas' Updatability.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What a great reference, Wendell! Thanks! I found the following "checklist" on that page:

    Query Fields That Cannot Be Updated

    Any field that is the result of a calculation
    Any field in a total or crosstab query
    Any field in a query that includes a total or crosstab query as one of the
    AutoNumber fields
    A primary key participating in a relationship unless Cascade Update is
    Any field in a Unique Values query
    Any field in a UNION query

    It turned out that my tblMyTable didn't have a designated Primary key, so I fixed that situation. But the rest doesn't apply..I'm merely updating new blank fields, 62 of them out of the 700-odd records in the table, and the primary key is definitely unique and is the basis of the join. Nothing I can do if there's no primary key in the DB2 table, but I hope that there is one (and believe that there is one, too, and it should be the same one as in tblMyTable).

    I'm a little unclear about the Cascade Update relationship thing, trying to figure that out now, but I doubt that's the problem, either. Grrr, how frustrating.

    Edit: I gave up and did it in code.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by Cecilia View Post
    It turned out that my tblMyTable didn't have a designated Primary key, so I fixed that situation. But the rest doesn't apply..I'm merely updating new blank fields, 62 of them out of the 700-odd records in the table, and the primary key is definitely unique and is the basis of the join. Nothing I can do if there's no primary key in the DB2 table, but I hope that there is one (and believe that there is one, too, and it should be the same one as in tblMyTable).

    I'm a little unclear about the Cascade Update relationship thing, trying to figure that out now, but I doubt that's the problem, either. Grrr, how frustrating.

    Edit: I gave up and did it in code.
    I may be wrong but i would think you cannot join an update query across multiple databases especially one via ODBC.
    What i would have done is to import the records required into a local access table ensuring that table has a primary key, then run an update queryon 2 access tables.

    The Cascade Update is not applicable in this problem you have. What a Cascade Update relationship thing does is to change the values of a field that is defined in the ONE side of a relationship and is also defined in tables linked to that table.
    In this case it will change the field in all related tables where it is used and linked to that master table.

    nb. I read an interesting article once where you should never need to use the Cascade Update feature, this article explains to use Autonumber fields to join tables..

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by patt View Post
    I may be wrong but i would think you cannot join an update query across multiple databases especially one via ODBC.
    I just did a test of this and it worked OK for me. I have a database with tables linked via ODBC to a SQL Server db. I ran an update query to updates values in a local table using values from the linked table.

    Code:
    UPDATE tblPatientsLocal INNER JOIN dbo_Patients ON tblPatientsLocal.patientID = dbo_Patients.patientID SET tblPatientsLocal.surname = [dbo_Patients].[lastname]
    WHERE (((dbo_Patients.lastName) Is Not Null))
    I suspect that the DB2 table does not have a Primary Key.

    I agree that using autonumbers as key fields avoids the need for cascading updates, but I find the odd occasion where they are useful. Usually where you inherit some data. Just yesterday I was working with membership data, where the membership type was a letter code, and I needed to change the codes. Cascading Updates took all the pain out of it.
    Regards
    John



  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Thanks John, I stand corrected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •