Excel VBA Connection string to MySQL remote Database is not working

Question

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.

Have you checked to make sure 32 bit vs 64 bit are all configured/installed? It gets a bit messy... — braX
– braX, Commented Nov 14, 2019 at 16:24
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
– braX, Commented Nov 15, 2019 at 2:41
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
– Truth is a person, Commented Nov 15, 2019 at 21:12

braX · Accepted Answer · 2019-11-15 21:38:23Z

1

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.

answered Nov 15, 2019 at 21:38

braX

11.9k5 gold badges24 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA Connection string to MySQL remote Database is not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related