1

I'm executing a query using ADO:

Dim connLocal As ADODB.Connection

    Set connLocal = CurrentProject.Connection

    strSQL = "INSERT INTO dbo_tbl_ErrorLog SELECT tbl_ErrorLog.* FROM tbl_ErrorLog;"

    On Error GoTo ErrorSQL
    connLocal.Execute strSQL
    .....
ErrorSQL:
    lErrNo = Err.Number
    strErrDesc = Err.Description
    If InStr(1, strErrDesc, "ODBC") Then
        Dim i As Long
        Dim strErr As String
        For i = 0 To connLocal.Errors.Count - 1
            strErrDesc = strErrDesc & vbCrLf & connRemote.Errors(i).Number & " - " & connLocal.Errors(i).Description
        Next i
    End If
    .....

In the query the table dbo_tbl_ErrorLog is linked MS SQL table, tbl_ErrorLog - linked MS Access table.

In case of SQL query error I receive an error:

-2147467259 - ODBC--call failed.

And I when I'm trying to get error details, the collection connLocal.Errors contains just one item "ODBC--call failed". Is it possible to retrieve full error details in such case of mixed query?

Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • You sure that isn't supposed to be `dbo.tbl_ErrorLog`? – UnhandledExcepSean Mar 14 '19 at 14:43
  • Yes, the name is correct, this is an alias of MS SQL linked table. Normally this query works as expected, but it fails, for instance, in case of primary key violation and I'd like to show this error to user. – Sergey S. Mar 14 '19 at 14:47
  • Not what you are asking, but you can do that with DAO: https://stackoverflow.com/questions/730414/determine-real-cause-of-odbc-failure-error-3146-with-ms-access – Andre Mar 14 '19 at 15:12

1 Answers1

1

Through ADO, unfortunately, there's not as far as I know.

The Access database engine doesn't propagate errors from ODBC data sources to ADO.

Your ADO connection is one to the Access Database Engine, and only receives the errors it raises.

If you need to trace this specific error, you can do so by enabling ODBC tracing for the ODBC connection you're using to connect Access to SQL server. The trace log should include any errors thrown by SQL Server, and also the queries Access uses to move the data to SQL server.

If you have sufficient permissions, you can also query the error log with sp_readerrorlog after the error gets raised, either using a pass-through query or separate ADO connection to SQL Server.

Erik A
  • 31,639
  • 12
  • 42
  • 67