vba 操作必须使用可更新的查询。（错误 3073）Microsoft Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1231350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Operation must use an updatable query. (Error 3073) Microsoft Access
提问by
I have written this query:
我写了这个查询：
UPDATE tbl_stock1 SET
tbl_stock1.weight1 = (
select (b.weight1 - c.weight_in_gram) as temp
from
tbl_stock1 as b,
tbl_sales_item as c
where
b.item_submodel_id = c.item_submodel_id
and b.item_submodel_id = tbl_stock1.item_submodel_id
and b.status <> 'D'
and c.status <> 'D'
),
tbl_stock1.qty1 = (
select (b.qty1 - c.qty) as temp1
from
tbl_stock1 as b,
tbl_sales_item as c
where
b.item_submodel_id = c.item_submodel_id
and b.item_submodel_id = tbl_stock1.item_submodel_id
and b.status <> 'D'
and c.status <> 'D'
)
WHERE
tbl_stock1.item_submodel_id = 'ISUBM/1'
and tbl_stock1.status <> 'D';
I got this error message:
我收到此错误消息：
Operation must use an updatable query. (Error 3073) Microsoft Access
But if I run the same query in SQL Server it will be executed.
但是如果我在 SQL Server 中运行相同的查询，它将被执行。
Thanks, dinesh
谢谢，迪内什
回答by Ben Griswold
I'm quite sure the JET DB Engine treats any query with a subquery as non-updateable. This is most likely the reason for the error and, thus, you'll need to rework the logic and avoid the subqueries.
我很确定 JET 数据库引擎将任何带有子查询的查询视为不可更新。这很可能是错误的原因，因此，您需要重新设计逻辑并避免子查询。
As a test, you might also try to remove the calculation (the subtraction) being performed in each of the two subqueries. This calculation may not be playing nicely with the update as well.
作为测试，您还可以尝试删除在两个子查询中的每一个中执行的计算（减法）。此计算也可能无法很好地与更新配合使用。
回答by onedaywhen
Consider this very simple UPDATE statement using Northwind:
考虑使用 Northwind 的这个非常简单的 UPDATE 语句：
UPDATE Categories
SET Description = (
SELECT DISTINCT 'Anything'
FROM Employees
);
It fails with the error 'Operation must use an updateable query'.
它因错误“操作必须使用可更新查询”而失败。
The Access database engine simple does not support the SQL-92 syntax using a scalar subquery in the SETclause.
Access 数据库引擎 simple 不支持在SET子句中使用标量子查询的 SQL-92 语法。
The Access database engine has its own proprietary UPDATE..JOIN..SETsyntax but is unsafe because, unlike a scalar subquery, it doesn't require values to be unambiguous. If values are ambiguous then the engine silent 'picks' one arbitrarily and it is hard (if not impossible) to predict which one will be applied even if you were aware of the problem.
Access 数据库引擎有自己的专有UPDATE..JOIN..SET语法，但不安全，因为与标量子查询不同，它不需要明确的值。如果值不明确，那么引擎会随意“选择”一个，即使您知道问题，也很难（如果不是不可能）预测将应用哪个。
For example, consider the existing Categoriestable in Northwind and the following daft (non-)table as a target for an update (daft but simple to demonstrate the problem clearly):
例如，考虑CategoriesNorthwind 中的现有表和以下愚蠢的（非）表作为更新的目标（愚蠢但简单以清楚地演示问题）：
CREATE TABLE BadCategories
(
CategoryID INTEGER NOT NULL,
CategoryName NVARCHAR(15) NOT NULL
)
;
INSERT INTO BadCategories (CategoryID, CategoryName)
VALUES (1, 'This one...?')
;
INSERT INTO BadCategories (CategoryID, CategoryName)
VALUES (1, '...or this one?')
;
Now for the UPDATE:
现在为UPDATE：
UPDATE Categories
INNER JOIN (
SELECT T1.CategoryID, T1.CategoryName
FROM Categories AS T1
UNION ALL
SELECT 9 - T2.CategoryID, T2.CategoryName
FROM Categories AS T2
) AS DT1
ON DT1.CategoryID = Categories.CategoryID
SET Categories.CategoryName = DT1.CategoryName;
When I run this I'm told that two rows have been updated, funny because there's only one matching row in the Categories table. The result is that the Categoriestable with CategoryIDnow has the '...or this one?' value. I suspect it has been a race to see which value gets written to the table last.
当我运行它时，我被告知有两行已更新，这很有趣，因为 Categories 表中只有一个匹配的行。结果是现在的Categories表CategoryID有“...还是这个？” 价值。我怀疑查看哪个值最后写入表一直是一场竞赛。
The SQL-92 scalar subquery is verbose when there are multiple clauses in the SETand/or the WHERE clause matches the SET's clauses but at least it eliminates ambiguity (plus a decent optimizer should be able to detects that the subqueries are close matches). The SQL-99 Standard introduced MERGEwhich can be used to eliminate the aforementioned repetition but needless to say Access doesn't support that either.
当SET和/或 WHERE 子句与SET's子句匹配时，SQL-92 标量子查询是冗长的，但至少它消除了歧义（加上一个不错的优化器应该能够检测到子查询是否接近匹配）。引入的 SQL-99 标准MERGE可用于消除上述重复，但不用说 Access 也不支持。
The Access database engine's lack of support for the SQL-92 scalar subquery syntax is for me its worst 'design feature' (read 'bug').
Access 数据库引擎缺乏对 SQL-92 标量子查询语法的支持对我来说是它最糟糕的“设计特性”（阅读“错误”）。
Also note the Access database engine's proprietary UPDATE..JOIN..SETsyntax cannot anyhow be used with set functions ('totals queries' in Access-speak). See Update Query Based on Totals Query Fails.
另请注意，Access 数据库引擎的专有UPDATE..JOIN..SET语法无论如何不能与集合函数（Access 说的“总计查询”）一起使用。请参阅基于总计查询失败的更新查询。
回答by Scott Wilbur
Keep in mind that if you copy over a query that originally had queries or summary queries as part of the query, even though you delete those queries and only have linked tables, the query will (mistakenly) act like it still has non-updateable fields and will give you this error. You just simply re-create the query as you want it but it is an insidious little glitch.
请记住，如果您复制最初将查询或汇总查询作为查询的一部分的查询，即使您删除了这些查询并且只有链接表，该查询也会（错误地）表现得好像它仍然具有不可更新的字段并且会给你这个错误。您只需根据需要重新创建查询，但这是一个阴险的小故障。
回答by BIBD
You are updating weight1and qty1with values that are in turn derived from weight1and qty1(respectively). That's why MS-Access is choking on the update. It's probably also doing some optimisation in the background.
您正在更新weight1andqty1的值，这些值依次来自weight1和qty1（分别）。这就是 MS-Access 在更新时窒息的原因。它可能也在后台做一些优化。
The way I would get around this is to dump the calculations into a temporary table, and then update the first table from the temporary table.
我解决这个问题的方法是将计算转储到临时表中，然后从临时表更新第一个表。
回答by Mark Macneil Bikeio
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..?
- 请检查天气您已授予对 MS-Access 数据库文件的读写权限。
- 存储它的数据库文件（比如在 Folder1 中）是只读的..？
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 filesall most all c drive files been set read-onlyso changing this permission solves this Problem.
假设您将数据库（MS-Access 文件）存储在只读文件夹中，但在运行应用程序时，连接并未强制完全打开。因此，更改文件权限/其包含的文件夹权限，就像在C:\Program files所有大多数 c 驱动器文件中都设置为只读一样，因此更改此权限可以解决此问题。
回答by Patrick Honorez
In the query properties, try changing the Recordset Type to Dynaset (Inconsistent Updates)
在查询属性中，尝试将 Recordset Type 更改为Dynaset (Inconsistent Updates)
