How can VBA connect to MySQL database in Excel?


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};" & _
                                            ";" & _
                                            "PORT=3306" & _
                                            "DATABASE=xth_9595110_MyNotes;" & _
                                            "UID=xth_9595110;" & _
                                            "PWD=myPassword;" & _
''' error '''
oConn.Open str
End Sub

Private Sub InsertData()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
sql = "SELECT * FROM ComputingNotesTable"
rs.Open sql, oConn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
    ActiveCell = rs.Fields("Headings")
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("","xth_9595110","myPassword") or die(mysql_error());



Ranjit’s code caused the same error message as reported by Tin, but worked after updating 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.

Answered By – Bob Davis

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published