更新时MS Access错误“操作必须使用可更新的查询”

I'm migratting a MS Access system into a mySQL system with PHP PDO. Everything's ok until PHP stop readding some records on an Access table. This table has an ID field that's a Long Integer datatype. I don't know why but the ID starts from 0 to 16949 then jumps to 36573745 and ends at 36581986. These higher numbers the PDO doesn't read, I can't figure out why.

我正在使用PHP PDO将MS Access系统迁移到mySQL系统中。在PHP停止读取Access表上的某些记录之前,一切都还可以。此表具有ID字段,该字段是Long Integer数据类型。我不知道为什么但ID从0到16949开始然后跳到36573745并在36581986结束。这些更高的数字PDO没有读,我无法弄清楚为什么。

Anyway to workaround this, I'm trying to UPDATE these higher numbers to continue the normal sequence of the lower ID's. But Access can't understand my query and I don't know how to make it run.

无论如何解决这个问题,我试图更新这些更高的数字以继续下一个ID的正常序列。但Access无法理解我的查询,我不知道如何让它运行。

This is the query

这是查询

UPDATE Validador_Manut SET ID = (
    SELECT (Max(ID) + 1) FROM Validador_Manut WHERE ID < 100000
) WHERE ID > 100000

It's shows me this error Uperation must use an updatable query.

它告诉我这个错误Uperation必须使用可更新的查询。

2 个解决方案

#1

If you just want to lower the IDs > 100000 to consecutive numbers after 16949 then why don't you try simply this?

如果您只是想在16949之后将ID> 100000降低到连续数字,那你为什么不试试这个呢?

UPDATE Validador_Manut SET [ID] = [ID]-36556795 WHERE ID > 100000

36573745 - 36556795 = 16950

#2

Two things come to my mind.

我想到了两件事。

1.) Reserved keywords

1.)保留关键字

If you use certain keywords as table names or column names, you might get cryptic exception that do not really tell you the actual cause.

如果您使用某些关键字作为表名或列名,您可能会得到神秘的异常,并不能真正告诉您实际原因。

Your column ID looks suspicious to me.

您的列ID对我来说很可疑。

A short internet search did not bring up an evidence for this theory, but I believe to remember that I saw ID as a reserved word in MS Access somewhere. You could try to

一个简短的互联网搜索没有提供这个理论的证据,但我相信我记得我在某个地方看到ID作为MS Access中的保留字。你可以尝试

rename the column

重命名列

write column names in angle brackets [] (not recommended)

在尖括号[]中写入列名(不推荐)

2.) This article looks like it could help: Solving the Operation Must Use An Updateable Query error

2.)本文看起来可能会有所帮助:解决操作必须使用可更新的查询错误

When a Jet 4.0 database (the actual type of database represented by your "Access" mdb file) is deployed in a multi-user environment, an .ldb file is created whenever the database is opened. The .ldb file contains details which include who has opened the file, and primarily serves to prevent opened records being written to by another user.

当Jet 4.0数据库(由“Access”mdb文件表示的实际数据库类型)部署在多用户环境中时,只要打开数据库,就会创建.ldb文件。 .ldb文件包含的详细信息包括谁已打开文件,主要用于防止其他用户写入打开的记录。

In the context of an ASP.NET application, who the "user" is will depend on the platform: for XP Pro machines, the user is the ASPNET account. On Windows Server 2003, 2008 and Vista, it is the NETWORK SERVICE account. However, if you have ASP.NET Impersonation enabled, the default user account will be IUSR_machinename, or whichever account you have applied. If you are unsure which account your ASP.NET application is running under, Environment.UserName will return it. To be able to create, write to and delete the required .ldb file, the relevant user needs MODIFY permissions on the folder that the .mdb file is in.

在ASP.NET应用程序的上下文中,“用户”将取决于平台:对于XP Pro计算机,用户是ASPNET帐户。在Windows Server 2003,2008和Vista上,它是NETWORK SERVICE帐户。但是,如果启用了ASP.NET模拟,则默认用户帐户将为IUSR_machinename,或者您应用的任何帐户。如果您不确定运行ASP.NET应用程序的帐户,Environment.UserName将返回它。为了能够创建,写入和删除所需的.ldb文件,相关用户需要对.mdb文件所在的文件夹具有MODIFY权限。

To set this permission, right click on the App_Data folder (or whichever other folder you have put the mdb file in) and select Properties. Look for the Security tab. If you can't see it, you need to go to My Computer, then click Tools and choose Folder Options.... then click the View tab. Scroll to the bottom and uncheck "Use simple file sharing (recommended)". Back to the Security tab, you need to add the relevant account to the Group or User Names box. Click Add.... then click Advanced, then Find Now. The appropriate account should be listed. Double click it to add it to the Group or User Names box, then check the Modify option in the permissions. That's it. You are done.

要设置此权限,请右键单击App_Data文件夹(或将mdb文件放入其中的任何其他文件夹),然后选择“属性”。查找“安全”选项卡。如果看不到,则需要转到“我的电脑”,然后单击“工具”并选择“文件夹选项...”,然后单击“视图”选项卡。滚动到底部并取消选中“使用简单文件共享(推荐)”。返回“安全”选项卡,您需要将相关帐户添加到“组或用户名”框中。单击添加....然后单击高级,然后单击立即查找。应列出相应的帐户。双击它以将其添加到“组或用户名”框,然后选中权限中的“修改”选项。而已。你完成了。

Note: this fix will also solve "The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data" errors.

注意:此修复程序还将解决“Microsoft Jet数据库引擎无法打开文件'(未知)”。它已由其他用户独占打开,或者您需要获得查看其数据“错误的权限。

#1