ADODB Connection.Open上的VBA错误处理 [英] VBA Error handling on ADODB Connection.Open

问题描述

我在VBA中有一个ADODB连接，用于连接到SQLServer数据库。我想捕获在调用connection.Open且给定的数据库不可访问时引发的错误。

I have an ADODB connection in VBA for connecting to an SQLServer database. I want to catch the error that is raised when connection.Open is called and the given database is unreachable.

我的代码如下：

Public Function Connect() As Boolean

On Error GoTo DBError

    Dim dbServer As String
    Dim dbName As String
    Dim dbUser As String
    Dim dbPwd As String

    dbServer = DatabaseSettings.dbServer
    dbName = DatabaseSettings.dbName
    dbUser = DatabaseSettings.dbUser
    dbPwd = DatabaseSettings.dbPwd

    Dim connectionString As String
    connectionString = "Server=" & dbServer & ";Database=" & dbName & ";User Id=" & dbUser & ";Password=" & dbPwd

    Set conn = New ADODB.Connection
    conn.Provider = "sqloledb"
    With conn
        .ConnectionTimeout = 2
        .CursorLocation = adUseClient
        .Open connectionString
        .CommandTimeout = 0
    End With

Connect = True
Exit Function

DBError:
    Connect = False
End Function

我的问题是当我尝试运行此代码时使用错误的connectionString会引发错误并显示在MsgBox中，并且不会被 On Error GoTo DBError捕获。

My problem is that when i try to run this code with an incorrect connectionString an error is raised and shown in a MsgBox and not caught by the "On Error GoTo DBError".

我的错误处理代码中有问题吗？还是我需要找到另一种捕获此错误的方法？

Is there something wrong in my error handling code or do i need to find another way of catching this error?

感谢您的帮助。欢迎任何建议。

Thank you for your help. Any suggestions are welcome.

推荐答案

不确定是否是这样，但是在VBE窗口中确保使用Tools ... Options ...常规...错误捕获选项设置为未处理的错误中断。如果将其设置为打破所有错误，则可能会绕过您的处理程序。

Not sure if this is it, but in the VBE window make sure the Tools...Options...General...Error Trapping option is set to "Break on Unhandled Errors". If it were set to "Break on All Errors" this may bypass your handlers.

这篇关于ADODB Connection.Open上的VBA错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！