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 kept getting the same error, but all SQLs execute in Access very well.

and when I amended the permission of AccessFile.

the problem fixed!!

I give 'Network Service' account full control permission, this account if for IIS

Answer 2

To further answer what DRUA referred to in his/her answer...

I develop my databases in Access 2007. My users are using access 2007 runtime. They have read permissions to a database_Front (front end) folder, and read/write permissions to the database_Back folder.

In rolling out a new database, the user did not follow the full instructions of copying the front end to their computer, and instead created a shortcut. Running the Front-end through the shortcut will create a condition where the query is not updateable because of the file write restrictions.

Copying the front end to their documents folder solves the problem.

Yes, it complicates things when the users have to get an updated version of the front-end, but at least the query works without having to resort to temp tables and such.

Answer 3

(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

Answer 4

When I got this error, it may have been because of my UPDATE syntax being wrong, but after I fixed the update query I got the same error again...so I went to the ODBC Data Source Administrator and found that my connection was read-only. After I made the connection read-write and re-connected it worked just fine.

Answer 5

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 6

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