Excel VBA Connection string to MySQL remote Database is not working

0
votes

I have MySQL server installed on a remote machine.

I am trying to connect to it via excel VBA - i can succesfully connect to it through excels built-in database connection tools and copy the connection string, however, VBA hates that connection string. here is my code and the error

Dim conn As New ADODB.Connection

Dim sConnectionString As String

Set conn = New ADODB.Connection

sConnectionString = "ODBC;Driver={MySQL ODBC 5.3 ANSI Driver};Provider=MSDASQL;Server=AddressHere;Port=3306;Database=DatabaseName;User=UserName;Option=3"

conn.ConnectionString = sConnectionString

conn.Open

Here is the error messgae i get when it tries to connect, i have verified that the ODBC driver is installed.

enter image description here

1
mysqlexcelvba
Have you checked to make sure 32 bit vs 64 bit are all configured/installed? It gets a bit messy... – braX
no, but I will first thing. Thanks for the tip – Truth is a person
I would start with that - based on personal experience with a very similar problem. 64 bit OS using 32 bit office install and 32 bit mySQL eventually made it work for me.. but different combinations can prove to be incompatible. – braX
Would it be weird if i said i love you? That totally worked, i just installed the 32bit version of the MySQL ODBC driver and bam! – Truth is a person
I guess I should make it an answer then, eh? – braX

1 Answers

1
votes

Have you checked to make sure 32 bit vs 64 bit are all configured/installed? It gets a bit messy...

Based on personal experience with a very similar problem. 64 bit OS using 32 bit office install and 32 bit mySQL eventually made it work for me.. but different combinations can prove to be incompatible.

Don't think that your 64 bit OS means you need 64 bit mySQL if your Office install is 32 bit. Also keep in mind that if you use ODBC to connect, there are 32 bit and 64 bit versions of that too in your system.