操作必须使用可更新的查询。（错误3073）Microsoft Access

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:

在某些Microsoft Access查询中,我收到以下消息:操作必须使用可更新的查询。 (错误3073)。我通过使用临时表来解决它,但我想知道是否有更好的方法。所有涉及的表都有一个主键。这是代码:

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;

21 个解决方案

#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.

从Jet 4开始,所有连接到汇总数据的SQL语句的查询都是不可更新的。您没有使用JOIN,但WHERE子句完全等同于连接,因此,Jet查询优化器将其视为处理连接的方式。

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 SQL知识而不是我可以提出一个解决方法。

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.

顺便说一句,它可能在Jet 3.5(Access 97)中可以更新,因为很多查询都是可更新的,然后在升级到Jet 4时变得不可更新。

#2

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;

但是使用DLookup解决了这个问题;

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://*.com/questions/537161/sql-update-woes-in-ms-access-operation-must-use-an-updateable-query

该解决方案最初是在https://*.com/questions/537161/sql-update-woes-in-ms-access-operation-must-use-an-updateable-query提出的。

#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).

该问题与使用(在这种情况下)max()函数有关。在连接期间使用的任何聚合函数(例如,从连接表中检索max或min或avg值)将导致错误。这同样适用于使用子查询而不是连接(如原始代码中所示)。

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).

这是令人难以置信的烦恼(并且没有道理!),因为它是一个相当普遍的想要做的事情。我还必须使用临时表来解决它(使用insert语句将聚合值拉入临时表,然后使用您的更新连接到此表,然后删除临时表)。

Glenn

#4

There is no error in the code. But the error is Thrown because of the following reason.

代码中没有错误。但由于以下原因,错误被抛出。

 - Please check weather you have given Read-write permission to MS-Access database file.

 - The Database file where it is stored (say in Folder1) is read-only..?

suppose you are stored the database (MS-Access file) in read only folder, while running your application the connection is not force-fully opened. Hence change the file permission / its containing folder permission like in C:\Program files all most all c drive files been set read-only so changing this permission solves this Problem.

假设您将数据库(MS-Access文件)存储在只读文件夹中,而在运行应用程序时,连接不会强制完全打开。因此,更改文件权限/其包含文件夹权限,如在C:\ Program文件中所有大多数c驱动器文件都设置为只读,因此更改此权限可以解决此问题。

#5

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

我知道我的答案已经晚了7年,但无论如何,这是我的建议:

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

当Access抱怨包含JOIN的UPDATE查询时,只需保存查询,并将RecordsetType属性设置为Dynaset(Inconsistent Updates)。

This will sometimes allow the UPDATE to work.

这有时会允许UPDATE工作。

#6

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

我会尝试在Access中构建UPDATE查询。我有一个我自己写的UPDATE查询

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.

查询给了我你看到的错误。这虽然适用于我的SQL Server,但就像前面提到的答案一样,Access UPDATE语法不是标准语法。但是,当我使用Access的查询向导(它使用JOIN语法)重建它时,它工作正常。通常我只是使UPDATE查询成为使用非JET语法的直通,但我加入的其中一个表是本地Access表。

#7

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

当没有更新的表的唯一MS-ACCESS键时,会发生这种情况。 (无论SQL架构如何)。

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

创建SQL表的MS-Access链接时,系统会要求您在链接时指定索引(键)。如果这样做不正确,或者根本不完成,则对链接表的查询不可更新

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.

将SQL表链接到Access时确保当Access提示您输入索引(键)时,您正好使用SQL用来避免问题,尽管指定任何唯一键都是Access需要更新表。

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.

如果您不是最初链接表的人,请从MS-ACCESS中删除链接表(该链接仅被删除)并重新链接它,正确指定密钥,所有这些都将正常工作。

#8

(A little late to the party...)

(派对有点晚......)

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

我过去解决这个问题的三种方法是:

Reference a text box on an open form

引用打开表单上的文本框

DSum
DLookup

#9

I had the same issue.

我遇到过同样的问题。

My solution is to first create a table from the non updatable query and then do the update from table to table and it works.

我的解决方案是首先从不可更新的查询创建一个表,然后从表到表进行更新,它的工作原理。

#10

Mine failed with a simple INSERT statement. Fixed by starting the application with 'Run as Administrator' access.

我的失败是一个简单的INSERT语句。通过以“以管理员身份运行”访问权限启动应用程序来修复

#11

MS Access - joining tables in an update query... how to make it updatable

MS Access - 在更新查询中连接表...如何使其可更新

Open the query in design view

在设计视图中打开查询

Click once on the link b/w tables/view

单击链接b / w表/视图一次

In the “properties” window, change the value for “unique records” to “yes”

在“属性”窗口中,将“唯一记录”的值更改为“是”

Save the query as an update query and run it.

将查询保存为更新查询并运行它。

#12

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

#13

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.

