How to Fix Operation Must Use an Updateable Query in Msaccess
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....
https://www.czetsuyatech.com/2021/07/msaccess-operation-must-use-an-updateable-query.html
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.
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:
The solution to this problem according to some article is to auto press ESC key when needed, which is done like this:
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.
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.
Post a Comment