ms-access could not delete

Question

ms-access is connecting to a local mysql database

the following code returns an error:

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 = "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

what is the cause of this error?

table structure is:

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

Answer 1

Is the table in Access or MySql? If it's in MySql it's likely that you don't have the proper permissions to edit table data. Check your connection string that points to the MySql table and make sure that whoever you're connecting as has delete permissions on that table.

Also - does this table have any foreign key relationships to other tables? Perhaps you are trying to delete a record that would cause a violation to some other table's primary key.

Answer 2

Maybe its a case-sensitive issue?

You typed: "tbluserssubjects" for the table structure, but in the query you typed "tblUsersSubjects" (capital U and S)

Answer 3

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.

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

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.

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.