Add err.number 503 to error routine.

Hi Experts,

We have a function that opens a CSV file, loops thru all records and uploads each record to a web site thru an API call.
Now we realized some time that site is offline and would like to add to the existing error routine below logic that if error number is 503 then it should wait a minute and try again call that API (the command that caused to throw the error).

Err_Proc:
    Select Case Err.Number
        Case 9
            '' display a message if you want
           Resume Next  '''' assuming you want to just pick up at the statement after the error
        Case Else
            s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Err.Number & "--" & Err.Description & "','" & sTable & "'," & Nz(pk, "") & ",'" & sFile & "','') "
            CurrentDb.Execute s
'            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
'            Resume ExitImportDataToCaspio
            Resume Next
    End Select

Open in new window


PS. for a detailed description of the function in question see here
.
Thanks
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Draw a flow diagram of your desired logic. You will pretty quickly see, that this should not be done via the error handler. You need some abstraction. You don't call the method for the APi call directly. E.g.

Option Compare Database
Option Explicit

Private Function ApiCallImpl() As Boolean

  On Local Error GoTo LocalError
  
  ' The actual API call..
  ApiCallImpl = True ' Call was successful.
  Exit Function
  
LocalError:
  ApiCallImpl = (Err.Number <> 503)
  If ApiCallImpl Then
    Resume Next
  End If

End Function

Private Function ApiCallIntf() As Boolean

  ApiCallIntf = ApiCallImpl
  If Not ApiCallImpl Then
    Sleep 60000
    ApiCallIntf = ApiCallImpl
  End If

End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You could use the Sleep API call. An implementation is shown in my article:

Modern/Metro style message box and input box for Microsoft Access 2013+

Browse to the paragraph Simulating dialogue mode.
Dale FyeOwner, Dev-Soln LLCCommented:
Are you calling this function manually, or does it run automatically, every so often?

If it is run automatically, and you can wait for the next iteration, then just elegantly exit from your function and wait for the next upload process.

If not, I would agree with Gustav about adding a pause into your code, the Sleep API would work or you could simply create your own Pause procedure and use the Timer or Now functions to initiate a loop for some period of time. followed by a Resume, to attempt to run the same line of code again.
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Download Free Ebook
bfuchsAuthor Commented:
Hi Experts,

You could use the Sleep API call...
...followed by a Resume, to attempt to run the same line of code again
I have been using already API sleep function in my code, and therefore my main question was how to ensure it tries to execute again (after sleeping) the same line of code which caused the error, taking in consideration that it can come from different locations in my function.

@ste5an,
Your code seems like addressing this issue, however will have to implement in my code & test it.

Thanks,
Ben
Gustav BrockCIOCommented:
I have been using already API sleep function in my code, and therefore my main question was how to ensure it tries to execute again (after sleeping) the same line of code which caused the error, taking in consideration that it can come from different locations in my function.

How could we know? It was not was you asked originally.

Resume [Next] will let the code continue where it left.
Dale FyeOwner, Dev-Soln LLCCommented:
Your code uses:
Resume NEXT

Open in new window

IN order to rerun the line that raised the error, you need to simply use:
Resume

Open in new window

bfuchsAuthor Commented:
Thank you my experts!
bfuchsAuthor Commented:
Hi experts,

A quick question.

The code posted above
 s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Err.Number & "--" & Err.Description & "','" & sTable & "'," & Nz(pk, "") & ",'" & sFile & "','') "
            CurrentDb.Execute s

Open in new window


inserted the following in the ErrorMessage column
<!DOCTYPE HTML PUBLIC ~-//W3C//DTD HTML 4.01//EN~~http://www.w3.org/TR/html4/strict.dtd~>
<HTML><HEAD><TITLE>Service Unavailable</TITLE>
<META HTTP-EQUIV=~Content-Type~ Content=~text/html; charset=us-ascii~></HEAD>
<BODY><h2>Service Unavailable</h2>
<hr><p>HTTP Error 503. The service is unavailable.</p>
</BODY></HTML>

Open in new window


Just realized its not inserting the Err.Number (as its not the first thing, followed by two dashes..).

Any idea why, and how can I figure out the number?

Thanks,
Ben
ste5anSenior DeveloperCommented:
You need to query the HTTP result, not the Err object. Err is only populated by an VB(A) runtime error. Thus you need to look at the value in Status after the request.
Reason is simple: An HTTP 503 is not an error. It is simply a possible state of that resource.

btw, after looking at your code again:
Do NOT use New in normal variable declarations.

Dim variable As New ClassType

Open in new window

Cause it behaves differently than in OO languages like C# or VB or VB.NET. It will auto-create an instance on each variable access when the reference is Nothing:

Option Compare Database
Option Explicit

Public Sub TestDimAsNew()

  Dim variable As New clsTestClass
  
  Set variable = New clsTestClass
  variable.Test = "txtMyTextBox"
  Debug.Print "1"; variable.Test
  
  Set variable = Nothing
  Debug.Print "2"; variable.Test

End Sub

Open in new window

with

Option Compare Database
Option Explicit

Private m_Test As String

Public Property Get Test() As String
  
  Test = m_Test
  
End Property

Public Property Let Test(AValue As String)

  m_Test = AValue
  
End Property

Open in new window

This means that you cannot longer test for uninitalized instances. And when using COM+ objects, this means that when these error out and get Nothing, you wouldn't catch that. Thus it should be really avoided in most cases.
bfuchsAuthor Commented:
@ste5an,

Okay, So if I modify the code to the following will it work?
                    objHTTP.Send JsonConverter.ConvertToJson(patient)
                    If objHTTP.ResponseText <> "" Then
                        If InStr(1, objHTTP.ResponseText, " in field 'PatientID' is invalid because it does not exist in the related parent table 'Patients' (field 'PatientID')") > 0 Then
                            Call AddPatient(columns(iPatIDCol), access_token)
                            objHTTP.Send JsonConverter.ConvertToJson(patient)
                        Else
                            s = "Insert into API_Errors(ErrorMessage,TableName, [TableID],FileName, ValuesSent) Values ('" & Replace(Replace(objHTTP.ResponseText, """", "~"), "'", "") & "','" & sTable & "'," & pk & ",'" & sFile & "','" & JsonConverter.ConvertToJson(patient) & "') "
                            CurrentDb.Execute s
                            If InStr(1, objHTTP.ResponseText, "HTTP Error 503") > 0 Then
                                Sleep 1000
                                Resume
                            End If

                        End If
                        'Debug.Print objHTTP.ResponseText
                    Else

Open in new window


PS. Just guessing it will not work, as Resume is only applicable to cases of error.


Thanks,
Ben
ste5anSenior DeveloperCommented:
To be honest, even when it would work, it is a terrible piece of code.. didn't I wrote you should refactor it? This is not an option. Cause your current code does too much things in one procedure. While this can work, it is on the other hand simply wrong in terms of software architecture and implementation.

The first step is to extract the HTTP handling and the logging into their own methods.  Then you need to thoroughly think about the flow. Cause imho it should like this base on the previous approach:

Option Compare Database
Option Explicit

Private Enum EnumApiCallStatus
  acsNoError,
  acsRetryNecessary,
  acsRetryFailed,
  acsRetrySucceded
  ' And maybe some more.
End Enum

Private Function ApiCallImpl(AParameters As Variant, ByRef OStatus As EnumApiCallStatus) As Boolean

  On Local Error GoTo LocalError

  ' The actual API call..
  ' Here you log the verbose/debug informations of your API calls.
  ApiCallImpl = True ' Call was successful.
  OStatus = acsNoError
  Exit Function

LocalError:
  ApiCallImpl = (Err.Number <> 503)
  If ApiCallImpl Then
    ' Here are only real exceptions/errors logged.
    ' These will not be propagated.
    Resume Next
    ' Or you set a different status here.
  Else
    OStatus = acsRetryNecessary
  End If

End Function

Private Function ApiCallIntf(Parameters As Variant, ByRef OStatus As EnumApiCallStatus) As Boolean
' No logging needed here.

  ApiCallIntf = ApiCallImpl(Parameters, OStatus)
  If OStatus = acsRetryNecessary Then
    ' Sleep or an other way of scheduling it.
    Sleep 60000
    ApiCallIntf = ApiCallImpl(Parameters, OStatus)
    If OStatus = acsNoError Then
      OStatus = acsRetrySucceded
    Else
      OStatus = acsRetryFailed
    End If
  End If

End Function

Open in new window

This is just a quick outline. Then your actual business logic looks readable:

Private Function AddPatientIntf(patient)

  Dim Status As EnumApiCallStatus

  If ApiCallIntf(patient, Status) Then 'JSON handling is down in the API call method.
     AddPatient(columns(iPatIDCol), access_token)  ' why an access_token as parameter??
  End If

End Function

Open in new window

bfuchsAuthor Commented:
Hi ste5an,

I'm not in today, will test it tom.
Really appreciate your help.

Thanks,
Ben
bfuchsAuthor Commented:
@ste5an,

To be honest
Okay so let be honest with you as well...
This code was originally designed by a more experienced developer (I'm not in that level yet...), and therefore may take some time till I get to implement your suggestions...

Will try do it step by step, and perhaps open a new thread to get it finalize, if necessary...

just a quick question.

ApiCallImpl = (Err.Number <> 503)
How does that coincide with the following.
You need to query the HTTP result, not the Err object. Err is only populated by an VB(A) runtime error

Thanks,
Ben
ste5anSenior DeveloperCommented:
It doesn't. It is an artifact from your posts, cause I thought the you get error codes to handle in the first answer. Thus the inner method should look like

Option Compare Database
Option Explicit

Private Enum EnumApiCallStatus
  acsNoError,
  acsUnkownException,
  acsFailure,
  acsRetryNecessary,
  acsRetryFailed,
  acsRetrySucceded
  ' And maybe some more.
End Enum

Private Function ApiCallImpl(AParameters As Variant, ByRef OStatus As EnumApiCallStatus) As Boolean

  On Local Error GoTo LocalError

  Dim HttpClient As Object ' Your concrete type or late bindng.

  Dim Content as String

  ApiCallImpl = False

  ' The actual API call..
  ' Here you log the verbose/debug informations of your API calls.
  '..
  HttpClient.Send Content
  Select Case HttpClient.Status
    Case Is = 200
      ApiCallImpl = True
      OStatus = acsNoError

    Case Is = 503
      ApiCallImpl = False
      OStatus = acsRetryNecessary

    Case Else
      ApiCallImpl = False
      OStatus = acsFailure
  End Select
  Exit Function

LocalError:
  ApiCallImpl = False
  OStatus = acsUnkownException

End Function

Open in new window

bfuchsAuthor Commented:
Thank you ste5an!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.