Operation must use an updatable query

Question

I am trying to update a column in one table to set its value to the count of records in another table. This produces the error:

Operation must use an updateable query.

Query:

UPDATE Tracking SET BatchCount = (Select Count(*) from Batch)
WHERE ReportingDate=Date();

It seems Access does not like the Select Count(*) from Batch sub-query. If I replace it with a literal value, it works fine.

Any suggestions to resolve this issue is much appreciated.

Answer 1

Unfortunately this is an inherent restriction of the JET database engine used by MS Access: no part of an update query may use aggregation, else the resulting recordset is not updateable.

---

There are a couple of workarounds:

You can use a domain aggregate function, such as DCount:

update tracking set batchcount = dcount("*", "Batch")
where reportingdate = date();

Alternatively, you can use a temporary table to store the result of the count, and then update the records using the value held in the table, e.g.

select count(*) as cnt into temptable from batch

update tracking, temptable set tracking.batchcount = temptable.cnt
where tracking.reportingdate = date();