VBA连接MySQL：如何兼容8.0与8.2版本ODBC驱动？

不需要重新安装8.0版本驱动，你可以通过以下两种方法让代码同时兼容两个版本：

方法一：枚举系统已安装的驱动，自动选择可用版本 #

通过ADODB枚举器获取系统中已安装的ODBC驱动列表，筛选出MySQL Unicode驱动，优先选择高版本：

Function GetMySQLDriver() As String
    Dim objEnumerator As Object
    Dim objDriver As Object
    Dim driverName As String
    Dim bestMatch As String
    
    Set objEnumerator = CreateObject("ADODB.Enumerator")
    For Each objDriver In objEnumerator.Enumerate("Microsoft OLE DB Service Providers\ODBC Drivers")
        driverName = objDriver.Name
        ' 匹配MySQL Unicode驱动，包含8.0或8.2版本
        If InStr(driverName, "MySQL ODBC") > 0 And InStr(driverName, "Unicode Driver") > 0 Then
            ' 优先选择8.2版本
            If InStr(driverName, "8.2") > 0 Then
                GetMySQLDriver = driverName
                Exit Function
            ' 记录8.0版本作为备选
            ElseIf InStr(driverName, "8.0") > 0 And bestMatch = "" Then
                bestMatch = driverName
            End If
        End If
    Next objDriver
    
    ' 返回找到的驱动，无兼容驱动则抛出错误
    If bestMatch <> "" Then
        GetMySQLDriver = bestMatch
    Else
        Err.Raise vbObjectError + 1001, , "未找到兼容的MySQL ODBC驱动"
    End If
End Function

' 使用示例
Sub ConnectToMySQL()
    Dim cn As Object
    Dim strConnection As String
    Dim SERVER As String, DB As String, UID As String, PWD As String
    
    ' 替换为你的数据库连接信息
    SERVER = "你的服务器地址"
    DB = "数据库名称"
    UID = "用户名"
    PWD = "密码"
    
    Set cn = CreateObject("ADODB.Connection")
    
    ' 动态生成兼容的连接字符串
    strConnection = "Driver={" & GetMySQLDriver() & "};SERVER=" & SERVER & _
            ";PORT=3306;DATABASE=" & DB & _
            ";UID=" & UID & ";PWD=" & PWD & ";"
    
    cn.Open strConnection
    ' 后续数据库操作...
    cn.Close
    Set cn = Nothing
End Sub

方法二：尝试连接失败后自动降级驱动 #

先尝试用8.2版本驱动连接，失败则切换到8.0版本：

Sub ConnectWithFallback()
    Dim cn As Object
    Dim strConnection82 As String, strConnection80 As String
    Dim SERVER As String, DB As String, UID As String, PWD As String
    
    ' 替换为你的数据库连接信息
    SERVER = "你的服务器地址"
    DB = "数据库名称"
    UID = "用户名"
    PWD = "密码"
    
    Set cn = CreateObject("ADODB.Connection")
    
    ' 构造两个版本的连接字符串
    strConnection82 = "Driver={MySQL ODBC 8.2 Unicode Driver};SERVER=" & SERVER & _
            ";PORT=3306;DATABASE=" & DB & _
            ";UID=" & UID & ";PWD=" & PWD & ";"
    strConnection80 = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=" & SERVER & _
            ";PORT=3306;DATABASE=" & DB & _
            ";UID=" & UID & ";PWD=" & PWD & ";"
    
    ' 优先尝试8.2驱动
    On Error Resume Next
    cn.Open strConnection82
    If Err.Number = 0 Then
        On Error GoTo 0
        Debug.Print "使用8.2驱动连接成功"
    Else
        On Error GoTo 0
        ' 8.2失败则尝试8.0
        cn.Open strConnection80
        Debug.Print "使用8.0驱动连接成功"
    End If
    
    ' 后续数据库操作...
    cn.Close
    Set cn = Nothing
End Sub

方法一更稳妥，能自动适配系统中已安装的最高兼容版本；方法二更简单直接，适合快速修改。

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