Results 1 to 12 of 12
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    vba stop code execution if append query fails

    I have a Public Function that imports a file and then does several queries. It imports every file in a folder. I need a way for it to stop and notify the user if the append query fails, then ask the user to click ok to continue and it will then finish the rest of the files.



    Here is what happens.
    1. I have code that imports all the files in a folder one at a time
    2. It imports each file and then runs several queries
    3. After the initial import I want to append the id key to a table to check for duplicates
    4. if a duplicate is found i want it to notify the user the file name that has failed
    5. Then allow the user to click ok and finish all the other files


    I already have the import done and I have created a query that holds the import date and id of each record imported. I just need a way to check it for duplicates and if found stop the process for THAT ONE file and then when the user clicks ok start it up again and finish the rest of the files.

    Thanks

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    ensure you use dbfailonerror in your currentdb.execute statement.

    and then use standard error control method (on error goto ...) to manage the error

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Thanks, that works but it also causes other problems due to loops.

    I have decided to go ahead and let the code execute but would like it to stop and show an error with a variable. I dont see that option on database.execute method. What would be the best way to accomplish this?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without seeing how you code is constructed - I can only suggest something like


    on error resume next
    currentdb.execute (....
    if err.number<>0 then msgbox err.number & " " & err.description
    on error goto 0

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have decided to go ahead and let the code execute
    That decision would not pass the logic test for many of us.
    If you are bent on doing that anyway, I suggest you take a look at Transactions. You could roll back the changes if there is an error.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I fear that Transactions may be too complex for me. I am a beginner to vba.
    Currently I have

    Set qdf = db.QueryDefs("qryImport_AppendBIV")
    qdf.Execute
    If Err.Number <> 0 Then MsgBox Err.Number & " " & Err.Description
    On Error GoTo 0

    but even though the query fails it does not give the error message or stop the code execution. When i try this query manually i get the error box

    Click image for larger version. 

Name:	appendError.PNG 
Views:	16 
Size:	15.2 KB 
ID:	25533

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    take a look at post #4 again and see what you are missing

  8. #8
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    If you are referring to On Error Resume next, i have tried that

    Set qdf = db.QueryDefs("qryImport_AppendBIV")
    On Error Resume Next
    qdf.Execute
    If Err.Number <> 0 Then MsgBox Err.Number & "--" & Err.Description
    On Error GoTo 0

    As well as
    On Error Resume Next
    Set qdf = db.QueryDefs("qryImport_AppendBIV")
    qdf.Execute
    If Err.Number <> 0 Then MsgBox Err.Number & "--" & Err.Description
    On Error GoTo 0


    I have verified that the query does fail because it is not appending another record. I just need to to completely stop the sub , but it is going forward and running the extra queries after the append query.

    Thanks for any help

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    then exit the function or sub

    If Err.Number <> 0 Then
    MsgBox Err.Number & "--" & Err.Description
    exit sub
    end if

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In case you are interested in how a transaction might be written using your code sample...

    Code:
    Dim wrk As DAO.Workspace
    Dim qdf As DAO.QueryDef
    
    Set wrk = DBEngine(0)
    Set qdf = db.QueryDefs("qryImport_AppendBIV")
    
    On Error GoTo transErr
    'Begin transaction
      wrk.BeginTrans
    
      qdf.Execute, dbfailonerror
    
      'Commit transaction
    wrk.CommitTrans dbForceOSFlush
    
    transExit:
    'Clean up
    Set wrk = Nothing
    Set qdf = Nothing
    Exit Sub
    
    transErr:
    wrk.Rollback
    MsgBox Err.Number & "--" & Err.Description
    Resume transExit
    
    End Sub
    I think it's a bit cleaner, not having to manage multiple GoTo's. More importantly, using the dbfailonerror parameter with a transaction will roll back any table edits. Otherwise, you may find that an Execute method will alter some records even though it fails to complete. What I don't see in your code is explicit declaration of the qdf type. I always declare DAO types rather than risk errors due to using ADO methods, plus it doesn't require Access to figure out which library has been referenced.

    The code logic: If no error , commit the transaction, clean up and exit the sub. Ensure disk transfer has completed (dbForceOSFlush) in case user performs an action that interrupts the disk write.
    If an error occurs, go to the error handler and roll back the transaction THEN present the message box. User actions should not be taking place during a transaction, and this includes message boxes. Then resume at the cleanup point. With code like this, I cannot understate the importance of maintaining data backups when testing. I may not have the sequence of events exactly as you want, but I hope you can follow the reasoning behind the code example.
    NOTE: this is air code (untested) so called because it's generated within the air space between my ears

    What I don't see in any of the posts is how the multiple files part is being handled. If you're calling this function x times as you loop through a set of files in a folder, it's not the best method. Better to do multiple table updates in the same procedure, although not necessarily in the same transaction.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Sorry Ajax, I though On Error Go to 0 effectively was the same as Exit Sub. I also did not mention that that code bit was in a bunch of other code and when I try to use it I get

    Compile Error:
    Exit Sub not allowed in Function or Property

    This is a project that was worked on before me. I was able to use some of what you recommended and just moved it
    ==============================
    A BUNCH OF CODE HERE
    ===========================================
    If ImportData(MySourcePath & sScrubFileName) <> SuccessResult Then

    '***TO DO **** Need to delete the scrub file OR add to the ImportData procedure
    AddToAlert "Unable to import... Error renaming scrub filename:" & vbCrLf & sScrubFileName
    mFiles(2, ctr) = "Fail"
    GoTo NextFile
    Else
    Set wrk = DBEngine.Workspaces(0)
    Set db = CurrentDb()

    'Check BID to see if it already exists
    CurrentDb.Execute "qryImport_AppendBIV", dbFailOnError
    On Error GoTo SubEXIT

    wrk.BeginTrans
    Set qdf = db.QueryDefs("qryImport_DeleteInvalidRecords")
    qdf.Execute


    Now it does fail but pulls up the VBA editor. At least it does stop the rest of the code from running even if it is ugly.

    I do see the developer before used transactions as you can see from the wrk.BeginTrans. I will try to find a way to have it quite more gracefully than just show the vba editor.

    Thanks for the help and thanks to Micron for the explanation. I will be using that so I can research more so that I can possibly rewrite this bit or use it when I write new code (I am a Unix admin, not a vba coder)
    Thanks

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Compile Error:
    Exit Sub not allowed in Function or Property
    if your code is in a function, you would use Exit Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 09-24-2014, 12:37 PM
  2. How to turn off prompt when running append query from code
    By GraeagleBill in forum Programming
    Replies: 5
    Last Post: 07-22-2013, 03:35 PM
  3. Replies: 1
    Last Post: 12-09-2011, 07:34 AM
  4. Inconsistent Code Execution
    By Paul H in forum Forms
    Replies: 2
    Last Post: 09-19-2011, 10:06 AM
  5. Code works in full, fails in Runtime
    By stephenaa5 in forum Programming
    Replies: 3
    Last Post: 09-14-2010, 12:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums