How to capture QueryDef error present when run interactively via VBA

mdlueck · May 8, 2013

I have long noticed / been annoyed with the lack of detailed error information made available to VBA running a given DAO.QueryDef object whereas running the same QueryDef interactively has more error information. (At least this time all of Access is not crashing when executing the QueryDef via VBA code... sometimes that has been the difference!) Example:

Via Access GUI:

Via VBA I see a mere:

Is there some way to capture in VBA code the rest of the error message? In this specific example, the error coming back from SQL Server.

MarkK · May 9, 2013

Check out the DAO.DBEngine.Errors collection, which may contain one or more DAO.Error objects, as opposed 'Err' which is a VBA.ErrObject object. DAO does not raise VBA errors by default, so make sure you include the dbFailOnError option whenever you run a DAO operation in VBA, but even then, VBA can only support one error at a time since there can only ever be ONE instance of a VBA.ErrObject, and a data operation may generate multiple errors.
hth

mdlueck · May 9, 2013

lagbolt said:
Check out the DAO.DBEngine.Errors collection, which may contain one or more DAO.Error objects

Come again?

I have placed a watch on DAO and DAO.DBEngine.Errors and do not see any object in the context of this function where the error occurs.

Putting a watch on the daoQDFfe object does show the valid QueryDef object, however I see no DBEngine nor Errors anywhere within that object.

Oh, and I recalled that the daoQDFfe object was created off of the daoDB object. So I placed a watch on that object and do not see what you indicated either.

Code snipped is as follows:

Code:

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Build thedaoQDFfe FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Execute
    .Close
  End With

Update:

I put a Watch simply on DBEngine and see that object exists. Here is what was inside that object:

mdlueck · May 9, 2013

lagbolt said:
DAO does not raise VBA errors by default, so make sure you include the dbFailOnError option whenever you run a DAO operation in VBA

Aaaahhhh.... NOW we're getting somewhere...

Code:

  'Build thedaoQDFfe FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Execute [COLOR=Blue][B]dbFailOnError[/B][/COLOR]
    .Close
  End With

And I see....

I will step back behind my curtain and polish up a DAO Error Handler now!

Thank you!

mdlueck · May 9, 2013

Oy! That was cOmPlIcAtEd!

DAO objects are not as friendly as ADO objects. DAO Objects appear more fragial than other types of objects. I was not able to ship off the DAO.Errors object to shared code for interrogation / processing... so had to do it right in the shared function that is executing the DAO query.

Code snippet right in that function's error handler as follows:

Code:

Err_dbutils_RefreshLocalTmpTbl:
  'OK to expect 3325 type errors since an IF might prevent the SELECT from executing
  If Err.Number = 3325 Then
    dbutils_RefreshLocalTmpTbl = True
  Else
    'Further error handling here for other error types
    Dim ObjErrorHandlerDAO As New clsObjErrorHandlerDAO
    ObjErrorHandlerDAO.ErrObj = Err
    strDAOErrorMsg = "DAOErrors.Count: " & DBEngine.Errors.Count
    'Check for the number of errors
    If DBEngine.Errors.Count > 0 Then
      'Enumerate through each error found in the object
      For Each objDAOError In DBEngine.Errors
        strDAOErrorMsg = strDAOErrorMsg & vbCrLf & "Source:       " & objDAOError.Source
        strDAOErrorMsg = strDAOErrorMsg & vbCrLf & "Description:  " & objDAOError.Description
        strDAOErrorMsg = strDAOErrorMsg & vbCrLf & "Number:       " & objDAOError.Number & vbCrLf
      Next objDAOError
    End If
    ObjErrorHandlerDAO.DAOErrorMsg = strDAOErrorMsg
    ObjErrorHandlerDAO.SQLbe = strSQLbe
    ObjErrorHandlerDAO.SQLfe = strSQLfe
    Call errorhandler_MsgBox("Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl()", , ObjErrorHandlerDAO)
    dbutils_RefreshLocalTmpTbl = False
  End If
  Resume Exit_dbutils_RefreshLocalTmpTbl

Sample error message the error handler now produces when it detects a clsObjErrorHandlerDAO additional error information object has been passed in:

Thank you very much, lagbolt, for your assistance!

MarkK · May 9, 2013

You're welcome Michael. You always have interesting questions, and I learn stuff from your work so thanks for posting back with what you've explored.

And maybe you need to Refresh that collection too? A collection is very practical to use, but costly to create, so it's an open question as to when DAO rebuilds it. Possibly you force that rebuild when an error is raised using dbFailOnError.

But it might end up being tidier to execute a DAO process, explicitly Refresh the DAO.Errors collection (DBEngine.Errors.Refresh), and THEN handle problems in-line, so to speak, rather than trap the error in VBA at all.

Just a thought,
Cheers,

mdlueck · Dec 11, 2013

Unfortunately even the solution I found to get more of an error message back from DAO.QueryDef objects does not guarantee that the error message returned to VBA is the same as what would have been displayed running the QueryDef objects interactively via the Access UI. Case in point example:

Error returned to VBA:

Error displayed running the QueryDef object via the Access UI:

Root cause of the error... numeric column in the FE temp table set to Integer datatype, trying to download too large of a number. A quick change to Double and the query / report works correctly now.

With error handling it is better than the application just crashing. It was just a bit of a wild goose chase to track down the root of the error.

Galaxiom · Dec 11, 2013

mdlueck said:
Root cause of the error... numeric column in the FE temp table set to Integer datatype, trying to download too large of a number. A quick change to Double and the query / report works correctly now.

Wouldn't Long be more appropriate? Or were they way too big?

mdlueck · Dec 12, 2013

Galaxiom said:
Wouldn't Long be more appropriate? Or were they way too big?

Rushing I was forgetting that this query runs against SQL Server, not the iSeries. So yes, in this case I know the schema of the SQL Server DB and a Long is big enough... Integer in SQL Server.

Numbers coming from the iSeries are much harder to guesstimate accurately the upper possible range. Somehow I was able to obtain one view of the iSeries numeric datatypes as a certain specific precision "Decimal (?,?)" format and for those queries mapped the iSeries fields to those Access / SQL Server data types... then end up using Single / Double as the VBA variable type to handle the "Decimal (?,?)" field data.

Update: Actually I was sort of correct. Turns out this Stored Procedure actually checks two different tables on the server to obtain a possible value for this field. One, the first checked, contains data coming from the iSeries, decimal(15, 0) format. The other is purely in my application, thus Long (Int) would be correct. So I will go with the decimal(15, 0) data type for the FE temp table. CoMpLiCaTeD!

How to capture QueryDef error present when run interactively via VBA

mdlueck

Sr. Application Developer

Attachments

MarkK

bit cruncher

mdlueck

Sr. Application Developer

Attachments

mdlueck

Sr. Application Developer

Attachments

mdlueck

Sr. Application Developer

Attachments

MarkK

bit cruncher

mdlueck

Sr. Application Developer

Attachments

Galaxiom

Super Moderator

mdlueck

Sr. Application Developer

Similar threads

Users who are viewing this thread