VBA Connect to MYSQL Driver Error

[Jay_610]

Hi,

I am trying to connect to a hosted MySQL database running 5.0.96 so I have installed the latest driver from MySQL site "Connector/ODBC 5.2.6".

But I get this error:
[Microsoft][ODBC Manager] Data source name not found and no default driver specified.

I am able to connect if I use the ODBC connection directly.

Any suggestions?

So here a sample of my code:

Dim oConn As ADODB.Connection
Dim Server_Name As String
Dim User_Name As String
Dim Password As String
Dim Database_Name As String

Server_Name = Sheets("_config").Range("B2").value
User_Name = Sheets("_config").Range("B3").value
Password = Sheets("_config").Range("B4").value
Database_Name = Sheets("_config").Range("B5").value

Set oConn = New ADODB.Connection

oConn.Open "DRIVER={MySQL ODBC 5.0.96 Driver};" & _
"SERVER=Server_Name;" & _
"DATABASE=Database_Name;" & _
"USER=User_Name;" & _
"PASSWORD=Password;" & _
"Option=3"

 

[mole999]

If you manfacture this

oConn.Open "DRIVER={MySQL ODBC 5.0.96 Driver};" & _
"SERVER=Server_Name;" & _
"DATABASE=Database_Name;" & _
"USER=User_Name;" & _
"PASSWORD=Password;" & _
"Option=3"

with the correct values applied does it work

is the ADO shown in your references

 

[Jay_610]

Yes I have "Microsoft ADO Ext. 2.8 for DLL and Security" and "Microsoft ActiveX Data Objects 2.8 Library".


Not sure if this matters but I am using Windows 7 64bit and running Excel 2007.

 

[Jay_610]

Yes I have "Microsoft ADO Ext. 2.8 for DLL and Security" and "Microsoft ActiveX Data Objects 2.8 Library".


Not sure if this matters but I am using Windows 7 64bit and running Excel 2007.

If I place the credentials directly into the code it still does not work.

 

[mole999]

I use this, its a bit messy but works for my needs

Dim adoConn As New ADODB.Connection
Dim adoRs   As New ADODB.Recordset
Dim RS      As New ADODB.Recordset
Dim sConn   As String

 Set rnStart = Sheets("my sheet").Range("A24")

sConn = "Provider=SQLOLEDB; Data Source =**** ; Initial Catalog = *******; User Id = ******; Password=*****"
Var1 = "" ' contains my SQL statement
sSql = Var1

    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    adoConn.CommandTimeout = 60
    Set adoRs = New ADODB.Recordset
    adoRs.Open Source:=sSql, ActiveConnection:=adoConn
    'On Error Resume Next
    If Not (adoRs.BOF Or adoRs.EOF) Then
        Do While Not adoRs.EOF
            rnStart.CopyFromRecordset adoRs
            adoRs.MoveNext
        Loop
        sOutput = Left(sOutput, Len(sOutput) - 1)
    Else
        sOutput = "NO MATCHED DATA"
        Sheets("my page").Range("A24") = sOutput
    End If

    adoRs.Close
    adoConn.Close
    Set adoRs = Nothing
    Set adoConn = Nothing