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


missinglinq
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:

http://allenbrowne.com/ser-61.html

Linq ;0)>
Jun 2 '08 #2

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

NeoPa
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.

-Stewart
Jun 3 '08 #5

NeoPa
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.

@OP:
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.