i am getting an error 'operation must be an updateable query in ac

[shruti]

Why am i getting an error "Operation must be an updateable query" in update
query?

 

[BruceM]

Without knowing more about the situation I defer to the error message, which
describes it pretty well. For some possible reasons why a query is
read-only:
http://allenbrowne.com/ser-61.html

 

[Maurice]

I assume you are trying to update just a table. In that case go to the SQL
view and remove the tablename in front of the fieldname ( Could look
something like this [tablename.fieldname] ) and then try again.

hth

 

[Douglas J. Steele]

What's the SQL of your query?

 

[John Spencer (MVP)]

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[shruti]

UPDATE 4_special_fees_recycling, Exception_List SET
4_special_fees_recycling.[Recycling Fee] = Exception_List.Fee
WHERE ((([4_special_fees_recycling].[Recycling Fee])<>Exception_List.Fee)
And (([4_special_fees_recycling].[P/N])=Exception_List.sku) And
(([4_special_fees_recycling].[Country Code])="81"));

 

[shruti]

UPDATE 4_special_fees_recycling, Exception_List SET
4_special_fees_recycling.[Recycling Fee] = Exception_List.Fee
WHERE ((([4_special_fees_recycling].[Recycling Fee])<>Exception_List.Fee)
And (([4_special_fees_recycling].[P/N])=Exception_List.sku) And
(([4_special_fees_recycling].[Country Code])="81"));

 

[John Spencer (MVP)]

From what you posted, I am not sure why you are getting the not updatable
message. I suspect the cartesian join might be the culprit, but that is a
guess. Also, if either of the two tables is a query then that could be the
problem.

If 4_special_fees_recycling is a query and is not updateable, then using it in
this query would generate the message.

If Exception_List is a query and it uses aggregate functions (sum etc) it may
be the cause of the problem, since you cannot use aggregate functions in an
update query.

I would try the following. Note that I set up an inner join and also
attempted to handle null values in the where clause.

UPDATE 4_special_fees_recycling INNER JOIN Exception_List
ON [4_special_fees_recycling].[P/N]=Exception_List.sku
SET 4_special_fees_recycling.[Recycling Fee] = [Exception_List].[Fee]
WHERE (
[4_special_fees_recycling].[Recycling Fee]<>Exception_List.Fee
OR ([4_special_fees_recycling].[Recycling Fee] is Null
AND Exception_List.Fee is Not Null)
OR ([4_special_fees_recycling].[Recycling Fee] is Not Null
AND Exception_List.Fee is Null)
)
And [4_special_fees_recycling].[Country Code]="81"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

UPDATE 4_special_fees_recycling, Exception_List SET
4_special_fees_recycling.[Recycling Fee] = Exception_List.Fee
WHERE ((([4_special_fees_recycling].[Recycling Fee])<>Exception_List.Fee)
And (([4_special_fees_recycling].[P/N])=Exception_List.sku) And
(([4_special_fees_recycling].[Country Code])="81"));

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

See whether this works any better:

UPDATE 4_special_fees_recycling INNER JOIN Exception_List
ON [4_special_fees_recycling].[P/N]=Exception_List.sku
SET 4_special_fees_recycling.[Recycling Fee] = Exception_List.Fee
WHERE [4_special_fees_recycling].[Recycling Fee]<>Exception_List.Fee
AND [4_special_fees_recycling].[Country Code])="81"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE 4_special_fees_recycling, Exception_List SET
4_special_fees_recycling.[Recycling Fee] = Exception_List.Fee
WHERE ((([4_special_fees_recycling].[Recycling Fee])<>Exception_List.Fee)
And (([4_special_fees_recycling].[P/N])=Exception_List.sku) And
(([4_special_fees_recycling].[Country Code])="81"));

What's the SQL of your query?

 

[a a r o n _ k e m p f]

_I_AM_CERTAIN_

Your problem is that Jet randomly craps out when you need it most.

