Excel VBA连接外部MySQL 8.0服务器（ODBC）报错求助

环境配置 #

• 64位MySQL 8.0服务器（部署于Oracle云Ubuntu服务器）
• ODBC Connector 8.0 Unicode 64位驱动
• 64位Office 365

报错信息 #

[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

解决方案 #

1. 确认驱动名称完全匹配 #

打开64位ODBC数据源管理器（路径：C:\Windows\System32\odbcad32.exe），在「驱动程序」标签中查看MySQL驱动的精确名称，必须与连接字符串里的名称完全一致（包括空格、版本号），比如MySQL ODBC 8.0 Unicode Driver。

2. 修正变量声明与IP配置 #

代码中database和mysql_driver未声明类型，添加Dim声明避免作用域问题；同时替换location为实际服务器IP：

Dim database As String, mysql_driver As String
location = "你的服务器IP地址"

3. 适配MySQL 8.0认证方式 #

MySQL 8.0默认使用caching_sha2_password插件，需在连接字符串中指定认证方式：

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

或在MySQL服务器端修改用户认证规则：

ALTER USER 'ExcelTest'@'%' IDENTIFIED WITH mysql_native_password BY 'Pass';
FLUSH PRIVILEGES;

（%允许任意IP连接，可替换为指定IP）

4. 检查网络连通性 #

• 确保本地机器能ping通服务器IP
• 确认服务器3306端口已在Oracle云防火墙、Ubuntu本地防火墙（如ufw）中开放，允许本地IP访问

5. 验证ADODB引用版本 #

在VBA编辑器「工具」→「引用」中，可尝试勾选更高版本的Microsoft ActiveX Data Objects Library（如6.1），替代注释中的v2.8版本，提升兼容性。

内容的提问来源于stack exchange，提问作者Predrog