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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

bbotzong

Board Regular
Joined
Dec 17, 2003
Messages
51
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
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

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

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Jun 14, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 14, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Bump... anyone can assist on this please....
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
1,119,120
Messages
5,576,207
Members
412,706
Latest member
msousa25
Top