操作必须使用可更新的查询.(错误 3073)Microsoft Access [英] Operation must use an updatable query. (Error 3073) Microsoft Access
问题描述
在某些 Microsoft Access 查询中,我收到以下消息:操作必须使用可更新的查询.(错误 3073).我通过使用临时表来解决它,但我想知道是否有更好的方法.所有涉及的表都有一个主键.代码如下:
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;
推荐答案
从 Jet 4 开始,所有连接到汇总数据的 SQL 语句的查询都将是不可更新的.您没有使用 JOIN,但 WHERE 子句完全等同于连接,因此,Jet 查询优化器以与处理连接相同的方式处理它.
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.
如果没有临时表,恐怕您会走运,尽管也许有人比我更了解 Jet SQL 可以想出解决方法.
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.
顺便说一句,它可能在 Jet 3.5 (Access 97) 中是可更新的,因为很多查询都是可更新的,然后在升级到 Jet 4 时变得不可更新.
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.
--
这篇关于操作必须使用可更新的查询.(错误 3073)Microsoft Access的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!