How can VBA connect to MySQL database in Excel?

Connecting to a MySQL Database using VBA

Attempting to connect to a MySQL database in Excel using VBA can be challenging at times. In your case, you encountered an error while trying to establish a connection.

To successfully connect to a MySQL database using VBA, follow these steps:

Sub ConnectDB()
    Dim oConn As ADODB.Connection

    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"

    ' Open the connection
    oConn.Open str
End Sub

Fetching Data from the Database

Once you have established a connection, you can retrieve data from the database using an ADODB.Recordset object. Here's how:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

' Execute a query
sql = "SELECT * FROM ComputingNotesTable"
rs.Open sql, oConn, adOpenDynamic, adLockOptimistic

' Retrieve the data
Do Until rs.EOF
    Range("A1").Select
    ActiveCell = rs.Fields("Headings")
    rs.MoveNext
Loop

' Clean up
rs.Close
oConn.Close
Set oConn = Nothing
Set rs = Nothing

Comparison with PHP

You mentioned that you were able to successfully connect to MySQL using PHP. In PHP, you used the mysql_connect function. However, in VBA, we use the ADODB.Connection object to establish a connection.

The mysql_connect function is a native PHP function that directly interacts with the MySQL extension. On the other hand, ADODB.Connection is an ActiveX object that provides a more generic way to connect to various databases, including MySQL.

Additional Notes

• Make sure you have the MySQL ODBC Connector installed.
• The Option=3 in the connection string ensures that the string is terminated by a semicolon.
• The adOpenDynamic and adLockOptimistic constants are used to optimize data retrieval.