Query MySQL Table with Excel VBA, ODBC Connection

Question

I've got a bit of an issue with Microsoft Excel while trying to get values from an MySQL 8.0 Table. I have some code (below) I got from the net but I keep getting an error message.
I'm quite new at this so I need a bit of help.

I'm on Windows 10 with a 64 bit computer.
Timeline:
→ I downloaded MySQL a week ago, got the 8.0 workbench version and have the connector installed (Connector/ODBC 8.0.12 - X64).
→ I created a Schema called sap in the MySQL Workbench
→ I created a Table in sap called variance filled with data
So far, so good.
Then, I load up Excel, get the 'Microsoft ActiveX Data Objects 6.1 Library' and 'Microsoft Forms 2.0 Object Library' create a Module and insert the following code :

Sub MySQL()

Dim conn As New ADODB.Connection
Dim SQL As String
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open "DRIVER={MySQL ODBC 8.0 Driver}" _
& ";SERVER=" & "localhost" _
& ";DATABASE=" & "sap" _
& ";USER=" & "root" _
& ";PASSWORD=" & "password" _
& ";OPTION=3"

Set rs = New ADODB.Recordset

SQL = "SELECT * FROM sap.variance;"
rs.Open SQL, conn

ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rs

rs.Close
Set rs = Nothing

End Sub

The file is on my computer so localhost works for me, I also tried with "127.0.0.1"
The database (schema for MySQL) is called sap
The user I'm using is the root user
And password is password (not really but I'm not telling for real)

Should work, shouldn't it ? And then, the fatal part :

"[Microsoft][ODBC Driver Manager] Data Source name not found and no device driver specified"

Debug highlights the line 'conn.Open ....

What am I doing wrong ?

DanB · Accepted Answer · 2018-10-25 13:59:49Z

1

If your Excel is 64 bits, you need MySQL odbc 64 bits. If your Excel is 32 bits, you need ODBC 32 bits (even your Windows is 64 bits).

An easy way to know if your Excel is 32 or 64 bits, open Excel and Task Manager. In processus tab, see if Excel has (32 bit) suffix.

answered Oct 25, 2018 at 13:59

DanB

2,1121 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Astrashar Over a year ago

I checked what you said, it was spot on I downloaded the latest 32 bit connector (turns out it's called MySQL ODBC 8.0 Unicode Driver) from the MySQL webpage, installed using their MSI installer. And now everything works fine ! Thank you ! PS: 64 32 bit compatibility ? I would never have thought to check for that one...

Collectives™ on Stack Overflow

Query MySQL Table with Excel VBA, ODBC Connection

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related