Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error routines with queries (Win XP Access 2003)

    From a form, I run a macro which in turn runs an Append query. What precautions should I take to be sure that there is no problem with the disk operation in the Append query? For example, BeginTrans, CommitTrans and Rollback. Where should I put Error routines?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error routines with queries (Win XP Access 2003)

    I would use VBA code instead of a macro. Two options:

    - Pass the SQL for the append query to DoCmd.RunSQL and set the UseTransaction argument to True:

    DoCmd.RunSQL "INSERT INTO ...", True

    - Use the Execute method of CurrentDb and specify dbFailOnError as option:

    CurrentDb.Execute "qryAppend", dbFailOnError

    The complete code could look like this (I've taken the On Click event procedure of a command button as example):

    Private Sub cmdAppend_Click()
    On Error GoTo ErrHandler
    CurrentDb.Execute "qryAppend", dbFailOnError
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error routines with queries (Win XP Access 2003)

    Digging into the dbFailOnUpdate method. I got this from Execute Method Help. "Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query." Is an Append query assumed to be an update query?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error routines with queries (Win XP Access 2003)

    No, an append query is not the same as an update query, but I think they meant action queries (i.e. queries that modify records in a table) in general.

Posting Permissions

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