Operation must use an updatable query. (Error 3073) Microsoft Access

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

Since Jet 4, all queries that have a join to a SQL statement that summarizes data will be non-updatable. You aren't using a JOIN, but the WHERE clause is exactly equivalent to a join, and thus, the Jet query optimizer treats it the same way it treats a join.

I'm afraid you're out of luck without a temp table, though maybe somebody with greater Jet SQL knowledge than I can come up with a workaround.

BTW, it might have been updatable in Jet 3.5 (Access 97), as a whole lot of queries were updatable then that became non-updatable when upgraded to Jet 4.

--
David W. Fenton
David Fenton Associates

Answer 2

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

[http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Produc][1]

[1]: http://ACC: Update Query Based on Totals Query Fails

Answer 3

The problem defintely relates to the use of (in this case) the max() function. Any aggregation function used during a join (e.g. to retrieve the max or min or avg value from a joined table) will cause the error. And the same applies to using subqueries instead of joins (as in the original code).

This is incredibly annoying (and unjustified!) as it is a reasonably common thing to want to do. I've also had to use temp tables to get around it (pull the aggregated value into a temp table with an insert statement, then join to this table with your update, then drop the temp table).

Glenn

Answer 4

I kept getting the same error until I made the connecting field a unique index in both connecting tables. Only then did the query become updatable.

Philip Stilianos

Answer 5

Even more maddening -- i have an update query the runs fine using linked tables from a SQL Server database. THE SAME QUERY running against an exact copy of the SAME DATABASE returns "Operation must use an updatable query" -- AARGH!

No workaround found yet.

Answer 6

(A little late to the party...)

The three ways I've gotten around this problem in the past are:

1. Reference a text box on an open form
2. DSum
3. DLookup