Solved

MSACCESS error: Operation must use an updateable query

Posted on 2009-04-08
2
2,714 Views
Last Modified: 2012-05-06
I am getting the error "Operation must use an updateable query."

I understand why I am getting the error but this is what I need to do.

1. I have an ODBC link to a table which holds the specific gravity (measure of weight to volume) of materials and manufactured products
2. Some materials are manufactured items (intermediates) so the SG is calculated based on the mix of all the materials within the manufactured product and then stored in the same table
3. A finished good can be comprised a mixture of purchased and manufactured products

When you update the SG of a purchased material or change the formula to make the product the SG's of intermediates and finished products are not automatically updated so I decided to write an "Update" query to maintain them .... but alas because I need to update the same table I need to calculate the results from I am not allowed.

How do I overcome this?


This is the 4 part query I have written that doesn't work in the update but gives the correct result in the 'select' view:

Query: 330-01 Formulakey Kilos and Litres
SELECT FMMAST.Formkey, Sum(FMITEM.wtorunitqty) AS Kilos, Sum([wtorunitqty]/[specgrav]) AS Litres
FROM (FMITEM INNER JOIN FMMAST ON FMITEM.Formkey = FMMAST.Formkey) INNER JOIN INMSPEC ON FMITEM.Itemkey = INMSPEC.Itemkey
WHERE (((FMITEM.Linetyp)="I"))
GROUP BY FMMAST.Formkey;



Query: 330-02 Formulakey Specific Gravity
SELECT [330-01 Formulakey Kilos and Litres].Formkey, [330-01 Formulakey Kilos and Litres].Kilos, [330-01 Formulakey Kilos and Litres].Litres, [kilos]/[litres] AS SpecificGravity
FROM [330-01 Formulakey Kilos and Litres];



Query: 330-03 Itemkey Specific Gravity
SELECT [330-02 Formulakey Specific Gravity].Formkey, INMAST.Itemkey, INMAST.Itemdescription1, [330-02 Formulakey Specific Gravity].SpecificGravity AS CalculatedSpecificGravity, INFSPEC.Itemstat, [specgrav]/[calculatedspecificgravity] AS SGRatio
FROM INMSPEC INNER JOIN ((INMAST INNER JOIN INFSPEC ON INMAST.Itemkey = INFSPEC.Itemkey) INNER JOIN [330-02 Formulakey Specific Gravity] ON INFSPEC.Formkey = [330-02 Formulakey Specific Gravity].Formkey) ON INMSPEC.Itemkey = INMAST.Itemkey
WHERE (((INMAST.Itemkey)="AAK0106") AND ((INMAST.Itemtype)="F"))
ORDER BY [330-02 Formulakey Specific Gravity].Formkey, INMAST.Itemkey;



Query: 330-04 Correct Specific Gravity
UPDATE INMSPEC INNER JOIN [330-03 Itemkey Specific Gravity] ON INMSPEC.Itemkey = [330-03 Itemkey Specific Gravity].Itemkey SET INMSPEC.Specgrav = [330-03 Itemkey Specific Gravity]![CalculatedSpecificGravity]
WHERE ((([330-03 Itemkey Specific Gravity].SGRatio)<>1));
0
Comment
Question by:Zarbs
2 Comments
 
LVL 2

Accepted Solution

by:
pskeens earned 500 total points
ID: 24104152
Create a TEMP Table and append your results to this table, then update the records in your MainTable with the records in the TEMP table, after this is complete, delete the TEMP Table.

0
 

Author Closing Comment

by:Zarbs
ID: 31568313
Such an easy solution .... thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now