Access form ODBC error using "GET DIAGNOSTICS"

[Andrew Betteridge]

It's well known that Accesss forms don't return the exact ODBC error message. If you perform an insert without setting a NO NULL field to a value it just returns "ODBC call failed" rather than e.g. "'Field ''table1col3Nonnull'' doesn''t have a default value'".

You can use DAO or ADO and return the precise error after performing a query in DAO or ADO but when the query is called by an Access form the only error returned is "ODBC call failed".

I tried using this function:

CREATE FUNCTION `new_function` ()
RETURNS VARCHAR(5)
BEGIN
GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT, @p3 = MYSQL_ERRNO;
RETURN @p1;
END

The function works fine when called from MySQL workbench but returns null when called from VBA e.g.:

Set rs = db.OpenRecordset("SELECT mydb.new_function();", dbOpenSnapshot, dbSQLPassThrough)
Debug.Print rs.Fields(0)

This returns null (and subsequent calls to the function from within MySQL workbench does return the error code meaning that the diagnostics area is still populated with the error.

When the function is changed to:

CREATE FUNCTION `new_function` ()
RETURNS VARCHAR(5)
BEGIN
GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT, @p3 = MYSQL_ERRNO;
RETURN "Testing";
END

Set rs = db.OpenRecordset("SELECT mydb.new_function();", dbOpenSnapshot, dbSQLPassThrough)
Debug.Print rs.Fields(0)

Returns "Testing" as expected.

I've also tried "SHOW ERRORS;" but this returns null as well.

Does anyone know if ODBC/VBA can return variables ok? Or is it just that "GET DIAGNOSTICS" doesn't work when called from ODBC/VBA?

Or does anyone know of another method to return the precise error?