Trapping ‘Operation Cancelled’ during Append Query (2000)

Topic

New Reply

I would like to capture a NO user response from the “CAN’T APPEND RECORDS…RUN QUERY?” (I paraphrase) error that ACCESS gives when an append query output violates the destination table validation rules. I Know I have seen (and at times captured) a 3059 OPERATION CANCELLED BY USER pop-up but now that I have the code written I can’t trigger the error. Is there some property that must be set to capture this response? Thanks.

Reply | Quote

Replies

If you’re referring to the error msg like the one illustrated, AFAIK this type of error cannot be trapped. See this MSKB article:

ACC2000: Cannot Trap Import Errors in Visual Basic for Applications

Article states in part: “The same rules that apply to manual data entry also apply to methods for importing data when using Visual Basic for Applications. Therefore, any violations of rules, such as referential integrity, validation rules, or the Required property, will result in an incomplete import. In Microsoft Access 2000, no trappable error is generated and if the procedure disables system messages by using the SetWarnings statement, there is no indication that any problem occurred. ” Though not exactly same situation, the same principle is applicable to running action queries, whether in UI or via VBA using DoCmd methods, you cannot trap this type of error.

When appending, updating, or deleting records programatically the recommended approach is to use the ADO Connection Execute method, or DAO equivalent. Both the ADO Connection object and the DAO Database & QueryDef objects have a RecordsAffected property that can be used to determine how many records were affected by the query. In addition, both the ADO Connection object and DAO DBEngine object have an Errors collection that you can loop thru to determine what specific error(s) occurred when trying to execute the query or SQL statement. The DAO errors tend to be more useful; when an ADO action query fails to execute due to key violations, etc, you usually get this standard error msg:

Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.

As noted in MSKB article, disabling standard warnings with SetWarnings method is NOT a good idea, you have no way of determining if action query succeeded, failed, or only partially succeeded. Recommend use ADO or DAO Execute method for append, update, and delete queries, with appropriate error handling enabled.

HTH

Reply | Quote

Thanks for your response. I got the following technique for executing an action query to work exactly as desired (I wrapped the code in a boolean function) as shown below. A couple of notes:

1)Using Docmd.openquery with a NO response to the “violations detected – continue?” dialog DOES sometimes raise a 3059 ‘user cancelled operation’ error and I remember at least once being able to trap it. Don’t know under what circumstances however and couldn’t reproduce it.

2) Controls on forms coded for query criteria did not seem to work using this technique. I had to code a parameter and build its value in VBA, as shown below.

3) DBFAILONERROR option is required or error will not be trapped.

4)Gratifyingly, the ERROR object returns exactly what error took place (ie what column failed).
:

‘Calling code:
Set Qdf = CurrentDb.QueryDefs(“qryEstA_Cost_100A”)
Qdf.Parameters(“ctlsub”) = Me.ctlSub
If Not fDaoQry(Qdf) Then Exit Function

Function:
Function fDaoQry(Qdf As DAO.QueryDef) As Boolean
””””””””””””””””””””””””
Dim ErrNum, ErrDsc
”””””””””
On Error Resume Next
Qdf.Execute dbFailOnError
ErrNum = Err
ErrDsc = Err.Description
On Error GoTo 0
”””””””’
If ErrNum 0 Then
MsgBox “Error ” & ErrNum & ” executing ” & Qdf.Name & “: ” & ErrDsc, vbCritical
End If
””””’
fDaoQry = (ErrNum = 0)
End Function
;comments.

Reply | Quote

Thanks for your response. I got the following technique for executing an action query to work exactly as desired (I wrapped the code in a boolean function) as shown below. A couple of notes:

1)Using Docmd.openquery with a NO response to the “violations detected – continue?” dialog DOES sometimes raise a 3059 ‘user cancelled operation’ error and I remember at least once being able to trap it. Don’t know under what circumstances however and couldn’t reproduce it.

2) Controls on forms coded for query criteria did not seem to work using this technique. I had to code a parameter and build its value in VBA, as shown below.

3) DBFAILONERROR option is required or error will not be trapped.

4)Gratifyingly, the ERROR object returns exactly what error took place (ie what column failed).
:

‘Calling code:
Set Qdf = CurrentDb.QueryDefs(“qryEstA_Cost_100A”)
Qdf.Parameters(“ctlsub”) = Me.ctlSub
If Not fDaoQry(Qdf) Then Exit Function

Function:
Function fDaoQry(Qdf As DAO.QueryDef) As Boolean
””””””””””””””””””””””””
Dim ErrNum, ErrDsc
”””””””””
On Error Resume Next
Qdf.Execute dbFailOnError
ErrNum = Err
ErrDsc = Err.Description
On Error GoTo 0
”””””””’
If ErrNum 0 Then
MsgBox “Error ” & ErrNum & ” executing ” & Qdf.Name & “: ” & ErrDsc, vbCritical
End If
””””’
fDaoQry = (ErrNum = 0)
End Function
;comments.

Reply | Quote

If you’re referring to the error msg like the one illustrated, AFAIK this type of error cannot be trapped. See this MSKB article:

ACC2000: Cannot Trap Import Errors in Visual Basic for Applications

Article states in part: “The same rules that apply to manual data entry also apply to methods for importing data when using Visual Basic for Applications. Therefore, any violations of rules, such as referential integrity, validation rules, or the Required property, will result in an incomplete import. In Microsoft Access 2000, no trappable error is generated and if the procedure disables system messages by using the SetWarnings statement, there is no indication that any problem occurred. ” Though not exactly same situation, the same principle is applicable to running action queries, whether in UI or via VBA using DoCmd methods, you cannot trap this type of error.

When appending, updating, or deleting records programatically the recommended approach is to use the ADO Connection Execute method, or DAO equivalent. Both the ADO Connection object and the DAO Database & QueryDef objects have a RecordsAffected property that can be used to determine how many records were affected by the query. In addition, both the ADO Connection object and DAO DBEngine object have an Errors collection that you can loop thru to determine what specific error(s) occurred when trying to execute the query or SQL statement. The DAO errors tend to be more useful; when an ADO action query fails to execute due to key violations, etc, you usually get this standard error msg:

Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.

As noted in MSKB article, disabling standard warnings with SetWarnings method is NOT a good idea, you have no way of determining if action query succeeded, failed, or only partially succeeded. Recommend use ADO or DAO Execute method for append, update, and delete queries, with appropriate error handling enabled.

HTH

Reply | Quote

Adding to Mark’s comments, there is a partial work-around for import errors. When an import error occurs, in most circumstances Access creates an “Import Errors” table, and writes records that describes the problem to that table. If you are automating an import process, you can check for the existance of that table, and if it exists, you can then warn the user. That doesn’t solve all the problems, but it does provide some help.

Reply | Quote

Adding to Mark’s comments, there is a partial work-around for import errors. When an import error occurs, in most circumstances Access creates an “Import Errors” table, and writes records that describes the problem to that table. If you are automating an import process, you can check for the existance of that table, and if it exists, you can then warn the user. That doesn’t solve all the problems, but it does provide some help.

Reply | Quote