VBA连接MySQL导出数据到Excel报运行时错误2147467259(80004005)

问题场景 #

实现MySQL数据提取到Excel的功能时，运行VBA代码触发运行时错误2147467259 (80004005)，原始代码如下：

Sub connect()
Dim Password As String
Dim sqlstr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
Dim table_name As String
 
Set rs = CreateObject("ADODB.Recordset")
Server_Name = "20.20.10.20"
Database_Name = "test"
User_ID = "user1"
Password = "abcd!7B3"
table_name = "tbl_master"
 
Set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & _
        Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
 
sqlstr = "SELECT * FROM table1;"
rs.Open sqlstr, cn, adopenstatic
 
ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rs
cn.Close
MsgBox "DONE!" End sub

错误截图参考：

排查方向&修复方案 #

该错误属于ODBC连接通用错误，按以下优先级逐一排查即可：

• 驱动匹配问题（最高发）
  连接串中指定的MySQL ODBC 5.3 Unicode Driver必须和本机安装的ODBC驱动完全匹配：
  1. 确认本机已安装MySQL ODBC 5.3版本驱动，若安装的是8.0等其他版本，需要把连接串里的驱动名改成对应版本的名称，可打开系统ODBC数据源管理器查看已安装驱动的准确名称
  2. 驱动架构必须和Office架构一致：32位Office只能装32位MySQL ODBC驱动，64位Office只能装64位驱动，架构不匹配会直接报该错误
     排查时可以先在ODBC数据源管理器新建对应驱动的用户DSN，填完连接参数点测试，若DSN都无法连通，优先排查驱动、网络、权限问题，不用先调整代码。
• 连接链路&权限问题
  1. 确认本地网络可以正常访问20.20.10.20的3306端口（MySQL默认端口，若服务端修改过端口，需要在连接串中添加Port=你的端口号;参数），服务端防火墙没有拦截当前客户端IP的访问
  2. 确认数据库账号user1密码正确，且该账号拥有从当前客户端IP访问test库的权限，MySQL默认账号通常仅允许本地登录，远程访问需要单独给用户授权
• 代码逻辑问题
  1. 代码中定义了目标表为tbl_master，但实际SQL查询的是table1，若test库下不存在table1表，执行SQL时会抛错，需要把SQL里的表名改成实际要查询的表名
  2. 代码使用了adopenstatic常量，但没有引用ADODB类型库的场景下，该常量未定义会触发执行错误。晚绑定写法可以直接把该参数替换为常量对应的值3，也可以在VBA编辑器的「工具-引用」菜单中勾选Microsoft ActiveX Data Objects x.x Library完成引用
  3. 连接串可追加Option=3;参数，避免部分字符集、连接配置类异常

修正后参考代码 #

Sub connect()
    Dim Password As String
    Dim sqlstr As String
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    Dim table_name As String
    Dim rs As Object, cn As Object
    
    ' 晚绑定创建ADO对象，无需提前引用库，兼容性更好
    Set rs = CreateObject("ADODB.Recordset")
    Server_Name = "20.20.10.20"
    Database_Name = "test"
    User_ID = "user1"
    Password = "abcd!7B3"
    table_name = "tbl_master"
    
    Set cn = CreateObject("ADODB.Connection")
    ' 注意核对驱动名与本机安装版本一致，非默认端口追加Port=xxx;参数
    cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & _
            Server_Name & ";Database=" & Database_Name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";Option=3;"
    
    ' 表名与定义的目标表一致，游标类型用直接值避免常量未定义问题
    sqlstr = "SELECT * FROM " & table_name & ";"
    rs.Open sqlstr, cn, 3
    
    ' 写入数据
    ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rs
    
    ' 规范释放资源
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
    MsgBox "DONE!"
End Sub

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