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

    Update query - must use an updateable query (2000)

    I have an update query that refuses to work. The message it gives is 'Operation must use an updateable query.' I am trying to update to fields in a table with the results of some calculations that have been done in another query. My update query is very simple; it contains the table to be updated, with left joins to the query containing the calculation results (see below).

    I've had a similar problem in the past and I think the essence of it then was that the fields that the joins point to in the table/query containing the data to be updated from, do not make up the primary key of a table. Access can't guarantee that there's a one to one relationship between the records in the table it's updating and the table it's getting the data from, so it refuses to cooperate.

    In this case I know that there is a one to one relationship, but how do I persuade Access to update the records? I could create a table from my calculation query, index it, then use that in my update query. However, this seems like a lot of work. I could step through the table and the query in code, but this would be slow. I

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

    Re: Update query - must use an updateable query (2000)

    If you have a one-to-one relationship, why use a left join? Does it work if you change it to an inner join? If not, you can create an update query based on tbl_AllocGrid_Detail alone, and set the Update To values to DLookup expressions:

    DLookup("Ros", "qry_AllocStoreDetail_ROS2", "branch_code = " & [branch_code] & " AND ProductCode = " & [ProductCode])

    and

    DLookup("CalcCover", "qry_AllocStoreDetail_ROS2", "branch_code = " & [branch_code] & " AND ProductCode = " & [ProductCode])

    I assumed that the codes are numeric; if they are text, use

    DLookup("Ros", "qry_AllocStoreDetail_ROS2", "branch_code = " & Chr(34) & [branch_code] & Chr(34) & " AND ProductCode = " & Chr(34) & [ProductCode] & Chr(34))

    and similar for the other.

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

    Re: Update query - must use an updateable query (2000)

    Thanks Hans. Using an inner join did not work. In the end I have gone for the route of creating a temp table. I tried the DLookup approach but it took too long to run.

    Was I right in thinking that the problem is because Access can

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

    Re: Update query - must use an updateable query (2000)

    Without seeing the database it's hard to say what would work and what wouldn't.

Posting Permissions

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