How can VBA connect to MySQL database in Excel?

Question

Dim oConn As ADODB.Connection
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
Dim str As String
str = "DRIVER={MySQL ODBC 5.2.2 Driver};" & _
                                            "SERVER=sql100.xtreemhost.com;" & _
                                            "PORT=3306" & _
                                            "DATABASE=xth_9595110_MyNotes;" & _
                                            "UID=xth_9595110;" & _
                                            "PWD=myPassword;" & _
                                            "Option=3"
''' error '''
oConn.Open str
End Sub

Private Sub InsertData()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
ConnectDB
sql = "SELECT * FROM ComputingNotesTable"
rs.Open sql, oConn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
    Range("A1").Select
    ActiveCell = rs.Fields("Headings")
    rs.MoveNext
Loop
rs.Close
oConn.Close
Set oConn = Nothing
Set rs = Nothing
End Sub

Doing the similar things in PHP, I could successfully log in to the MySQL server. I have installed the ODBC connector. But in the above VBA codes, I failed. An error turns up. (see the codes where the error exists)

$connect = mysql_connect("sql100.xtreemhost.com","xth_9595110","myPassword") or die(mysql_error());

mysql_select_db("myTable",$connect);

Answer 1

This piece of vba worked for me:

Sub connect()
    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

    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

    SQLStr = "SELECT * FROM ComputingNotesTable"

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

    rs.Open SQLStr, Cn, adOpenStatic

    Dim myArray()

    myArray = rs.GetRows()

    kolumner = UBound(myArray, 1)
    rader = UBound(myArray, 2)

    For K = 0 To kolumner ' Using For loop data are displayed
        Range("a5").Offset(0, K).Value = rs.Fields(K).Name
        For R = 0 To rader
           Range("A5").Offset(R + 1, K).Value = myArray(K, R)
        Next
    Next

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

Answer 2

Ranjit's code caused the same error message as reported by Tin, but worked after updating Cn.open with the ODBC driver I'm running. Check the Drivers tab in the ODBC Data Source Administrator. Mine said "MySQL ODBC 5.3 Unicode Driver" so I updated accordingly.

Answer 3

Just a side note for anyone that stumbles onto this same inquiry... My Operating System is 64 bit - so of course I downloaded the 64 bit MySQL driver... however, my Office applications are 32 bit... Once I downloaded the 32 bit version, the error went away and I could move forward.

Answer 4

Enable Microsoft ActiveX Data Objects 2.8 Library

Dim oConn As ADODB.Connection 
Private Sub ConnectDB()     
Set oConn = New ADODB.Connection    
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _        
"SERVER=localhost;" & _         
"DATABASE=yourdatabase;" & _        
"USER=yourdbusername;" & _      
"PASSWORD=yourdbpassword;" & _      
"Option=3" 
End Sub

There rest is here: http://www.heritage-tech.net/908/inserting-data-into-mysql-from-excel-using-vba/

Answer 5

Updating this topic with a more recent answer, solution that worked for me with version 8.0 of MySQL Connector/ODBC (downloaded at https://downloads.mysql.com/archives/c-odbc/):

Public oConn As ADODB.Connection
Sub MySqlInit()
    If oConn Is Nothing Then
        Dim str As String
        str = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=xxxxx;DATABASE=xxxxx;PORT=3306;UID=xxxxx;PWD=xxxxx;"
        Set oConn = New ADODB.Connection
        oConn.Open str
    End If
End Sub

The most important thing on this matter is to check the proper name and version of the installed driver at: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers\