“操作必须使用可更新的查询"用于对 Access 中的 SQL 链接表进行 UPDATE 查询 [英] "operation must use an updateable query" for UPDATE query on SQL linked table in Access

问题描述

在我的 Access 数据库中，我有一个临时表 - msCurrentAnnualUpload 和一个链接到 SQL 服务器表 - msCurrent.

In my Access database I have one temp table - msCurrentAnnualUpload and one linked to SQL server table - msCurrent.

在我尝试运行更新查询后，出现错误(操作必须使用可更新的查询").

After I try to run Update query I get an error ("operation must use an updateable query").

查询文本如下:

UPDATE msCurrent 

INNER JOIN msCurrentAnnualUpload ON (msCurrent.ProductID = msCurrentAnnualUpload.ProductID) AND (msCurrent.CountryCodeID = msCurrentAnnualUpload.CountryCodeID) 

SET 
msCurrent.Y1977 = msCurrentAnnualUpload.Y1977, 
msCurrent.Y1978 = msCurrentAnnualUpload.Y1978, 
msCurrent.Y1979 = msCurrentAnnualUpload.Y1979, 
msCurrent.Y1980 = msCurrentAnnualUpload.Y1980

你能帮我找出问题吗??

can you help me to find an issue??

推荐答案

我刚刚将您的查询复制并粘贴到 Access 2010 中，它运行正常.通过在数据表视图中打开链接表并手动更改 [Y1977] 值之一，验证您是否可以更新链接表中的值.如果您无法更新链接表，则需要确认

I just copied and pasted your query into Access 2010 and it worked correctly. Verify that you can update the values in your linked table by opening it in Datasheet View and manually changing one of the [Y1977] values. If you cannot update the linked table then you'll need to confirm that

1. 您的 ODBC 连接实际上有权更新 SQL Server 上的表.

1. Your ODBC connection actually has permission to UPDATE the table on the SQL Server.

您的 Access 链接表定义了主键.(在设计视图中打开 Access 链接表并检查下面的屏幕截图中的小金钥匙图标.)

Your Access linked table has a Primary Key defined. (Open the Access linked table in Design View and check for the little gold key icon(s) as in the screenshot below.)

这篇关于“操作必须使用可更新的查询"用于对 Access 中的 SQL 链接表进行 UPDATE 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！