bfuchs

asked on 2/10/2019

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

VB ScriptMicrosoft AccessVBA

Last Comment

bfuchs

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

ste5an

2/11/2019

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

Gustav Brock

2/11/2019

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 Fye

2/11/2019

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.

bfuchs

2/11/2019

ASKER

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

Your help has saved me hundreds of hours of internet surfing.

fblack61

SOLUTION

Gustav Brock

2/12/2019

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

SOLUTION

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

bfuchs

2/12/2019

ASKER

Thank you my experts!

bfuchs

2/12/2019

ASKER

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

ste5an

2/12/2019

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

bfuchs

2/12/2019

ASKER

@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

ste5an

2/13/2019

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

bfuchs

2/13/2019

ASKER

Hi ste5an,

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

Thanks,
Ben

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

bfuchs

2/14/2019

ASKER

@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

SOLUTION

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

bfuchs

2/17/2019

ASKER

Thank you ste5an!