Error Handling Code in Ms Access (1 Viewer)

[nector]

I have developed a code for the purpose of get information from the server, well if I put the correct URL it works just fine , but if I put a wrong URL it give an error message and also debug.

I do not want to see the debug on the screen but a proper suppressing error handler with a message not allowing users to go to the IDE, this is creating a risk. Any idea?

Dim Company As New Dictionary
Dim n As Integer
Dim Request As Object
Dim strData As String
Dim stUrl As String
Dim Response As String
Dim requestBody As String
Set Company = New Dictionary
stUrl = "http://locahost:8080/nectorprime/code/selectCodes"
Set Request = CreateObject("MSXML2.XMLHTTP")
Company.Add "tpin", "1112623668"
Company.Add "bhfId", "100"
Company.Add "lastReqDt", Format((Me.txtItemClsResDatesr), "YYYYMMDD000000")
strData = JsonConverter.ConvertToJson(Company, Whitespace:=3)
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .send requestBody
        Response = .responsetext
    End With
If Request.Status = 200 Then
MsgBox Request.responsetext, vbCritical, "Internal Audit Manager"
n = FreeFile()
Open "C:\Users\necto\Desktop\Testing\test.txt" For Output As #n
Print #n, strData
Close #n
Exit Sub
End If

 

[Josef P.]

Do you know the error handling in Northwind2?

 

[nector]

Okay thanks Josef , let me look at it very closely

 

[GPGeorge]

Okay thanks Josef , let me look at it very closely

You can download and install the Northwind templates which include the error handling code from here, or install them from Access.

 

[The_Doc_Man]

@nector - I'm going to give you some reading references to help you understand this tricky but extremely important topic.

Error handling starts with a bit of code that must be inside a subroutine or function, as opposed to anything in a module declaration area. The setup step starts with a single line "ON ERROR" followed by one of three options, to be executed very early in your code after you are finished with your sub/function routine's DIM, PUBLIC, PRIVATE, or other data allocation lines. Ideally it is done as the first executable instruction in your routine's code. The common choices are ON ERROR GOTO <target label> (to identify the starting point of the code that would handle an error), ON ERROR GOTO 0 (to turn off error handling within that sub/function), and ON ERROR RESUME NEXT (to indiscrimately suppress non-fatal errors). You can look up the "VBA ON ERROR" articles to see more details. Understand that the "RESUME NEXT" option WILL NOT HANDLE system-fatal errors.

Error handlers are local to a given routine but sometimes it SEEMS that they are global. They are NEVER truly global - but sometimes the error routine that intercepts your error condition isn't in the routine where the error occurred. The topic to examine online is "WINDOWS ERROR HANDLER" or "... HANDLING" because error handlers are a special context. That is why when you are finished with your error handler code, you exit it with a RESUME statement rather than a GOTO. You theoretically CAN terminate a handler with an EXIT SUB but (as a matter of personal preference) I would not do that - particularly if you wanted the sub/function in question to continue operation.

The effect of "nesting" is described in depth in the "HANDLING" articles. Since VBA allows you to call one subroutine from another, and both subroutines CAN have error handlers, you might wonder which handler will handle your errors. The error handler declared by the currently executing sub/function handles errors in the only piece of code that is executing at the moment - your active routine. When that sub/function terminates, its associated handler ceases to be in force. If the routine to which you returned had earlier declared a handler, that one is now in force. However, if that "called" function DIDN'T have an error handler but its caller DID, it is the caller's error handler that takes over - hence my comments about which error handler catches the error.

IMPORTANT: Error handlers represent a special context. That <target label> starts an error handler routine that CANNOT be in the mainline flow of your subroutine and you SHOULD as a technical matter use a RESUME statement to dismiss the error handler's special context. Note also that if you are in an error handler and trigger another error, the active error handler CANNOT accept its own error and in such cases, your sub/function WILL lose control completely. Look at the error handling topic I mentioned earlier.

Normally, in an error handler you will want to look at the ERR object, which is an inherent part of the VBA execution environment. Look up the properties of "VBA ERR OBJECT" to see what you can see after an error occurs. You will most often use ERR.NUMBER and ERR.DESCRIPTION properties though there are others. If you are expecting a specific error (and I think in your case you ARE expecting that error), you can compare the ERR.NUMBER value to the error number you expected and do something special, but you have to remember that error handlers catch ALL errors, not just the one you were expecting. The toughest part of error handling is what to do after an the error you weren't expecting as a possibility.

