Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Operation must use an updateable query? (2000)

    I get an 'Operation must use an updateable query' when I try to run the following query:

    UPDATE StockInquiry LEFT JOIN OnOrderTotal ON StockInquiry.article_id = OnOrderTotal.article_id SET StockInquiry.[Total On Order] = [OnOrderTotal]![Total On Order];

    I've done lots like this in the past and they have all worked OK. I cannot see what I am doing this time that is different. I am baffled.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Operation must use an updateable query? (2000)

    Access 2000 and higher are more picky in this respect than Access 97. OnOrderTotal looks like a Totals query, this is probably the cause of the problem. Try setting the "Unique Records" property of the query to Yes. This corresponds to adding DISTINCTROW to the SQL statement for the query.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Operation must use an updateable query? (2000)

    You have a couple of problems in this query. One is the lack of the DISTINCTROW keyword that Hans mentioned. The other is that you are using an outer join between the tables which could include null values if there is no matching record in OnOrderTotal. Is that really what you wanted to do?
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Operation must use an updateable query? (2000)

    I've tried both suggestions (DISTINCT ROW and join type) but still get the same 'Operation must use an updateable query' error.

    the query now looks like this:

    UPDATE DISTINCTROW StockInquiry INNER JOIN OnOrderTotal ON StockInquiry.article_id = OnOrderTotal.article_id SET StockInquiry.[Total On Order] = [OnOrderTotal]![Total On Order];

    Ian

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Operation must use an updateable query? (2000)

    Is OnOrderTotal a table or a totals query? Totals queries are not updatable, and like union queries, they can make other queries in which they are joined not updatable.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Operation must use an updateable query? (2000)

    Hi again,

    I seem to have two threads running which have the same theme - the way that I have done things in the past is not right, even though up until now they have usually worked.

    OnOrderTotal is indeed a totals query. Should I create a local table or maybe use an intermediate query?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Operation must use an updateable query? (2000)

    Unfortunately, the query engine changed radically between 97 and 2000 and a bunch of things that used to work no longer do. An intermediate table is perhaps the simplest way to do this since you're trying to store summary information, an action which isn't usually done. Another method is to use code to open a recordset on the totals query and another on the main table and then update the main table recordset from the query recordset.
    Charlotte

Posting Permissions

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