asked on

Access Message: Operation must use an updateable Query

Objective: Update table dbo_LEASEBUT4 based on a value in table dbo_LEASEBUT7
The following Syntax::
update dbo_LEASEBUT4 set dbo_LeaseBut4.leasid = (select dbo_LeaseBut7.leasid from dbo_Leasebut7 where dbo_LeaseBut7.HCode=dbo_LeaseBut4.HCode) where Exists(select * from dbo_LeaseBut7 where dbo_LeaseBut7.HCode=dbo_LeaseBut4.HCode)
Result: Returns the following error: "Operation must use an Updateable Query"

Note 1:
To test/eliminate the issue of the table being "updatable", the following ran successfully:
update dbo_LeaseBut4 set dbo_LeaseBut4.leasid = 100011
Result: All rows updated with 100011 as expected

Note 2:
Further testing, the following ran successfully:
update dbo_LeaseBut4 set dbo_LeaseBut4.leasid = dbo_Leasebut4.tfinrepsup
Result: All rows updated with the value from field dbo_Leasebut4.tfinrepsup

Note 3: Other info (for what it's worth):
No queries exist (Only tables)
I do not believe the tables have groups, sums, etc. I checked/double checked.
They are Access tables (imported from MS-SQL DB). The Access tables are no longer linked.
The update syntax is from Experts-Exchange solution 2/4/2005; BigSchmuh; Solution ID: 13225084

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

you can also use inner join

update dbo_LEASEBUT4 INNER join dbo_LeaseBut7
on dbo_LEASEBUT4.HCode = dbo_LeaseBut7.HCode
set dbo_LEASEBUT4.leasid = [dbo_LeaseBut7].[leasid]