VBA如何在Excel中连接到MySQL数据库？

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

在PHP中执行类似的操作，我可以成功登录到MySQL服务器。我已经安装了ODBC连接器。但是在上面的VBA代码中，我失败了。出现错误。（请参阅存在错误的代码）

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

mysql_select_db("myTable",$connect);

Answer 1

Ranjit的代码导致了与Tin报告相同的错误消息，但是在使用我正在运行的ODBC驱动程序更新Cn.open之后起作用。检查“
ODBC数据源管理器”中的“驱动程序”选项卡。我的说“ MySQL ODBC 5.3 Unicode驱动程序”，所以我进行了相应的更新。