Operation must use an updateable query

I am currently developing a certain application in MS Access and I need to link 2 databases, select data from one and move it to the second. In access I have no problem using the TransferDatabase command.

DoCmd.TransferDatabase acLink, "Microsoft Access", databaseFile.mdb, acTable, tableName, newTableName

But since our application must support multi database via ODBC, I also run the script on 2 mysql databases. Unfortunately, I have encountered this error, when executing update statements.

But before this, I have encountered and solve another error which is:
Select Unique Record Identifier - (1st error)

The solution to this problem according to some article is to auto press ESC key when needed, which is done like this:

SendKeys ("{ESC}") 'this solves the 1st error
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=" & localDSN, acTable, tableName, newTableName

When I have encountered the second error - the title of the blog, I google it and found several suggestions including giving rights to IUSR*, IWAM users [http://support.microsoft.com/kb/175168], still I haven't solve my problem.

That's when I thought about the 1st error was related to the second, and going deep into the article mentioned, I've tried adding index to a table that cause the error (yes it doesn't have one since it's just a link table).

And my problem was gone just like that. If you will read the mysql documentation you will find out that it doesn't allow UPDATE statements without primary/unique keys not like in MS Access which ignores this. So when using other database always set your primary key :-D.
Operation must use an updateable query Operation must use an updateable query Reviewed by czetsuya on Wednesday, October 21, 2009 Rating: 5

No comments:

Powered by Blogger.