ms-access无法删除 [英] ms-access could not delete

问题描述

ms-access正在连接到本地mysql数据库

ms-access is connecting to a local mysql database

以下代码返回错误:

Public Function run_sql(strSql As String)
On Error GoTo lblError
CurrentDb.Execute strSql, dbFailOnError
lblExit:
    Exit Function
lblError:
    MsgBox Err.Number & ": " & Err.Description
    Resume lblExit
End Function

strSql =从tblUsersSubjects删除WHERE user_id = 2007;" -我运行了该语句，它运行正常，但是访问给了我这个错误:3086:无法从指定的表中删除

strSql = "DELETE FROM tblUsersSubjects WHERE user_id=2007;" - i ran this statement it works perfectly, but access is giving me this error: 3086: Could not delete from specified tables

此错误的原因是什么?

表结构为:

CREATE TABLE `tbluserssubjects` (
  `user_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `other` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

请注意，我可以使用外壳而不是访问来执行所需的删除操作

please note that i AM able perform the needed delete operation my using the shell, instead of access

推荐答案

在Access中，您可以在数据表视图中打开链接表tblUsersSubjects并在数据表视图中进行编辑或删除吗?如果不是，Access可能会将与您的MySql表的连接视为只读.尝试删除链接(在Access中；在MySql中不是实际的表).然后在Access中重新链接表，并确保告诉Access将哪个字段(或字段组合)用作主键.如果Access不知道链接表的主键，则该链接将为只读.

From within Access can you open your linked table, tblUsersSubjects, in datasheet view and edit or delete in datasheet view? If not, Access may be treating the connection to your MySql table as read-only. Try deleting the link (in Access; not the actual table in MySql). Then re-link the table in Access and make sure to tell Access which field (or combination of fields) to use as a primary key. If Access isn't aware of a linked table's primary key, the link will be read-only.

与Alex脱机讨论后，我想添加到此答案中:

After off-line discussions with Alex, I want to add to this answer:

最初访问无法识别要做什么 用作主键，因此您的链接表在Access端是只读的.我猜这是因为您的CREATE TABLE语句没有 包括主键约束.但我实际上不知道 链接时，Access 自动如何识别主键的详细信息 外部表.也许，在没有明确定义的情况下 主键，它可能会寻找一个非空且唯一的字段 约束.但是CREATE TABLE语句在MySql上不包含任何唯一约束 也可以.

Access originally didn't recognize what to use as a primary key, so your linked table was read-only from the Access side. I'll guess that was because your CREATE TABLE statement didn't include a primary key constraint. But I don't actually know the details of how Access automagically identifies the primary key when linking to an external table. Perhaps, in the absence of an explicitly defined primary key, it might look for a field with Not Null and Unique constraints. But the CREATE TABLE statement didn't include any unique constraints on your MySql table either.

因此，当Access无法自动猜测外部表的主键时，必须告诉它哪个字段用作主键... 除非，否则要使用链接表在Access中为只读.

So when Access is not able to automagically guess the external table's primary key, you must tell it which field (or fields) to use as the primary key ... unless you want the linked table to be read-only from Access.

这篇关于ms-access无法删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！