Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping 'Operation Cancelled' during Append Query (2000)

    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.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Trapping 'Operation Cancelled' during Append Query (2000)

    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
    Attached Images Attached Images

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Trapping 'Operation Cancelled' during Append Query (2000)

    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.
    Wendell

  4. #4
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping 'Operation Cancelled' during Append Query (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •