Not getting error msg on failed insert query (1 Viewer)

[expat1000]

Access FE, SQL Server BE. I run a simple insert query in Access in the GUI. A no dup index on two fields in the table rejects it with an error message as it should. However

Set qry = CurrentDb.QueryDefs("query2")
qry.Execute dbSeeChanges

also fails but without an error being displayed so I can't trap it. Same goes for CurrentDb.Execute sSQL, dbSeeChanges I have preceded that statement with docmd.setwarnings(true). Is this expected behaviour?

 

[arnelgp]

maybe:

qry.Execute dbFailOnError

 

[expat1000]

maybe:

qry.Execute dbFailOnError

I think the dbSeeChanges is required. This gives me argument error

qry.Execute dbSeeChanges, dbFailOnError

 

[arnelgp]

it's because it only accepts 1 argument:

qry.Execute dbSeeChanges + dbFailOnError

 

[expat1000]

OK. That's got it - sort of. I get a generic error "ODBC--call failed" - better than nothing! Thank you.

 

[Isaac]

OK. That's got it - sort of. I get a generic error "ODBC--call failed" - better than nothing! Thank you.

Something that would be PRICELESS for Microsoft to solution....getting actual SQL Server error messages back to Access.

I've put significant time into this issue and concluded that the closest an Access/T-SQL developer can come who wants to create server-side error handling with Access FE-side knowledge of it is a stored proc
How to capture SQL Server error via MS Access, after error not generated by DAO VBA code (microsoft.com)

 

[The_Doc_Man]

Apparently there IS a way to get back an ODBC detailed code.

Handling Errors and Messages - SQL Server

Learn about what diagnostic information is returned when an application calls an ODBC function, including success or failure and detailed information.

docs.microsoft.com

Appendix A: ODBC Error Codes - ODBC API Reference

Appendix A: ODBC Error Codes

docs.microsoft.com

You get back some indication of an error from the basic call, so you call SQLGetDiagRec (get diagnostic record) to find out what went wrong.

 

[Isaac]

Apparently there IS a way to get back an ODBC detailed code.

Handling Errors and Messages - SQL Server

Learn about what diagnostic information is returned when an application calls an ODBC function, including success or failure and detailed information.

docs.microsoft.com

Appendix A: ODBC Error Codes - ODBC API Reference

Appendix A: ODBC Error Codes

docs.microsoft.com

You get back some indication of an error from the basic call, so you call SQLGetDiagRec (get diagnostic record) to find out what went wrong.

Let me know if you know how to make Access use SQLFetch odbc.

Note in the post I linked to, the great need is to return errors when executing a saved query....not just when using an ado/dao connection