How to get SQL Query Error Details when QueryTable.Refresh fails

Question

Say I have this simple bit of VBA that refreshes a query table:

With aListObject.QueryTable
    .refresh BackgroundQuery:=False
End With

If the above table returns an error from the SQL engine upon refreshing, then how do I obtain the actual SQL error details?

E.g. I want this type of message, which is returned in SQL Server Management Studio:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

I can add error handling to the VBA to return the VBA error as such:

On Error Resume Next    
With aListObject.QueryTable
    .refresh BackgroundQuery:=False
    if err.number > 0 then msgbox err.Description
End With
On Error GoTo 0

But this only returns this message:

SQL Syntax Error

How do I return the full SQL error message details?

I have looked at the following but can't find anything that jumps out there:

QueryTable object (https://learn.microsoft.com/en-us/office/vba/api/excel.querytable)
QueryTable.Refresh method (https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.refresh)

Parfait · Accepted Answer · 2020-02-21 16:05:16Z

To return specific errors from connected ODBC driver, consider building QueryTable from an ADO recordset that processes the SQL prior to passing into QueryTable. As QueryTables docs indicate, the connection argument can take a variety of forms including recordsets (and even the example in docs shows how such ADO recordset connection):

The data source for the query table can be one of the following:

A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form ODBC;<connection string>.

A QueryTable object from which the query information is initially copied, including the connection string and the SQL text, but not including the Destination range. Specifying a QueryTable object causes the Sql argument to be ignored.

An ADO or DAO Recordset object. Data is read from the ADO or DAO recordset. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. The resulting query table cannot be edited.

...

Adjusted VBA with error handling:

Sub Get_SQL_Data()
On Error GoTo ErrHandle
    Dim conn As ADODB.Connection, rst As ADODB.Recordset
    Dim sqlstring As String, connstring As String

    ' OPEN CONNECTION
    Set conn = New ADODB.Connection
    connstring = "Driver={SQL Server}; ..."
    conn.Open connstring

    ' OPEN RECORDSET
    Set rst = New ADODB.Recordset
    sqlstring = "SELECT * FROM mytable"
    rst.Open sqlstring, conn

    With Worksheets("MAIN").QueryTables.Add( _
           Connection:=rst, _
           Destination:=Range("A1"))
       .Name = "SQL_DATA"
       .FieldNames = True
       .Refresh BackgroundQuery:=False
    End With

    rst.Close: conn.Close

ExitHandle:
    Set rst = Nothing: Set conn = Nothing
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle
End Sub

Thanks for you detailed answer. Using the ABO connection seems a good way to get the error message before inserting the recordset into the querytable.Thanks!

Collectives™ on Stack Overflow

How to get SQL Query Error Details when QueryTable.Refresh fails

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related