Failed to connect to MySQL via VBA

Question

Failed to connect to MySQL via VBA

Navigation

#1 by (1 votes)
#2 by (0 votes)

7

I'm trying to connect to a Mysql database via Excel VBA, but it returns an error reporting catastrophic failure. I did a test by entering the wrong password, or the wrong database name, and it informs that access was denied or the database does not exist, so it seems to be working, Mysql is responding, it just can not make the connection.

I'm using:

Mysql Server 4.1
ODBC connector 3.51

Note: I can not play Mysql Server version for some issues. I thought about trying to upgrade the connector to a version 5, but I was wondering if it would not generate another problem with MysqlServer.

Below the code I'm using. I've tried other variations, but it's always the same mistake. 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 connector for 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, but the code I am executing to make the query. .open, 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

mysql excel vba

asked by anonymous 12.12.2014 / 12:50

2 answers

Why can not I free memory? Development Problem in Unity 5 3D

score 1 · Answer 1

Try updating the Connector version. According to documentation it works with any version of MySQL above 4.1. 1.

You just need to change your connection string:

 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 make it easier to manage 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 .

score 0 · Answer 2

In my case, it was always a problem not to find the driver in VBA, but the connection to Excel worked. I changed the driver name according to 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