Error: ODBC driver does not support the requested properties

[Totti_K]

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.

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

 

[bbotzong]

Remove the quotes around the table name in the sqlstr assignment line as shown:

sqlstr = "select * from menu_items "

Also, check your statement writing to Excel. The CopyFromRecordset command should be referencing your recordset (rs), not your connection (cn). And, it worked better for me getting rid of the ThisWorkbook. reference as shown:

Sheets(2).Range("A1").CopyFromRecordset rs

That should get you going...

 

[Totti_K]

Hi @bbotzong, I'm afraid after the changes, it still has the same error.

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 where 1"

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 rs
cn.Close

End Sub

 

[Fazza]

the WHERE clause in the SQL doesn't make sense

WHERE 1

it either needs to be deleted entirely, or
WHERE somefield = 1

 

[Totti_K]

the WHERE clause in the SQL doesn't make sense

WHERE 1

it either needs to be deleted entirely, or
WHERE somefield = 1

Hi @Fazza , it indeed doesn't make sense and the original statement doesn't have it. I added it in after troubleshooting as this statement works in MYSQL. Removed it but same result.

 

[Totti_K]

Bump... anyone can assist on this please....

 

[Fazza]

what if you remove the adOpenStatic from the rs.Open line?

 

[Totti_K]

what if you remove the adOpenStatic from the rs.Open line?

That seems to do the job. thanks much....

 

[Fazza]

thank you