How can VBA connect to MySQL database in Excel?
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
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.