Operation must use an updateable query - Access 2010

[mpov]

Hi all,

I would appreciate any help you can provide with this.

I have an update query that was working fine on my local machine (Access 2003) with one of the tables being linked via an ODBC connection.

This process was just moved over to a server on our network (Access 2010). I believe I have all the settings set properly so I don't believe this to be a read only issue.

Below is the sql I am using but I will describe what I am trying to do...

Table 1 (tCP_DTL) is a local table where I am trying to update the fields STD_COST_FLAG & STD_COST_MOD.

There are no fields to join directly so I am taking 8 characters from field EO11 in tCP_DTL and matching to the field EO8 in the 2nd table (DM_RDY_LU_MRCS_BY_EO8 using a where clause.

I then have if statements within the updates to update the STD_COST_FLAG with either an 'N' or 'Y' and the STD_COST_MOD with either a 0 or some value >0.

There are 2 other filters used on fields within tCP_DTL

UPDATE tCP_DTL, DM_RDY_LU_MRCS_BY_E08 SET tCP_DTL.STD_COST_FLAG = IIf([RESALE_MRC]>0,"Y","N"), tCP_DTL.STD_COST_MOD = IIf([RESALE_MRC] Is Null,0,[RESALE_MRC])
WHERE (((tCP_DTL.LINE_TYPE)="RSL") AND ((tCP_DTL.ANI) Is Not Null) AND ((Left([EO11],8))=[E08]));

Because this was running fine previously I am not sure if there are properties within the query, tables, etc that need to be changed, Access 2010 is causing the problem, or something else with the query.

I can also manually change the values for the 2 fields in tCP_DTL.

Thanks in advance for any assistance.
Mike

 

[dhookom]

I find it difficult to understand how this ever worked in Access since JET is very picky about primary and foreign keys being used in a join for any updateable query. Are you sure it worked before? Can you edit the records if the query is changed to a select query?

Duane
Hook'D on Access
MS Access MVP

 

[mpov]

Yes, it was part of a longer process that was run monthly and never failed. This was over a year ago and we resurrected this process. I know IT has made some changes in most of the Data warehouse tables (DM_RDY_LU_MRCS_BY_EO8 may have been one of those).

I cannot update the values when changed to a select query.

I created a work around but if there is anything else I can do, please let me know.

Thanks
Mike

 

[dhookom]

Luke Chung of FMS Inc has an article Dealing with Non-Updateable Microsoft Access Queries and the Use of Temporary Tables.

Duane
Hook'D on Access
MS Access MVP

 

[mpov]

I have reworked the queries to use temp tables.

Still not sure why it is not working any longer but it works this way.

Thanks