One last reference for you, and this might be more than you care about, but I'll list it for information purposes. The hexadecimal error code you got (0x800C0005), or the negative long integer version of it, is called an "HRESULT" and actually has a format that can be broken down into parts. The significant parts are the leading "8", the "00C", and the "0005" - which mean "8"=not fatal, "00C" is the "facility" number, and "0005" is the facility-specific error code. If that code had been 0xC00C0005 then you could not have intercepted it because the process would have terminated on the fatal error.

The error display you got with the code, text, and "END" and "DEBUG" buttons was sent to you by the Access "last chance" error handler. It can be understood if you look at the "handling" topic and recognize that it is merely the "unhandled error" handler for Access itself, used when the user's error handling was insufficient or absent. However, what happens AFTER you click either the END or DEBUG buttons depends on whether the error was fatal or not.

 

[gemma-the-husky]

You aren't getting runtime errors as such. Your call to the resource never fails to return an answer.

However the code it returns indicates whether it worked or not. You are testing for a successful code of 200, but not allowing/testing for an error condition of >=400.

With an response of <>200 the response text will contain a brief description of the problem.

 

[The_Doc_Man]

On further research (because of Dave's comment about condition codes 200 vs. >=400), I realized what the code REALLY means.

The error 0x800C0005 means something in this web-related operation isn't registered correctly. Do a web search on "Windows error 0x800C0005" and you will get several hits. Here's one of them:

How to Fix Error Code 0xc00000e5 on Windows? Here’s the Guide - MiniTool Partition Wizard

Some users may encounter the error code 0xc00000e5 on Windows. If you are also bothered by this problem, this post is what you need.

www.partitionwizard.com

All of the error articles I found suggest an improper installation of some component in the web software or a security error in addressing one such component. The "resource" in this case isn't a web site. It's a program component.

 

[gemma-the-husky]

I don't think it's necessarily an installation issue, as @The_Doc_Man suggests.

What happens is that the call using the strurl returns 200 for success, and another number for failure. The json response includes the error code and a text description of the error, which is easy to read, but probably needs some code to display it in a structured sensible way. So the result of the attempt to read data returns code 400, say. The reason might be that strurl is constructed incorrectly or that the API function couldn't process the call correctly. It's not really an access database issue, it's a nectorprime issue, whatever nectorprime actually does or is. Eg, is it a restive API source?

I found it quite hard implementing calls like this in VBA, because there's so little VBA code out there. There's examples in C, but it's tricky to get the correct VBA syntax from the C examples. It can take a lot of trial and error, and it can be a relief when you get it working consistently.

Because of this the error text you get may be misleading. You just know it failed but it may have failed because the request was constructed incorrectly and failed, or the request was syntactically correct, but not logically correct and still failed, and the "blunt" error returned may not clarify the exact problem.

Sometimes we found there was a caching issue. The first read returned a result. Subsequent reads don't re-read the data, they just return the cached result. You have to change the call slightly to force it to requery correctly each time, so you may have fixed the problem without actually getting a different answer.

I forget the code offhand, but instead of MSXML.XMLHTTP, there's an alternative, but similar version to force the code to return the data from the remote server, and you may need that variant.

One final point to bear in mind is that you are probably dealing with asynchronous processing, so you have to time your process to wait for the response.

I imagine that after your command .sendrequestbody, you should wait until response.status is <>0.

 

[The_Doc_Man]

Dave, depends on the nature of the utility program, but in general, network stuff doesn't return the 100/200/300/400/500 codes. You get a code of 0 (no error occurred) or -1 (yes, error occurred) and in the latter case you have to request a "status of last request" - which will be one of those codes you mentioned. The web codes do not linearly map to the Windows or Access environment codes.

 

[Minty]

Agree with what @gemma-the-husky said regarding caching the answers. We have had that issues on a small app we developed.
I have an example somewhere of the correct syntax.

 

[gemma-the-husky]

Dave, depends on the nature of the utility program, but in general, network stuff doesn't return the 100/200/300/400/500 codes. You get a code of 0 (no error occurred) or -1 (yes, error occurred) and in the latter case you have to request a "status of last request" - which will be one of those codes you mentioned. The web codes do not linearly map to the Windows or Access environment codes.

I thought the syntax used seemed exactly the same as the syntax for querying a restive API (if that's the right way to put it), and the responses in JSON resembled the responses from a restive API, where a return of 200 represents success. I am not familiar with what the OP was doing but I thought it might be the same. Hence also the other observations.