473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Updateable query?

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
5 3319
missinglinq
3,532 Expert 2GB
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
kpfunf
78
missinglinq,
Is there any workaround other than just do individual queries rather than a union?
Jun 2 '08 #3
NeoPa
32,556 Expert Mod 16PB
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
Stewart Ross
2,545 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

8
by: Tom wilson | last post by:
This is driving me nuts. I'm trying to update an Excel spreadsheet using ADO.Net and Oledb in VB.Net. The connection is open, the adapter is connected and the dataset is loaded. Here's the code...
4
by: Steve | last post by:
I have a products table where the PK is ProductID. Also have the standard Orders table and OrderDetails table. I created a query that joins the Orders table and OrderDetails table. The query...
3
by: Ray | last post by:
I have below query in Access 97 but it is not updateable by entering or editing data in the query. Can someone advise how to modify it to be updateable one. SELECT .PCode, .Model, .Description,...
1
by: Edward | last post by:
I've recently migrated the back end of one of my client's applications from Access to SQL Server. One of the forms is based on an Access query thus: SELECT dbo_tblDistributionDetail.*,...
4
by: Jim in Arizona | last post by:
Continuing my lessons out of a book, I ran into a problem when trying for the first time to update a datastore (access database in this case). My Code: Private Sub Page_Load(ByVal sender As...
5
by: Web Search Store | last post by:
I'm getting this error on my web page: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Operation must use an updateable query. /searchweb33.asp, line 5014 Here is the...
8
by: Jim in Arizona | last post by:
I've been using an example out of a book to be able to edit the rows in a database. I am getting the following error: ========================================================...
11
by: Arpan | last post by:
I have always been working with SQL Server 2005 for ASP.NET apps but due to some reasons, had to revert back to MS-Access 2000. When I try to insert/update a MS-Access DB table (MDB), ASP.NET...
1
by: pavya | last post by:
Hi, I have developed one Web application. At that time my system had a FAT file system on it and this application worked properly. But now i have converted FAT file system to NTFS file system and...
2
by: jghouse | last post by:
Everyone, Hopefully you can help me with a little problem I am having. I have a need to limit the records shown in a form by a few different criteria. I also need these records to be editable....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.