can not open mysql odbc backend as read-write from code

Question

I have gone through my MySQL server setup for two days. I can only secure a read-only connection from code. The remote odbc connection can be established.  MySQL workbench connects remotely. I am able to upload tables and all their data, from access. I do have a read only connection in code.

I use:

Set dbs_mac = OpenDatabase("ConnectODBC32", True, False, "ODBC;DATABASE=db;UID=ServerClient;PWD=*********;DSN=ConnectODBC32") 'CurrentDb

strSQL = "SELECT *" & _
        " FROM [Client Information]" & _
        " ORDER BY [client_id] ASC"

Set rst_ctl = dbs_mac.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbConsistent)

it's hard to tell if I missed something on the internet because a lot of stuff changed since a lot of posts were made over a decade ago.

Answer 1

Hi Xittenn,

Do you mean that you could not update data in MySql database from access?

Would you get any error message when running the code? What's the error message?

Best Regards,

Terry

Answer 2

Are you using an ODBC Workspace when connecting? You may also want to consider using ADO. See below for examples:

http://www.utteraccess.com/forum/odbc-connection-mysql-ta-t1637493.html

---

Paul ~~~~ Microsoft MVP (Visual Basic)