Mysql connection failed via VBA

Question

I am trying to connect to a Mysql database via Excel VBA, but returns an error stating catastrophic failure. I made a test by putting the wrong password, or the name of the wrong database, and it informs that access has been denied or the database does not exist, so it seems that it is working, Mysql is responding, just can not make the connection.

I’m using:

• Mysql Server 4.1
• ODBC Nector 3.51

Note: I can’t touch the Mysql Server version for some questions. I thought about trying to upgrade the Nector to a version 5, but I was wondering if it would generate another problem with Mysqlserver.

Below the code I am using. I tried other variations, but it is always the same error. Do you have a solution to this problem?

 Set Cn = New ADODB.Connection
 With Cn
    .ConnectionString = _
    "driver={mysql odbc 3.51 driver};" & _
    "server=localhost;database=oswau;" & _
    "uid=root;pwd=;"
    .CursorLocation = adUseClient
    .Open
 End With

Adding some more information: With the information passed by colleagues, updating the Nector to version 5 he communicated, but only with localhost. When I try to communicate with the Mysql server on the network, the error occurs indicating:"The ODBC driver does not support the requested properties. Below the code I am running to make the query. The error occurs when it tries to open the query in the rs.open part, last line of code below.

 SQLStr = "Select Count(cd_tecnico) as 'Total' from Suporte_tecnico_nacional 
 where dt_suporte between '2014-06-01' and '2014-06-02' and cd_tecnico=23370"

 Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
 Cn.Open "Driver={MySQL ODBC 5.2 Unicode Driver};Server=" & _
         Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"

 rs.Open SQLStr, Cn, adOpenStatic

Answer 1

Try updating the Connector version. As per documentation it works with any version of Mysql above 4.1.1.

You just need to change your string Connection:

 Set Cn = New ADODB.Connection
 With Cn
    .ConnectionString = _
    "driver={MySQL ODBC 5.2 UNICODE Driver};" & _
    "server=localhost;database=oswau;" & _
    "uid=root;pwd=;"
    .CursorLocation = adUseClient
    .Open
 End With

I like to create a DSN on my machine to facilitate the management of ODBC connections, so if I update the connector, I just need to change the configuration in one place.

Dim db As ADODB.Connection
Set db = New ADODB.Connection

conStr = "myDSN"

db.Open myDSN, "user", "pswd"

I see how to create DSN connections in Windows here.

Answer 2

In my case always gave problem of not finding the driver in VBA, but the connection linked to Excel worked. I changed the driver name according to the linked excel system DSN:

Sub connect2()

    Dim Password As String
    Dim SQLStr As String
    'OMIT Dim Cn statement
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    'OMIT Dim rs statement

    Sheets("Plan1").Select
    'Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
    Server_Name = Range("b2").Value
    Database_Name = Range("b3").Value ' Name of database
    User_ID = Range("b4").Value 'id user or username
    Password = Range("b5").Value 'Password

      'DSN = "MySQL ODBC 5.3 ANSI Driver Sis"

    Set conn = New ADODB.Connection
        conn.Open "DSN=MySQL ODBC 5.3 ANSI Driver Sis;" _
        & ";SERVER=" & Server_Name _
        & ";DATABASE=" & Database_Name _
        & ";UID=" & User_ID _
        & ";PWD=" & Password _
        & ";OPTION=3"
        '"ODBC;DSN=MySQL ODBC 5.3 ANSI Driver Sis;"

        Set rs1 = New ADODB.Recordset
            SQLStr = "SELECT * FROM `cargos` " & ";"
            rs1.Open SQLStr, conn, adOpenStatic
        With Worksheets("Planilha3").Cells(1, 1)
            .ClearContents
            .CopyFromRecordset rs1
        End With
            rs1.Close
        Set rs1 = Nothing

End Sub