using on error and resume for odbc connection errors RRS feed

  • Question

  • I have a Access FE with Several SQL BEs available accross a VPN.  When processing Scheduled Macros to transfere and update data I may loose connectivity for brief period.  This causes me to have to either step throught the Macro or re run the entire macro.  The later can not be done if the data has been transformed..

    Is there a way to use onerror and resume with a loop count to retry the failed step in the macro/VB for a few times untill the connection recovers.(Usually seconds).  Or trap the error.

    I have converted this test to VB and I think I get stuck in a loop.

    Following is without the next x loop as I had problems getting it to work.

    Function Macro1()

        DoCmd.SetWarnings False
    On Error GoTo Macro1_Err

        DoCmd.OpenQuery "Query1", acViewNormal, acEdit

        Exit Function



    The above was tested by starting with the VPN down and restoring after started. (while running)

    • Moved by Mike Feng Wednesday, February 13, 2013 2:37 AM
    Saturday, February 2, 2013 7:05 PM