If you want predictability-- then move to SQL Server.
With SQL Server, you can utilize 'Instead of Triggers' so that you can
_DO_ stuff instead of trying to run an update against a query.

for example.. I'm going to take a query.. and build an INSTEAD OF
trigger on it..

and instead of randomly crapping out (when you need it most) you can
write handlers so that you can update Table1 if it is columns 1,4,5
and you can update Table2 if it is columns 2,3, 6

So sorry that you've invested in a database that _RANDOMLY_CRAPS_OUT_

Grow up and learn a real database you ****ing NooB

-Aaron

 From what you posted, I am not sure why you are getting the not updatable
message.  I suspect the cartesian join might be the culprit, but that is a
guess.  Also, if either of the two tables is a query then that could bethe
problem.

If 4_special_fees_recycling is a query and is not updateable, then using it in
this query would generate the message.

If Exception_List is a query and it uses aggregate functions (sum etc) itmay
be the cause of the problem, since you cannot use aggregate functions in an
update query.

I would try the following.  Note that I set up an inner join and also
attempted to handle null values in the where clause.

UPDATE 4_special_fees_recycling INNER JOIN Exception_List
ON [4_special_fees_recycling].[P/N]=Exception_List.sku
SET 4_special_fees_recycling.[Recycling Fee] = [Exception_List].[Fee]
WHERE (
     [4_special_fees_recycling].[Recycling Fee]<>Exception_List.Fee
OR ([4_special_fees_recycling].[Recycling Fee] is Null
     AND Exception_List.Fee is Not Null)
OR ([4_special_fees_recycling].[Recycling Fee] is Not Null
     AND Exception_List.Fee is Null)
      )
And [4_special_fees_recycling].[Country Code]="81"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

UPDATE 4_special_fees_recycling, Exception_List SET
4_special_fees_recycling.[Recycling Fee] = Exception_List.Fee
WHERE ((([4_special_fees_recycling].[Recycling Fee])<>Exception_List.Fee)
And (([4_special_fees_recycling].[P/N])=Exception_List.sku) And
(([4_special_fees_recycling].[Country Code])="81"));

"John Spencer (MVP)" wrote:

- Show quoted text -

 

[shruti]

Actually, the query which i sent works in access 2000. It is not working in
access 2003 only.

From what you posted, I am not sure why you are getting the not updatable
message. I suspect the cartesian join might be the culprit, but that is a
guess. Also, if either of the two tables is a query then that could be the
problem.

If 4_special_fees_recycling is a query and is not updateable, then using it in
this query would generate the message.

If Exception_List is a query and it uses aggregate functions (sum etc) it may
be the cause of the problem, since you cannot use aggregate functions in an
update query.

I would try the following. Note that I set up an inner join and also
attempted to handle null values in the where clause.

UPDATE 4_special_fees_recycling INNER JOIN Exception_List
ON [4_special_fees_recycling].[P/N]=Exception_List.sku
SET 4_special_fees_recycling.[Recycling Fee] = [Exception_List].[Fee]
WHERE (
[4_special_fees_recycling].[Recycling Fee]<>Exception_List.Fee
OR ([4_special_fees_recycling].[Recycling Fee] is Null
AND Exception_List.Fee is Not Null)
OR ([4_special_fees_recycling].[Recycling Fee] is Not Null
AND Exception_List.Fee is Null)
)
And [4_special_fees_recycling].[Country Code]="81"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

UPDATE 4_special_fees_recycling, Exception_List SET
4_special_fees_recycling.[Recycling Fee] = Exception_List.Fee
WHERE ((([4_special_fees_recycling].[Recycling Fee])<>Exception_List.Fee)
And (([4_special_fees_recycling].[P/N])=Exception_List.sku) And
(([4_special_fees_recycling].[Country Code])="81"));

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Douglas J. Steele wrote:
What's the SQL of your query?

 

[John Spencer (MVP)]

If it works in Access 2000 and does not work in Access 2003 then I am
stumped. Perhaps someone else has an idea.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County