MySQL connection error

Problem Statement: Unable to establish connection to MySQL database from Excel macro. Error states ‘Data source name not found and no default driver specified’.

Environment: Windows 10 64 bit machine with Office 2016 and MySQL x64

Code causing the error:

ConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;" +
                     "Database=<database>;User=<user>; Password=<password>;"
Connection.Open ConnectionString
VisualBasic Error
My SQL connection error

Analysis: The error states that data source name is not found. However, the data source is present and is connecting fine when connected from MySQL workbench.

The error also states that the default driver is not specified even though MySQL x64 has been installed on the same machine. Microsoft Office is a 32 bit application. This means that Excel is expecting MySQL 32 bit driver to be present on the machine to be able establish the connection.

Solution: Download and install MySQL x32 ODBC driver from here.

 

All feedback welcome. Leave a Reply.