Operation Must Use Updateable Query

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.  
TravidiaAsked:
Who is Participating?
 
mmconsultantOwnerCommented:
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 ...
0
 
TravidiaAuthor Commented:
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.
0
 
mdouganCommented:
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 ;

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TravidiaAuthor Commented:
that doesn't work - I get error in query expression
0
 
TravidiaAuthor Commented:
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.
0
 
TravidiaAuthor Commented:
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.  
0
 
TravidiaAuthor Commented:
Please close question as I figured it out on my own.  Thank you.
0
 
mmconsultantOwnerCommented:
Sometimes the solutions aren't elegant ... that's why they keep coming out with new versions!

Sorry I couldn't be of more assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.