Operation Must Use Updateable Query

Posted on 2007-07-25
Medium Priority
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
  • 5
  • 2

Author Comment

ID: 19570001
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

ID: 19570122
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

ID: 19570140
that doesn't work - I get error in query expression
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 19570470
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.

Accepted Solution

mmconsultant earned 1500 total points
ID: 19570487
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

ID: 19570504
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

ID: 19570522
Please close question as I figured it out on my own.  Thank you.

Expert Comment

ID: 19570988
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

840 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