Excel vba mysql ado connection
I am trying to establish an ADO connection between excel on my local machine and a MySQL database on my server.
The examples I've seen ( here and here , for example) have a form driver MySQL ODBC 5.x Driver
. It looks like after installing the latest mysql connector / odbc download (32-bit to match my msexcel) the corresponding registry driver files HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Driver\
are now named "SQL Server" and "SQL Server Native Client 11.0". I have no success establishing a MySQL connection with any of these.
My VBA
Sub connect()
Dim Password As String
Dim SQLStr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = "184.154.225.243"
Database_Name = "*******" ' Name of database
User_ID = "********" 'id user or username
Password = "*******" 'Password
Port = "3306"
SQLStr = "SELECT * FROM *******"
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Driver={SQL Server};Server=" & _
Server_Name & ";Port=" & Port & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
When doing the above, I am getting an error [Microsoft][ODBC SQL Server Drive][DBNETLIB]SQL Server does not exist or access denied
. Error for driver "native client 11.0":Could not open a connection to SQL Server[53].
I've tested the connection parameters in MySQL workbench and it's all functional. What's happening?
source to share
-
Check if you are using 32-bit or 64-bit version of Microsoft Office .
-
Based on the above, download and install the appropriate MySQL driver from the download link
-
After the ODBC driver installation is complete, check the ODBC snap-in to see that the driver is listed as installed.
If you are using a 32 bit OS then everything is 32 bit. Use the Run tab → odbcad32.exe → Drivers.
If you are using 64-bit OS and Microsoft Office is 32-bit then use c: \ windows \ syswow64 \ odbcad32.exe → Drivers tab.
If you are using a 64-bit OS and Microsoft Office is 64-bit, use the "Run → odbcad32.exe → Drivers" tab.
If the MySQL drivers are installed correctly, they should display as shown above.
-
Create a system DSN using the ODBC snap-in with the above MySQL driver and check the connection to see if it works.
-
Use the same parameters when trying to create ODBC from VBA.
Example:
Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=myDataBase;
User=myUsername;Password=myPassword;Option=3;
- Once it is established that you can successfully create a connection to the MySQL server, change the driver name in the registry (be sure to update the registry) and try using the new name you give, for example
SQL Server
.
Remember, on an x64-bit system, for x32-bit drivers:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers
After changing the driver name, a restart may be required.
source to share