none
using onerror with resume for odbc error

    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


    Macro1_Exit:
        Exit Function

    Macro1_Err:
        Err.Clear
        Resume
       
        'Macro1_Exit

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

    Saturday, February 02, 2013 6:50 PM

Answers

  • I can's see any counter where you try 4 times?

    You could do something like this:

    Function ..(..)
    ..
    Dim lngTry as Long
    lngTry = 0
    ..
    On Error GoTo PROC_ERR
    DoCmd.OpenQuery ...
    PROC_EXIT:
      Exit Function
    PROC_ERR:
      lngTry = lngTry + 1
      If lngTry < 4 Then
        Resume
      Else
        If MsgBox(Err.Description & vbCrLf & "Try again?", vbQuestion + vbYesNo) = vbYes Then
          Resume
        Else
          Resume PROC_EXIT
        End If
      End If
    End Function

    You also could react depending on Err.Number and only Resume if it's the Error that is thrown when it's an VPN connection (Network) error.

    HTH

    Henry


    Tuesday, February 05, 2013 2:19 AM

All replies

  • This is a quick note to let you know that we are doing research on this issue. We will let you know if there is any progress.

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 04, 2013 7:42 AM
  • Hi Baryb123

    You can't do a resume when you clear the error. On the other hand will Resume clear the error itself, so clearing it is obolete.

    What you want to do? You want to exit when en error occured, correct?

    In this case try following:

    ..
    Macro1_Err:
      MsgBox Err.Description
      Resume Macro1_Exit
    ...

    HTH

    Henry

    Monday, February 04, 2013 8:59 AM
  • When I have an error I want to retry the line in which the error occured for appox 4 times.  If it corrects itself then continue with rest of the procedures, if not then fail with an error code.  At the present I have to Stop all and either start over or manualy run the lines from the error on.  the error is almost alway a general network error connection error(caused by a blip in the vpn).

    The code you have is what I am using at this time. The error almost always clears and  can manualy run right away.  the code presented is only a test for a much longer but simular type.

    Your help is much appreciated.

    Monday, February 04, 2013 1:49 PM
  • I can's see any counter where you try 4 times?

    You could do something like this:

    Function ..(..)
    ..
    Dim lngTry as Long
    lngTry = 0
    ..
    On Error GoTo PROC_ERR
    DoCmd.OpenQuery ...
    PROC_EXIT:
      Exit Function
    PROC_ERR:
      lngTry = lngTry + 1
      If lngTry < 4 Then
        Resume
      Else
        If MsgBox(Err.Description & vbCrLf & "Try again?", vbQuestion + vbYesNo) = vbYes Then
          Resume
        Else
          Resume PROC_EXIT
        End If
      End If
    End Function

    You also could react depending on Err.Number and only Resume if it's the Error that is thrown when it's an VPN connection (Network) error.

    HTH

    Henry


    Tuesday, February 05, 2013 2:19 AM
  • Thank you for your response.   will try this.  do we need not to clear the error befor resume?  and some of my testing shows the possibility that Access will not reset the odbc error with out restarting?  I am not sure.

    Tuesday, February 05, 2013 1:40 PM
  • Thank you again,  it is working sometimes.  (in my original loop i failed to put the second else to escape the loop)

    my test procedure is as follows.

    Start process with VPN disabled

    allow loop to run a few times then enable VPN connection.

    it starts the DoCmd and runs perfectly. (sometime)

    the error I get when it won't continue is as follows

    [ODBC SQL Driver] Communication line Failure (#0)

    When it fails it is taking not time between DoCmd attempts.  When it works there is a delay between attempts.

    at this point befor closing Access I Test the odbc link with the ocbcad and it tests good.  I am also get the same error when I try to open the table (which the query is based on) in datasheet.  When I close Access and reopen Access it is fine.

    I have tested during the loop and at the msg box.  sometimes works some times not.

    I added a strconnect to the err procedure but with no luck.  I have also created a SQL passthrough query and run this but the SQL link will still not connect.  so far the only thing that does work is to close access and reopen the DB and then it works.   What I am trying to accomplish is to keep runing a line untill the connection recovers and then proced on as usual.  These are scheduled tasks and no one is around to kill Access and restart.

    Function Macro1()
    Dim lngTry As Long
    lngTry = 0

    On Error GoTo PROC_ERR
    'DoCmd.SetWarnings False
    DoCmd.OpenQuery "Query1", acViewNormal, acEdit
    PROC_EXIT:
      Exit Function
    PROC_ERR:
      lngTry = lngTry + 1
      If lngTry < 4 Then

     

       'strConnect = "ODBC;Driver={SQL Server};Server=192.168.5.2;Database=POSBdat;UID=sa;PWD=cbm"
       strConnect = "ODBC;DSN=posbdatla;Description=posbdatla;UID=sa;PWD=cbm;DATABASE=POSBdat;TABLE=Vendors"
    MsgBox ("cnn")


        Resume
      Else
        If MsgBox(Err.Description & vbCrLf & "Try again?", vbQuestion + vbYesNo) = vbYes Then
          Resume
        Else
          Resume PROC_EXIT
        End If
      End If
    End Function

     Am i woriking with a dog that wont hunt. your assistance is very much appreciated


    • Edited by Barryb123 Tuesday, February 05, 2013 8:54 PM
    Tuesday, February 05, 2013 2:21 PM
  • The Err.Clear statement is described in the VBA help of Access. There you can find following explanation ((c) Microsoft):

    The Clear method is called automatically whenever any of the following statements is executed:
    • Any type of Resume statement
    • Exit Sub, Exit Function, Exit Property
    • Any On Error statement

    No need to call it manually if you use Resume.

    Henry

    Wednesday, February 06, 2013 1:57 AM
  • Try the first statement again and let the {} around "SQL Server" away.

    Henry

    Wednesday, February 06, 2013 1:58 AM
  • Henry,

    I have removed the {} and still no luck.  I have tried a refreshlinks (see attached) I have tried to refresh the links with the link manager but no luck. (even though it says the link as refreshed.  I have tried taking the Connectiontime out for ODBC from 600 sec to 0.

    through furter testing(and patience) I have found if I let it set for 10 minutes (timed, retimed, etc) and then try the function it works.

    Test procedure;  run with good connetion good, kill connection run error, restore connection run still error. continue to try every min. at 10 min. it works.

    I had it working.  Attached is the code now.   The connect seem to be the answer. I am sure there can be some improvement or a better way to accomplish this.  if so please let me know.  not sure if I need bothe the connect and refresh.  and there is pobably a better way.  I do not know why it stopped back to the 10 min

    Function Macro1()
    Dim lngTry As Long
    lngTry = 0

    On Error GoTo PROC_ERR
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Query1", acViewNormal, acEdit

    PROC_EXIT:
     DoCmd.SetWarnings True
       Exit Function
    PROC_ERR:
      lngTry = lngTry + 1
      If lngTry < 4 Then
           Connect = "ODBC;DSN=posbdatla;Description=posbdatla;UID=sa;PWD=CBM;APP=Microsoft Office 2010;Database=POSBdat;Network=DBMSSOCN;Table=dbo.Vendors"
           RefreshLinks = "ODBC;DSN=posbdatla;Description=posbdatla;UID=sa;PWD=CBM;APP=Microsoft Office 2010;Database=POSBdat;Network=DBMSSOCN;Table=dbo.Vendors"

    'MsgBox ("cnn")

        Resume
      Else
        If MsgBox(Err.Description & vbCrLf & "Try again?", vbQuestion + vbYesNo) = vbYes Then
          Resume
        Else
          Resume PROC_EXIT
        End If
      End If
    End Function

    Thankyou for your assistance


    • Edited by Barryb123 Thursday, February 07, 2013 12:19 AM
    Wednesday, February 06, 2013 7:34 PM
  • Hi Barryb123

    If you want to retry in 10 minutes then you can use an api call to let the code sleep for 10 minutes.

    Declare the API in the top of your module (general section)

    Declare Sub Sleep Lib "kernel32" (ByVal lngMilliseconds As Long)

    Now you can let your application (in the error handler) sleep for 10 minutes by calling following statement:

    sleep 600000 'milliseconds= 10 * 60 * 1000 

    and after retry by calling Resume

    HTH

    Henry

    Thursday, February 07, 2013 3:38 AM