Excel连接到外部MySQL服务器(ODBC8.0)

Question

我想将excel文件与一个项目的MySQL服务器连接起来，所以我在互联网上搜索了一个解决方案。我找到了一些，但都是旧的，我不喜欢8-10岁的方法和司机。

我得到的只是没有找到Microsoft数据源名称，也没有指定默认驱动程序。

因此，我设置了以下设置：

• A MySQL 8.0服务器(在甲骨文云中的Ubuntu服务器上) 64位，ODBC连接器8.0Unicode 64位
• Office 365 64位

我试图与其中一些方法建立连接，但它们提供了所有相同的错误：[Microsoft][ODBC Driver Manager] Data source name not found, and no default driver specified

Public Function OpenConnection() As ADODB.Connection
    ''This function requires the "Microsoft ActiveX Data Objects" Library (Choose v2.8 from references for compatibility across Office versions)

    Dim source As String, location As String, user As String, password As String
    location = My server IP
    user = "ExcelTest"
    password = "Pass"
    database = "Test"
    mysql_driver = "MySQL ODBC 8.0 Driver" ''Tried "MySQL ODBC 8.0 Unicode Driver" too

    ''Build the connection string
    Dim connectionString As String

    connectionString = "Driver={" & mysql_driver & "};Server=" & location & ";Database=" & database & ";UID=" & user & ";PWD=" & password

    ''Create and open a new connection
    Set OpenConnection = New ADODB.Connection
    OpenConnection.CursorLocation = adUseClient
    Call OpenConnection.Open(connectionString)

End Function

通常，我发现一个相当好的教程或步行，但这一次，没有最新的。

我是的。我很确定所有的事情都是64位。

我不知道去哪里找--请帮忙

Answer 1

首先使用Windows数据源管理员测试驱动程序是否正确安装。

Option Explicit

Sub test()

    Dim conn
    Set conn = OpenConnection()
    With conn
        .CursorLocation = adUseClient
        MsgBox "Connected to " & .DefaultDatabase, vbInformation
    End With
    
End Sub

Public Function OpenConnection() As ADODB.Connection

    Const location = ""
    Const user = ""
    Const password = ""
    Const database = "test"
    Const mysql_driver = "MySQL ODBC 8.0 Unicode Driver"

    ' Build the connection string
    Dim s As String
    s = "Driver={" & mysql_driver & "};Server=" & _
    location & ";Database=" & _
    database & ";UID=" & _
    user & ";PWD=" & password
    Debug.Print s
    
    ' Open connection
    Set OpenConnection = New ADODB.Connection
    OpenConnection.Open s
    
End Function