Update Query for an Oracle Table

[sxschech]

I created an update query that updates data to an Oracle Table and works fine (this query was created in Access 2003). I created a different update query (now that I've been upgraded, this query was created in Access 2007, mdb format) and this one doesn't work "Operation must use an updateable query". I can do a select * on the oracle table from Access and manually update the data from within the query, but when I try change via the update query it doesn't like it. When I created the query, it automatically joined the field that had a key icon next to it to the field with the same name in the query containing the data to be used for the update, any thoughts on what else I might be missing? Data for both tables are linked in from Oracle. qryCUTOFF_HIST_GR_HISTORY is a select off of the oracle table SIPROD_CUTOFF_GR_HIST and only reason doing it this way is needed to filter the data that I want updated.

UPDATE SIPROD_CUTOFF_GR INNER JOIN qryCUTOFF_HIST_GR_HISTORY ON SIPROD_CUTOFF_GR.SORTORDER = qryCUTOFF_HIST_GR_HISTORY.sortorder SET SIPROD_CUTOFF_GR.RPT_YEAR = [qryCUTOFF_HIST_GR_HISTORY].[RPT_YEAR], SIPROD_CUTOFF_GR.CUTOFF_DT = [qryCUTOFF_HIST_GR_HISTORY].[CUTOFF_DT];

 

[sxschech]

I gave up on the direct approach. Ended up creating a local table putting the key on the local table, then run a delete query and an update query to repopulate. Then changed the above query to use the local table instead of the query and was subsequently able to execute the update query. Seems like a lot of extra steps considering that both oracle tables had a key and did the autojoin within access in the query design view, but at least it works.

 

[sxschech]

Oops used the wrong word. Meant to say append rather than update...

then run a delete query and an [red]update[/red] query to repopulate

should be

then run a delete query and an [red]append[/red] query to repopulate

 

[PHV]

What is the SQL code of qryCUTOFF_HIST_GR_HISTORY ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sxschech]

Here is the sql:

SELECT DISTINCT SIPROD_CUTOFF_GR_HIST.RPT_YEAR, SIPROD_CUTOFF_GR_HIST.CUTOFF_DT, SIPROD_CUTOFF_GR_HIST.report_ind, SIPROD_CUTOFF_GR_HIST.weekno, SIPROD_CUTOFF_GR_HIST.sortorder, SIPROD_CUTOFF_GR_HIST.yrwk
FROM SIPROD_CUTOFF_GR_HIST
WHERE (((SIPROD_CUTOFF_GR_HIST.WEEKOF)=[forms]![frmMain].[cbohistory]))
ORDER BY SIPROD_CUTOFF_GR_HIST.RPT_YEAR DESC , SIPROD_CUTOFF_GR_HIST.sortorder;

SIPROD_CUTOFF_GR_HIST is an ODBC sql pass through query

SELECT *
 FROM SIPROD.CUTOFF_GR_HIST

Should it be linked in as a table instead?

 

[PHV]

What about this ?

UPDATE SIPROD_CUTOFF_GR G INNER JOIN SIPROD_CUTOFF_GR_HIST H ON G.SORTORDER = H.sortorder
SET G.RPT_YEAR = H.RPT_YEAR, G.CUTOFF_DT = H.CUTOFF_DT
WHERE H.WEEKOF=[Forms]![frmMain].[cbohistory]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sxschech]

I tried your suggestion and still showing run time error 3073 Operation must use an updateable query.

After that I tried directly linking the table and using the linked table instead of the pass through query and that seems to work, so I guess the issue was that I couldn't run the update joining to a pass through query.