Operation Must Use Updateable Query

Posted on 2007-07-25
Last Modified: 2010-05-18
I have a table [tblROPError] that I want to update one field in.  I created an Update query that uses a SQL Passthrough query [qryROPTracking] as my data source.  I get error: Operation Must Use Updateable Query.  

Do I have to write the records from [qryROPTracking] to a table before I can update my table [tblROPError]?  Isn't there a better way without having to store records that are already being stored in SQL?  

Here is the Update Query:
UPDATE qryROPTracking INNER JOIN tblROPError ON qryROPTracking.adid = tblROPError.AdID SET tblROPError.DEUsername = qryROPTracking!username;

Please help.  I want to avoid having to write the records to a table so I don't have a delayed response that the users will feel.  
Question by:Travidia

    Author Comment

    I built the query in the query window.  When I look at the SQL, I notice that it reads: Update qryROPTracking.  In the query design, I am updating tblROPError.DEUsername.  I am not trying to update qryROPTracking - this isn't updateable.  

    I don't understand why the query is written like that.  Please explain/help me resolve.
    LVL 18

    Expert Comment

    You could try to edit the SQL, changing it to this to see if it will help:

    UPDATE  tblROPError SET tblROPError.DEUsername = qryROPTracking!username
    FROM tblROPError, qryROPTracking
    WHERE qryROPTracking.adid = tblROPError.AdID ;


    Author Comment

    that doesn't work - I get error in query expression

    Author Comment

    From the book - Access 2002 Inside and Out:

    The following queries are not updatable:

    Crosstab, SQL pass-through, and union queries
    Multiple-table queries based on three or more tables when there is a many-to-one-to-many relationship
    Any multiple-table query based on a many-to-many relationship
    Any query that contains a totals or an aggregate function or that refers to a query or subquery that contains a totals or an aggregate function
    Any query in which the UniqueValues property is set to Yes
    Any multiple-table query in which no relationship exists between the tables
    Any query in which the SQL statement includes a GROUP BY clause on the record source

    I guess my pass-trhough query needs to be qritten to a table first.  Please advise.
    LVL 6

    Accepted Solution

    Is qryROPTracking a complex query?

    I've found that certain complex queries will not update, even if it logically seems like they should.

    What I've done in this situation is turn qryROPTracking into a make table query and then link the update query to the created table. This removes the query complexity issue ...

    Author Comment

    Yeah, I was planning to do that.  The query is a SQL PassThrough query, so I will have to make a new MakeTable query based on qryROPTracking.  So now I will have two queries and a table.  3 objects instead of just one.  This isn't what I hoped for.  Seems I solved this from the start.  

    Author Comment

    Please close question as I figured it out on my own.  Thank you.
    LVL 6

    Expert Comment

    Sometimes the solutions aren't elegant ... that's why they keep coming out with new versions!

    Sorry I couldn't be of more assistance.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now