dimanche 19 avril 2015

excel vba mysql ado connection

I'm trying to establish an ADO connection between excel on my local machine and a MySQL database on my server.

In the examples I've seen (here and here, for instance) there's a driver of the form MySQL ODBC 5.x Driver. It seems that after installing the latest mysql connector / odbc download the relevant 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'm not having success establishing a connection to MySQL with either of these.


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 = ""
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

Upon running the above, I receive error [Microsoft][ODBC SQL Server Drive][DBNETLIB]SQL Server does not exist or access denied. The error for the 'native client 11.0' driver is Could not open a connection to SQL Server[53].

I've tested the connection parameters in MySQL workbench and all is functional. What's going on?

Aucun commentaire:

Enregistrer un commentaire