如何用简单MsgBox处理SQL Server连接报错问题？

原代码的问题在于，当Cn.Open执行失败时会直接抛出运行时错误，不会进入后续的If Cn.State判断逻辑。要实现连接错误时弹出提示而非程序报错，需要添加错误捕获机制，以下是修改后的代码：

Sub ADOExcelSQLServer()
    Dim Cn As ADODB.Connection
    Dim server_name As String
    Dim database_name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Dim ws As Worksheet
    
    Set rs = New ADODB.Recordset
    Set ws = ActiveSheet
    
    server_name = "192.168.x.xxx\SQLEXPRESS"
    database_name = "ABC_System"
    User_ID = "xx"
    Password = "12345"
    SQLStr = "SELECT * FROM dbo.Tbl_anb"
    
    Set Cn = New ADODB.Connection
    
    ' 开启错误捕获，遇到错误时继续执行下一行代码
    On Error Resume Next
    Cn.Open "Driver={SQL Server};Server=" & server_name & ";Database=" & database_name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    
    ' 检查是否发生连接错误
    If Err.Number <> 0 Then
        MsgBox "连接失败：" & Err.Description, vbExclamation, "错误提示"
    ElseIf Cn.State = 1 Then
        Debug.Print "Connected!"
        MsgBox "连接成功", vbInformation, "提示"
        ' 可在此添加后续SQL执行逻辑，比如rs.Open SQLStr, Cn等
    Else
        MsgBox "未建立连接", vbExclamation, "提示"
    End If
    
    ' 清理资源：关闭连接并释放对象
    If Cn.State = 1 Then Cn.Close
    Set Cn = Nothing
    Set rs = Nothing
    
    ' 恢复默认错误处理机制
    On Error GoTo 0
End Sub

关键修改说明 #

• On Error Resume Next：开启错误捕获，让代码遇到错误时不会中断，转而执行后续判断逻辑
• Err.Number检查：连接操作后通过错误编号判断是否出错，Err.Description可显示具体错误原因
• 资源清理：无论连接成功或失败，最后都要关闭连接并释放对象，避免资源泄漏
• 恢复错误处理：用On Error GoTo 0恢复默认错误处理机制，防止影响其他代码执行

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