By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 1,987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Updateable query?

P: 78
The point of this query is to look at the transactions already entered and see if new transactions have occured and if there is a need to update the amount of the transaction.

I have an update query that is running into the error: Operation must use an updateable query.

Expand|Select|Wrap|Line Numbers
  1. UPDATE M_Transactions INNER JOIN Q_UMI ON (Q_UMI.TransDate = M_Transactions.TransactionDate) AND (M_Transactions.VendorID = Q_UMI.VendorID) SET M_Transactions.Debit = [Q_UMI].[SumofTotalPurchase]
  2. WHERE (((Q_UMI.SumOfTotalPurchase)>([M_Transactions].[Debit])) AND ((M_Transactions.TransactionDate) Is Not Null));
Q_UMI is pulling of a union query, and from what I've gathered you cannot updated a union query field. However, I am trying to update a table field with a union query value. Is it as simple as you cannot use a union query at all in an update query?
Jun 2 '08 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 3,532
Queries involving Union queries are read-only. Allen Browne explains all the things that can make a query read-only here:

Linq ;0)>
Jun 2 '08 #2

P: 78
Is there any workaround other than just do individual queries rather than a union?
Jun 2 '08 #3

Expert Mod 15k+
P: 31,494
It's certainly not possible to use a UNION query for updates (as previously stated).

I'm afraid I can think of no alternative to separate UPDATE queries. A nuisance, but one we learn to live with in Access (this is not a general SQL limitation but specific to MS Jet).
Jun 2 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi. As NeoPa said, we live with the limitations of Access. I find the non-updatable-query problem really frustrating.

As an example, I developed and maintain a planning database used to plan activity for our College academic departments. It is now in its second year of use. I am frequently asked to make iterative changes to data on behalf of users, and invariably the data source I want to use as the base for the update is a summary (grouped) query that leads to a non-updatable end result.

The workround I adopt for data maintenance purposes is to take the output of the non-updatable query which identifies the rows to be updated and use this as a make-table query generating a temporary table. I then use a separate update query to join the temporary table to the one to be updated and run the update using the equi-joined tables instead of a complex query (as the joined tables approach will always be updatable).

If this has to be done frequently on the same dataset I set up the two queries to run sequentially using a named macro. This can then be run by me on demand and will always perform the sequence as specified - set up the update using temp table and then perform update. It takes longer to describe than to do.

This is not a user-oriented approach but one that simplifies maintenance tasks for people like me who do internal data manipulation unseen by the users. Performing sequential tasks using a macro avoids the overhead of designing a user interface form with a command button and coding it when a simple macro will do the job instead. For these kinds of small sequential tasks macro automation works very well indeed.

Shame we all have to adopt workrounds like this, though.

Jun 3 '08 #5

Expert Mod 15k+
P: 31,494
I didn't think of that Stewart (I tend to steer clear of Make-Tables myself - hence I'm blind to them as answers to questions). Nice work-around.

Don't forget to tidy (delete) the made table when you're finished though, as you don't want to build up a bunch of extraneous tables in your database.
Jun 4 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.