Operation must use an updatable query. (Error 3073

Question

On some Microsoft Access queries, I get the following message: Operation must use an updatable query. (Error 3073). I work around it by using temporary tables, but I'm wondering if there's a better way. All the tables involved have a primary key. Here's the code:

UPDATE CLOG SET CLOG.NEXTDUE = (
    SELECT H1.paidthru 
    FROM CTRHIST as H1
    WHERE H1.ACCT = clog.ACCT AND
    H1.SEQNO = (
        SELECT MAX(SEQNO) 
        FROM CTRHIST 
        WHERE CTRHIST.ACCT = Clog.ACCT AND 
        CTRHIST.AMTPAID > 0 AND
        CTRHIST.DATEPAID < CLOG.UPDATED_ON
    )
)
WHERE CLOG.NEXTDUE IS NULL;

Answer 1

I know my answer is 7 years late, but here's my suggestion anyway:

When Access complains about an UPDATE query that includes a JOIN, just save the query, with RecordsetType property set to Dynaset (Inconsistent Updates).

This will sometimes allow the UPDATE to work.

Answer 2

I would try building the UPDATE query in Access. I had an UPDATE query I wrote myself like

UPDATE TABLE1
SET Field1 = 
(SELECT Table2.Field2
 FROM Table2
 WHERE Table2.UniqueIDColumn = Table1.UniqueIDColumn)

The query gave me that error you're seeing. This worked on my SQL Server though, but just like earlier answers noted, Access UPDATE syntax isn't standard syntax. However, when I rebuilt it using Access's query wizard (it used the JOIN syntax) it worked fine. Normally I'd just make the UPDATE query a passthrough to use the non-JET syntax, but one of the tables I was joining with was a local Access table.

Answer 3

I had a similar problem where the following queries wouldn't work;

update tbl_Lot_Valuation_Details as LVD
set LVD.LGAName = (select LGA.LGA_NAME from tbl_Prop_LGA as LGA where LGA.LGA_CODE = LVD.LGCode)
where LVD.LGAName is null;

update tbl_LOT_VALUATION_DETAILS inner join tbl_prop_LGA on tbl_LOT_VALUATION_DETAILS.LGCode = tbl_prop_LGA.LGA_CODE 
set tbl_LOT_VALUATION_DETAILS.LGAName = [tbl_Prop_LGA].[LGA_NAME]
where tbl_LOT_VALUATION_DETAILS.LGAName is null;

However using DLookup resolved the problem;

update tbl_Lot_Valuation_Details as LVD
set LVD.LGAName = dlookup("LGA_NAME", "tbl_Prop_LGA", "LGA_CODE="+LVD.LGCode)
where LVD.LGAName is null;

This solution was originally proposed at https://stackoverflow.com/questions/537161/sql-update-woes-in-ms-access-operation-must-use-an-updateable-query

Answer 4

There is another scenario here that would apply. A file that was checked out of Visual Source Safe, for anyone still using it, that was not given "Writeablity", either in the View option or Check Out, will also recieve this error message.

Solution is to re-acquire the file from Source Safe and apply the Writeability setting.

Answer 5

In essence, while your SQL looks perfectly reasonable, Jet has never supported the SQL standard syntax for UPDATE. Instead, it uses its own proprietary syntax (different again from SQL Server's proprietary UPDATE syntax) which is very limited. Often, the only workarounds "Operation must use an updatable query" are very painful. Seriously consider switching to a more capable SQL product.

For some more details about your specific problems and some possible workarounds, see Update Query Based on Totals Query Fails.

Answer 6

This occurs when there is not a UNIQUE MS-ACCESS key for the table(s) being updated. (Regardless of the SQL schema).

When creating MS-Access Links to SQL tables, you are asked to specify the index (key) at link time. If this is done incorrectly, or not at all, the query against the linked table is not updatable

When linking SQL tables into Access MAKE SURE that when Access prompts you for the index (key) you use exactly what SQL uses to avoid problem(s), although specifying any unique key is all Access needs to update the table.

If you were not the person who originally linked the table, delete the linked table from MS-ACCESS (the link only gets deleted) and re-link it specifying the key properly and all will work correctly.