MySQL Forums
Forum List  »  Microsoft Access

Access form ODBC error using "GET DIAGNOSTICS"
Posted by: Andrew Betteridge
Date: December 16, 2015 05:52AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Access form ODBC error using "GET DIAGNOSTICS"
2517
December 16, 2015 05:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.