实质上,虽然您的SQL看起来非常合理,但Jet从未支持UPDATE的SQL标准语法。相反,它使用自己的专有语法(与SQL Server专有的UPDATE语法不同),这是非常有限的。通常,唯一的解决方法“操作必须使用可更新的查询”非常痛苦。认真考虑切换到功能更强大的SQL产品。

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

有关特定问题和一些可能的解决方法的更多详细信息,请参阅基于总计查询失败的更新查询。

#14

I kept getting the same error, but all SQLs execute in Access very well.

我一直得到同样的错误,但所有SQL都在Access中执行得非常好。

and when I amended the permission of AccessFile.

当我修改AccessFile的权限时。

the problem fixed!!

问题解决!!

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

我给'网络服务'帐户完全控制权限,这个帐户是否适用于IIS

#15

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.

当我收到此错误时,可能是因为我的UPDATE语法错误,但在我修复了更新查询之后我又得到了同样的错误...所以我去了ODBC数据源管理员,发现我的连接被读了-只要。在我连接读写并重新连接后,它工作得很好。

#16

Today in my MS-Access 2003 with an ODBC tabla pointing to a SQL Server 2000 with sa password gave me the same error.
I defined a Primary Key on the table in the SQL Server database, and the issue was gone.

今天在我的MS-Access 2003中使用指向带有sa密码的SQL Server 2000的ODBC tabla给了我同样的错误。我在SQL Server数据库的表上定义了一个主键,问题就消失了。

#17

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.

这里还有另一种情况可以适用。对于仍在使用它的人,在View选项或Check Out中没有给出“Writeablity”的文件已经从Visual Source Safe检出,也会收到此错误消息。

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

解决方案是从Source Safe重新获取文件并应用可写性设置。

#18

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

为了进一步回答DRUA在他/她的回答中提到的内容......

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.

我在Access 2007中开发我的数据库。我的用户正在使用Access 2007运行时。他们具有对database_Front(前端)文件夹的读权限,以及对database_Back文件夹的读/写权限。

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.

是的,当用户必须获得前端的更新版本时,它会使事情变得复杂,但至少查询工作而不必求助于临时表等。

#19

check your DB (Database permission) and give full permission

检查您的数据库(数据库权限)并授予完全权限

Go to DB folder-> right click properties->security->edit-> give full control & Start menu ->run->type "uac" make it down (if it high)

转到DB文件夹 - >右键单击属性 - >安全 - >编辑 - >给出完全控制和开始菜单 - >运行 - >键入“uac”使其降低(如果它高)

#20

You can always write the code in VBA that updates similarly. I had this problem too, and my workaround was making a select query, with all the joins, that had all the data I was looking for to be able to update, making that a recordset and running the update query repeatedly as an update query of only the updating table, only searching the criteria you're looking for

您始终可以在VBA中编写类似更新的代码。我也有这个问题,我的解决方法是使用所有连接进行选择查询,其中包含我正在寻找的所有数据都能够更新,使得记录集和重复运行更新查询作为更新查询只有更新表,只搜索您正在寻找的标准

    Dim updatingItems As Recordset
    Dim clientName As String
    Dim tableID As String
    Set updatingItems = CurrentDb.OpenRecordset("*insert SELECT SQL here*");", dbOpenDynaset)
    Do Until updatingItems .EOF
        clientName = updatingItems .Fields("strName")
        tableID = updatingItems .Fields("ID")
        DoCmd.RunSQL "UPDATE *ONLY TABLE TO UPDATE* SET *TABLE*.strClientName= '" & clientName & "' WHERE (((*TABLE*.ID)=" & tableID & "))"
        updatingItems.MoveNext
    Loop

I'm only doing this to about 60 records a day, doing it to a few thousand could take much longer, as the query is running from start to finish multiple times, instead of just selecting an overall group and making changes. You might need ' ' around the quotes for tableID, as it's a string, but I'm pretty sure this is what worked for me.

我每天只对大约60条记录执行此操作,由于查询从头到尾多次运行,而不是仅选择整个组并进行更改,因此将其执行到几千条可能需要更长时间。你可能需要围绕tableID的引号,因为它是一个字符串,但我很确定这对我有用。

#21

The answer given above by iDevlop worked for me. Note that I wasn't able to find the RecordsetType property in my update query. However, I was able to find that property by changing my query to a select query, setting that property as iDevlop noted and then changing my query to an update query. This worked, no need for a temp table.

iDevlop上面给出的答案对我有用。请注意,我无法在更新查询中找到RecordsetType属性。但是,通过将查询更改为选择查询,将该属性设置为iDevlop注释,然后将查询更改为更新查询,我能够找到该属性。这很有效,不需要临时表。

I'd have liked for this to just be a comment to what iDevlop posted so that it flowed from his solution, but I don't have a high enough score.

我喜欢这个只是对iDevlop发布的内容的评论,以便它从他的解决方案中流出,但我没有足够高的分数。

#1