Error: ODBC driver does not support the requested properties

Totti_K

New Member
Joined
Jun 14, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.

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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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...
 
Upvote 0
Hi @bbotzong, I'm afraid after the changes, it still has the same error.
1592785536802.png

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 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
 
Upvote 0
the WHERE clause in the SQL doesn't make sense

WHERE 1

it either needs to be deleted entirely, or
WHERE somefield = 1
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top