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     amp                                                 SERVER sql100 xtreemhost com    amp                                                 PORT 3306   amp                                                 DATABASE xth 9595110 MyNotes    amp                                                 UID xth 9595110    amp                                                 PWD myPassword    amp                                                 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

User · Answer

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\

User · Answer

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     amp             SERVER localhost    amp              DATABASE yourdatabase    amp             USER yourdbusername    amp           PASSWORD yourdbpassword    amp           Option 3   End Sub   There rest is here  http   www heritage-tech net 908 inserting-data-into-mysql-from-excel-using-vba

User · Answer

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    amp                 Server Name  amp    Database    amp  Database Name  amp                  Uid    amp  User ID  amp    Pwd    amp  Password  amp           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

User · Answer

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

User · Answer

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

[mysql] How can VBA connect to MySQL database in Excel?

Examples related to mysql

Examples related to excel

Examples related to vba