0

I am transitioning us from an MS Access 2007 backend to a SQL Server 2014 backend.

When I try to do a pass through insert I don't get the full SQL Server error message.

Dim qd As QueryDef
Dim rs As DAO.Recordset

On Error GoTo save_err

Set qd = CurrentDb.CreateQueryDef("")
qd.ReturnsRecords = False

If IsNull(Me.txtCategoryName) Then
    MsgBox "enter a category name"
Else
    qd.Connect = ConStringName
    qd.sql = "INSERT INTO [Categories] (name) VALUES  ('" & Text Field & "')"
    qd.Execute

End If

In my test case, a unique constraint should raise an error that the name is duplicate and can't be inserted. Instead I just get an ODBC -- Call Failed.

If I instead try to create the pass through query in an actual query window, I get the full error.

My current error handler is a message box:

  MsgBox "FormName|SubName: " & Error$, vbCritical, "Error"

If I switch the table to a linked table, and try to insert, there is no error raised. I want an error in this case.

How do I get the full error in VBA?

Elias
  • 2,602
  • 5
  • 28
  • 57
  • Ditch DAO, and use ADODB instead. Then you can declare a `WithEvents conn As ADODB.Connection`, and handle `conn_InfoMessage` and/or `conn_ExecuteComplete`, which gives you a `pError` parameter object, which should contain your detailed error message. Or you can iterate the connection's `Errors` collection. I don't see anything equivalent browsing the DAO library members in the Object Browser (F2). – Mathieu Guindon Jul 13 '17 at 17:45
  • DAO is what Access uses. For SQL Server you'll want to use ADODB anyway. – Mathieu Guindon Jul 13 '17 at 17:47
  • I figured DAO might be the problem. It has been a while since I have worked with ADODB so I forgot the difference. – Elias Jul 13 '17 at 17:49
  • 1
    DAO is not the problem. – Gustav Jul 13 '17 at 20:10

0 Answers0