Hello, I am trying to create a simple VBA to query mysql db. I have done the following.
1. Install visual Studio 2015 Redistributable
2. Download and install MySQL connector/ODBC
3. Setup the ODBC in "ODBC Datasource Administrator" in windows
When I run my code line by line, the error pop up online 27 ("rs Open....")
I believe the code is able to connect to MYsql db and I have confirmed the sql query statement is fine as I was able to run it in mysql.
Any assistance is very much appreciated.
1. Install visual Studio 2015 Redistributable
2. Download and install MySQL connector/ODBC
3. Setup the ODBC in "ODBC Datasource Administrator" in windows
When I run my code line by line, the error pop up online 27 ("rs Open....")
I believe the code is able to connect to MYsql db and I have confirmed the sql query statement is fine as I was able to run it in mysql.
Any assistance is very much appreciated.
VBA Code:
Sub ConnectMySql()
Dim cn As Object
Dim rs As Object
Dim userName As String
Dim password As String
Dim server As String
Dim dbName As String
Dim sqlstr As String
Const adOpenStatic = 3
server = "localhost"
userName = "admin"
password = "admin"
dbName = "menu"
sqlstr = "select * from 'menu_items' "
Set cn = CreateObject("ADODB.Connection")
cn.Open "DRIVER={MySQL ODBC 8.0 Unicode Driver}" _
& ";SERVER=" & server _
& ";DATABASE=" & dbName _
& ";UID=" & userName _
& ";PWD=" & password _
& ";OPTION=3" '
Set rs = CreateObject("ADODB.Recordset")
rs.Open sqlstr, cn, adOpenStatic
ThisWorkbook.Sheets(2).Range("A1").CopyFromRecordset cn
cn.Close
End Sub