Excel VBA连接MySQL Server出现未指定自动化错误如何解决？

问题场景 #

• 测试数据库部署在db4free.net，存储公开northwind训练数据集，尝试通过Excel VBA将数据拉取到本地工作表时触发通用未指定/自动化错误
• 已在VBA引用中添加Microsoft ActiveX Data Objects 2.8 Library/6.1版本，配置无异常
• 原始运行代码如下：

Sub sqlTest()
    'Declare some strings to hold the connection string and the SQL statement
    Dim cnStr As String
    Dim sqlStr As String
    
    'Define a connection and a recordset to hold extracted information
    Dim oConn As ADODB.Connection
    Dim rcSet As New ADODB.Recordset
    Set oConn = New ADODB.Connection
    Set rcSet = CreateObject("ADODB.Recordset")
    
    'connection string to connect to db4free.net
    cnStr = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=85.10.205.173;DATABASE=resumedemo;PORT=3306;UID=jwaycaster;PWD=resumedemo123;"
    
    'Test SQL query
    sqlStr = "SELECT * FROM `Employees`"
    
    'This is where it crashes
    oConn.Open cnStr
    
    oConn.CommandTimeout = 900
    
    rcSet.Open sqlStr, oConn
    
    Sheets(1).Range("A1").CopyFromRecordset rcSet
    
    rcSet.Close
    
    oConn.Close
End Sub

核心根因 #

运行代码的设备未安装连接字符串指定的MySQL ODBC 8.0 Unicode Driver驱动，导致ADODB无法初始化数据库连接触发报错。

解决步骤 #

1. 下载安装与Excel位数匹配的MySQL ODBC 8.0驱动，安装完成后重启Excel生效
2. 建议使用优化后的代码版本，修复了原代码中Recordset对象重复声明的问题，新增错误处理逻辑避免资源泄漏：

Sub sqlTest()
    Dim cnStr As String
    Dim sqlStr As String
    Dim oConn As ADODB.Connection
    Dim rcSet As ADODB.Recordset
    
    Set oConn = New ADODB.Connection
    Set rcSet = New ADODB.Recordset
    
    cnStr = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=85.10.205.173;DATABASE=resumedemo;PORT=3306;UID=jwaycaster;PWD=resumedemo123;"
    sqlStr = "SELECT * FROM `Employees`"
    
    On Error GoTo ErrHandle
    oConn.Open cnStr
    oConn.CommandTimeout = 900
    
    rcSet.Open sqlStr, oConn
    If Not rcSet.EOF Then
        Sheets(1).Range("A1").CopyFromRecordset rcSet
    End If
    
    ' 正常流程释放资源
    rcSet.Close
    oConn.Close
    Set rcSet = Nothing
    Set oConn = Nothing
    Exit Sub
    
ErrHandle:
    MsgBox "执行出错：" & Err.Description, vbCritical
    ' 异常流程释放资源
    If Not rcSet Is Nothing Then
        If rcSet.State = adStateOpen Then rcSet.Close
        Set rcSet = Nothing
    End If
    If Not oConn Is Nothing Then
        If oConn.State = adStateOpen Then oConn.Close
        Set oConn = Nothing
    End If
End Sub

其他说明 #

如果本地安装的是其他版本的MySQL ODBC驱动，只需将连接字符串中Driver参数的版本号替换为实际安装的版本即可正常使用。

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