Operation must use an updateable query.

[Viswanathan S]

Hi All,

I am using OLE DB connection from VB.NET. I uses update statement having subquery, it gives error message as "Operation must use an updateable query".

The query i used is

UPDATE oecd2 SET oecd2.C_Commodity = (select MANT_COMCODEMATCH.C_Commodity from MANT_COMCODEMATCH where oecd2.C_Commodity = C_OECDLCommodity )

If i use the select statement it is execued and update statement alone also executed in Access. But when i use this subquery, it gives the above error.

How can i solve it?

 

[Wolfgang Kais]

Hello S.

I am using OLE DB connection from VB.NET. I uses update statement
having subquery, it gives error message as
"Operation must use an updateable query".

The query i used is

UPDATE oecd2 SET oecd2.C_Commodity = (select
MANT_COMCODEMATCH.C_Commodity from
MANT_COMCODEMATCH where oecd2.C_Commodity =
C_OECDLCommodity )

If i use the select statement it is execued and update statement alone
also executed in Access.
But when i use this subquery, it gives the above error.

Since Access does not support subqueries that represent a single value,
I think the oledb provider does neither. Try to use a join instead.

How can i solve it?

Try this:
Update oedc2 Inner Join MANT_COMCODEMATCH On oecd2.C_Commodity =
MANT_COMCODEMATCH.C_OECDLCommodity
Set oecd2.C_Commodity = MANT_COMCODEMATCH.C_Commodity