Travidia
asked on
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.
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.
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 ;
UPDATE tblROPError SET tblROPError.DEUsername = qryROPTracking!username
FROM tblROPError, qryROPTracking
WHERE qryROPTracking.adid = tblROPError.AdID ;
ASKER
that doesn't work - I get error in query expression
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Please close question as I figured it out on my own. Thank you.
Sometimes the solutions aren't elegant ... that's why they keep coming out with new versions!
Sorry I couldn't be of more assistance.
Sorry I couldn't be of more assistance.
ASKER
I don't understand why the query is written like that. Please explain/help me resolve.