Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Little Rock, Arkansas, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to update fields in my tables. I made an update query and updated thousands of records using data drawn from another table. Then I needed to update another field in the same table, but this time the data is in a query. I get "Operation must use an updateable query" every time I try to run the update query. I can change the update query to a select query and it runs with no errors. The query that the update query is drawing from is not updateable, but I am not trying to update the query, I am trying to update a table. If the data is there, why won't it just write it to the table? I have a report that prints this out every day, but I need to start saving it in the table for historical analysis.

    Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The select query that an update query is based on needs to be updateable, otherwise it's not clear to which records the updates are going to be applied. You may be able to change the design of the query so that it becomes updateable, but if you want help with that we'd need to see a stripped down and zipped copy of the database.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    Little Rock, Arkansas, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Clearly, that's what the error message is saying, but try as I might to understand it; it just makes no sense to me. It is not an ambiguous query, if it was, I would not be able to display the results on my report. If the query is valid, why can I not save the data to a table? Making the original select query updateable is not a likely option, it is a complicated query based on choosing the closest value from another table via an underlying group-by query.

    For today, I worked around this by changing from an update query to a make table query. Then, I ran an update query based upon the new table, presto, my data is updated like I wanted. I need a long term solution, something I can run every day from now on. I can automate the process, build a macro to delete the temporary table, run the make table query, and then run the update query, tie this macro to the On Close event on the data entry form. I had a process like that in the past, but it was prone to problems when multiple users would inadvertently try to use and delete the same temporary data tables at the same time, so I told myself I would never do that again. Would you advise this approach? If I used code instead of the query builder in Access03, is there a way to update the records without first writing them to an intermediate temporary table?

    Thanks for your help!


    [quote name='HansV' post='780361' date='17-Jun-2009 13:47']The select query that an update query is based on needs to be updateable, otherwise it's not clear to which records the updates are going to be applied. You may be able to change the design of the query so that it becomes updateable, but if you want help with that we'd need to see a stripped down and zipped copy of the database.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Without seeing the design of the query, it's hard to provide assistance.

    A common cause for a query not being updateable is a join between tables where there isn't a unique index on the link fields on either side. If that is the case, see if it's possible to create a unique index on the "one" side of the join.

    Sometimes, you can get around this as follows:
    - Open the query in design view.
    - Click in an empty part of the upper half of the query design window.
    - Activate the Properties window.
    - Set the Unique Records property to Yes. This corresponds to adding DISTINCTROW to the SQL statement of the query.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In addition to Hans' comments about updatability, if your query joins three or more tables, or if you have calculated values in your query (it appears you do), then the query will not be updateable. And easy test is to check and see if you can append new records. If you can't then the query is not updatable. You can also check to see if you can start an update to a specific record, and it won't let you if the query isn't updateable.

    Based on what you've said, I would be inclined to develop a VBA module that opens the appropriate recordsets and does the updates in a fashion somewhat similar to the Make Table approach you are using. The advantage of VBA over using macros and such is that you can create error handling routines to deal with the weird things. Even in those cases, the recordset you are updating must however be updatable of course. In summary, it is possible to do it in code, and not have to create a temporary table. If you would like some assistance with that we can try to point you in the right direction.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Following with interest....because I have same error reporting, so if I may.
    one original table with data:
    tbl_PO_Summary
    Client_Ref - text
    Purchase_Order - text
    Tax_Year - number
    Tax_Period - number
    BillAmount - number

    A query built on sql db table derives the current sum bill amount as posted in separate bill fields. Because the number of postings are many, then the billamount is summed over the grouped period
    qry_PO_Details

    The query and the table are brought together in an update query (but same error of "Operation must use an updateable query" occurs)

    SQL for the update query is:
    UPDATE DISTINCTROW qry_PO_Details INNER JOIN tbl_PO_Summary ON (qry_PO_Details.Tax_Period = tbl_PO_Summary.Tax_Period) AND (qry_PO_Details.Tax_Year = tbl_PO_Summary.Tax_Year) AND (qry_PO_Details.Purchase_Order = tbl_PO_Summary.Purchase_Order) AND (qry_PO_Details.Client_Ref = tbl_PO_Summary.Client_Ref) SET tbl_PO_Summary.BillAmount = ([qry_PO_Details].[billamount]);

    If needed I'll post new thread but seeing as it is practically identical error then I figured would be OK to post.
    Any help much appreciated.
    Thanks
    Alan

    Cheshire, cloudy and wet.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='ase001' post='791781' date='02-Sep-2009 02:09']...
    SQL for the update query is:
    UPDATE DISTINCTROW qry_PO_Details INNER JOIN tbl_PO_Summary ON (qry_PO_Details.Tax_Period = tbl_PO_Summary.Tax_Period) AND (qry_PO_Details.Tax_Year = tbl_PO_Summary.Tax_Year) AND (qry_PO_Details.Purchase_Order = tbl_PO_Summary.Purchase_Order) AND (qry_PO_Details.Client_Ref = tbl_PO_Summary.Client_Ref) SET tbl_PO_Summary.BillAmount = ([qry_PO_Details].[billamount]);[/quote]
    You don't indicate what version of Access you are using, which may or may not have any bearing on the issue, but in general the more recent the version, the more picky the database engine is about what queries are updateable, and what are not. You could try setting the property of the query called Recordset Type to Dynaset (Inconsistent Updates), but I would be more inclined to make a temporary table as the original poster did, or take the VBA approach if you have a multi-user situation.
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='791792' date='02-Sep-2009 12:24']You don't indicate what version of Access you are using, which may or may not have any bearing on the issue, but in general the more recent the version, the more picky the database engine is about what queries are updateable, and what are not. You could try setting the property of the query called Recordset Type to Dynaset (Inconsistent Updates), but I would be more inclined to make a temporary table as the original poster did, or take the VBA approach if you have a multi-user situation.[/quote]
    Apologies, it's access 2007 office sp2
    Again, with multi-users the temp table is fraught with sharing issues.

  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Union query!!
    Blinking blast!!!!!
    The originating source query (parent query?) was a union query which is a no-no for use in the update query tree (as are grouped queries involving summed fields).
    resolved the union query with a select query calculated field and all updates nicely now.

    Thanks for the suggestions and hopefully this will give pointers to other 'updaters'.
    Cheers
    Alan

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Glad you were able to solve it - the possibility of it being a union query completely escaped me.
    Wendell

Posting Permissions

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