Catching error message from SQL Server in VBA in Excel

Question

I am doing an excel macro in order to automate some query what eventually I run in SQL Server. My problem is that I don't know how the server could alert excel if a query did not succeed.

For example, I am importing a file, and there is no syntax error, but it might result in error if bulk insert statement is not set properly. For the SQL connection I use the following:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=localhost;" & _
              "Initial Catalog=" & MyDatabase & ";" & _
              "Integrated Security=SSPI;"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open sConnString

Set rs = conn.Execute(Myquery)

If I have a syntax error while compiling the code it stops which is good. But if I have another problem, e. g. the database name is not good, the table already exists, then the program runs with no error, I only can detect when I check it in SQL Server. I really want to know somehow whether the query run has resulted in error and then code some alerting message then into my macro. How can I do that?

Every help is much appreciated!

Answer 1

The ADO connection object has an Errors collection, which you can check after running your SQL:

conn.Errors.Clear
Set rs = conn.Execute(Myquery) 

If conn.Errors.Count > 0 Then
    For i = 0 To conn.Errors.Count
        Debug.Print conn.Error(i).Number 
        Debug.Print conn.Error(i).Source 
        Debug.Print conn.Error(i).Description
    next i
End If

That should get you started. You may find that you're seeing an 'error zero' that's actually a status message; if so, you'll have some additional coding to to do.

Answer 2

I found this helpful but needed to use:

Debug.Print conn.Errors.Item(i).Description
Debug.Print conn.Errors.Item(i).Source
Debug.Print conn.Errors.Item(i).NativeError

I might be using a different connection type