Excel 2016运行32位ODBC旧宏报数据源未找到错误如何解决

• 待适配的宏在Excel 2013（32位，依赖32位Microsoft Excel ODBC驱动）环境可正常运行，宏存储为XLS格式
• 迁移至搭载64位Excel驱动的Excel 2016环境运行时，触发报错：[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
• 经定位，报错触发位置为VBA代码With cn块内的.Open数据库连接语句，原故障代码如下：

Function ReportGroup(strSQL33 As String) As Recordset
    Dim ocmd As Command
    Set ocmd = New Command
    Dim ors As ADODB.Recordset
    Dim r As Long
    Dim intIndx As Long
    Dim cn As ADODB.Connection
    Dim strFile As String
    strFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";"
    Set ors = Nothing
    Set cn = Nothing
    Set cn = New ADODB.Connection
    Set ors = New Recordset
    'The connection
    With cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
        "DBQ=" & strFile & "; ReadOnly=False;"
        .Open
    End With
    With ocmd
      .CommandText = strSQL33
      .CommandType = adCmdText
      .ActiveConnection = cn
      Set ors = .Execute
    End With
    Set ReportGroup = ors
End Function

原代码连接字符串硬编码了32位环境专属的旧版ODBC驱动名Microsoft Excel Driver (*.xls)，64位Excel 2016默认搭载的是ACE架构的64位Excel ODBC驱动，驱动名称、架构均不匹配，导致ODBC驱动管理器找不到对应驱动触发报错；另外原代码拼接文件路径时末尾多写了冗余分号，也可能引发路径识别异常。

方案1：修改代码实现全环境兼容（推荐） #

通过VBA编译常量自动识别当前运行的Office架构，匹配对应驱动名称，同时修正路径拼接的冗余字符，修改后可同时兼容32/64位、2013-2021等多版本Excel环境，无需调整终端用户配置。
修改后的完整代码如下：

Function ReportGroup(strSQL33 As String) As Recordset
    Dim ocmd As Command
    Set ocmd = New Command
    Dim ors As ADODB.Recordset
    Dim r As Long
    Dim intIndx As Long
    Dim cn As ADODB.Connection
    Dim strFile As String
    Dim strDriver As String
    ' 移除原代码路径末尾冗余分号
    strFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    Set ors = Nothing
    Set cn = Nothing
    Set cn = New ADODB.Connection
    Set ors = New Recordset
    
    ' 自动识别Office架构匹配对应ODBC驱动
    #If VBA7 Then
        ' 64位Office匹配新版ACE驱动
        strDriver = "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
    #Else
        ' 32位旧环境兼容原有驱动
        strDriver = "Microsoft Excel Driver (*.xls)"
    #End If
    
    ' 建立数据库连接
    With cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={" & strDriver & "};" & _
        "DBQ=" & strFile & "; ReadOnly=False;"
        .Open
    End With
    With ocmd
      .CommandText = strSQL33
      .CommandType = adCmdText
      .ActiveConnection = cn
      Set ors = .Execute
    End With
    Set ReportGroup = ors
End Function

方案2：调整运行环境适配旧代码（不推荐） #

如果不修改原有代码，可通过调整运行环境解决报错，可选操作如下：

• 卸载当前设备的64位Office，安装32位版本Excel 2016，直接匹配原代码依赖的32位驱动
• 保留64位Office的前提下，手动安装64位版本的Microsoft Access Database Engine可再发行组件，安装完成后打开64位ODBC数据源管理器，确认对应Excel驱动已正常注册

注意：环境适配方案仅能解决单台设备的报错问题，若宏需要分发给不同架构、不同版本Office的用户使用，仍会反复触发同类报错，优先选择代码适配方案。

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