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)

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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

Answer 8

Try the first statement again and let the {} around "SQL Server" away.

Henry

Answer 9

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

Answer 10

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