Solved VBA on timer in MS Access (1 Viewer)

[nector]

Here I'm trying to sleep the function below for 10 seconds so that if the internet is not connected it should move to the next code and provide a friendly message to the user so that the user knows exactly what is happening.

What is now happening
(1) If there no internet the code works ok and give the messages within the specified seconds
(2) If there is internet then the code will keep waiting for over 45 minutes which is causing long lines in our retails shops. This is where we need help to fix this.

Dim Request As Object
Dim stUrl As String
Dim Response As String
Dim requestBody As String
stUrl = "http://localhost:8080/xxxxxxxxxxxxxxxxxxxx"
Set Request = CreateObject("MSXML2.XMLHTTP")
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .Send requestBody
        Response = .ResponseText
    End With
Sleep (10000)
If Request.Status <> 200 Then
Resume Next
ElseIf Request.Status = 200 Then
MsgBox Request.ResponseText, vbInformation, "Internal Audit Manager"
Resume Next
End If


Option Compare Database

Option Explicit
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

 

[sonic8]

Here I'm trying to sleep the function below for 10 seconds so that if the internet is not connected it should move to the next code and provide a friendly message to the user so that the user knows exactly what is happening.

Why do you want to wait before telling the user that the internet is not available?
It looks to me as if you are not waiting for a response from the server or retrying to connect in those 10 seconds (actually, your code shown here waits for just one second). You're just waiting for ... what exactly?

From the subject of this thread and your description of the problem, I guess you are using that code in a loop. - But you are not showing your code for the loop. That code is the only possible reason that the shown code lets users wait for 45 minutes.

 

[nector]

Why do you want to wait before telling the user that the internet is not available?
It looks to me as if you are not waiting for a response from the server or retrying to connect in those 10 seconds (actually, your code shown here waits for just one second). You're just waiting for ... what exactly?

From the subject of this thread and your description of the problem, I guess you are using that code in a loop. - But you are not showing your code for the loop. That code is the only possible reason that the shown code lets users wait for 45 minutes.

I'm not too sure whether you understood the code properly,

Sleep (10000)

The reason why the sleep code is there is too allow the top code to do its job if takes more than10 seconds then its timed out , the code move to the next stage, this is where I thought maybe I mixed it up or missed somthing

 

[sonic8]

I'm not too sure whether you understood the code properly,

You might be right. I completely missed those "Resume Next" statements in the code which make absolutely no sense, neither to me nor to the compiler, unless this code is inside an error handler.
If it actually is in an error handler, then you made it impossible for anybody to understand the code properly because the "Resume Next" statement will make execution jump to a code location you did not include.

The reason why the sleep code is there is too allow the top code to do its job if takes more than10 seconds then its timed out , the code move to the next stage, this is where I thought maybe I mixed it up or missed somthing

Now I'm not too sure whether *you* understand the code properly.
What makes you think the value of Request.Status could change after the request completed?
This approach in the code would only make sense if you would execute the HTTP request asynchronously, which you currently don't do.

 

[Jason Lee Hayes]

Here I'm trying to sleep the function below for 10 seconds so that if the internet is not connected it should move to the next code and provide a friendly message to the user so that the user knows exactly what is happening.

What is now happening
(1) If there no internet the code works ok and give the messages within the specified seconds
(2) If there is internet then the code will keep waiting for over 45 minutes which is causing long lines in our retails shops. This is where we need help to fix this.

Dim Request As Object
Dim stUrl As String
Dim Response As String
Dim requestBody As String
stUrl = "http://localhost:8080/xxxxxxxxxxxxxxxxxxxx"
Set Request = CreateObject("MSXML2.XMLHTTP")
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .Send requestBody
        Response = .ResponseText
    End With
Sleep (10000)
If Request.Status <> 200 Then
Resume Next
ElseIf Request.Status = 200 Then
MsgBox Request.ResponseText, vbInformation, "Internal Audit Manager"
Resume Next
End If


Option Compare Database

Option Explicit
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Undeclared srtData
Resume Next used incorrectly
Sleep placement
Missing Error handling

Try this pal..

Option Compare Database
Option Explicit

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub SendJsonRequest()
    Dim Request As Object
    Dim stUrl As String
    Dim Response As String
    Dim strData As String

    On Error GoTo ErrHandler

    stUrl = "http://localhost:8080/xxxxxxxxxxxxxxxxxxxx"
    strData = "{""key"":""value""}" ' Replace with actual JSON
    Set Request = CreateObject("MSXML2.XMLHTTP")

    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .Send strData
        Response = .ResponseText
    End With

    Sleep 10000 ' Optional

    If Request.Status = 200 Then
        MsgBox Response, vbInformation, "Internal Audit Manager"
    Else
        MsgBox "Request failed with status: " & Request.Status, vbExclamation
    End If

    Exit Sub

ErrHandler:
    MsgBox "Error: " & Err.Description, vbCritical
End Sub

 

[The_Doc_Man]

The others have suggested solutions for you. My remarks are in no way intended to comment on their solutions. I will discuss one of the specific errors you made. The nature of your error makes it clear that you do not understand error handling completely. It is beyond the intended scope of this discussion to be a complete tutorial on error handling.

Normally, event routines in VBA are executed linearly in an Access internal queue. If an event occurs and no other event was being processed, then the new event routine's code runs immediately. IF, however, you were already running event code, the new event waits for the active event to end or exit, at which time Access runs the code for the new event. It is possible for several events to be backlogged in this queue, which is not visible to us. We only see the effects. This potential backlog occurs because VBA event routines cannot interrupt other VBA event routines. Events can occur as needed - but event code might have to wait its turn to run. In most cases, VBA code is synchronously executed.

The one exception is ... exceptions - one name for the VBA interrupt mechanism. You might also see the word "fault" used for this mechanism, which is a holdover from the MS-DOS days, when exceptions, faults, traps, and interrupts were four (slightly) different hardware mechanisms. Now, at the app level, Windows and VBA merge them all into "error context."

You can define an On Error GoTo label near the top of each event routine to set up to trap errors. If an error occurs, you enter "exception" context, starting at the named label. This label becomes the starting point of your error "handler" or "error routine." A handler routine is part of the event code in which that On Error statement was defined. Therefore, you can have the error handler look at local or global variables or objects to make decisions about how to proceed. This error context is how you handle unexpected errors. (Which leads to the philosophical question of, if you bothered to write an error handler, was the error actually unexpected?)

Eventually, you must dismiss the error context because that error context interrupted the associated event's code. That code is still "pending" i.e. still in memory and waiting for execution to resume in "normal" event context.

This exception context can be ended in either of two ways. First and simplest, the error handler code can execute an Exit Sub or run into the End Sub and that cleans up both the faulting routine and its exception context. Since the error context is part of an event routine, cleanup of the event context implicitly cleans up any pending error context for that event routine.

The OTHER way is to use a Resume statement, which can be either Resume Next (which acts like an Exit Function, returning to the instruction AFTER the one that tripped the error) or Resume label (acting like a GoTo label and subject to the same label visibility rules as the event routine.) In that way, Resume acts like a GoTo that specifically terminates exception context without leaving the subroutine / function that encountered the error. The issue here is that it is meaningless in non-exception context. If you aren't in error context, there is no Next to which you can Resume. In fact, I recall that some years ago we had a report of a Resume Next that crashed an app because of that exact problem.

The important takeway from the previous paragraphs is that you must dismiss the exception context and allow normal context to resume operation. Your error, therefore, involved using syntax related to error handling outside the proper context of an error handler.

 

[Jason Lee Hayes]

The others have suggested solutions for you. My remarks are in no way intended to comment on their solutions. I will discuss one of the specific errors you made. The nature of your error makes it clear that you do not understand error handling completely. It is beyond the intended scope of this discussion to be a complete tutorial on error handling.

Normally, event routines in VBA are executed linearly in an Access internal queue. If an event occurs and no other event was being processed, then the new event routine's code runs immediately. IF, however, you were already running event code, the new event waits for the active event to end or exit, at which time Access runs the code for the new event. It is possible for several events to be backlogged in this queue, which is not visible to us. We only see the effects. This potential backlog occurs because VBA event routines cannot interrupt other VBA event routines. Events can occur as needed - but event code might have to wait its turn to run. In most cases, VBA code is synchronously executed.

The one exception is ... exceptions - one name for the VBA interrupt mechanism. You might also see the word "fault" used for this mechanism, which is a holdover from the MS-DOS days, when exceptions, faults, traps, and interrupts were four (slightly) different hardware mechanisms. Now, at the app level, Windows and VBA merge them all into "error context."

You can define an On Error GoTo label near the top of each event routine to set up to trap errors. If an error occurs, you enter "exception" context, starting at the named label. This label becomes the starting point of your error "handler" or "error routine." A handler routine is part of the event code in which that On Error statement was defined. Therefore, you can have the error handler look at local or global variables or objects to make decisions about how to proceed. This error context is how you handle unexpected errors. (Which leads to the philosophical question of, if you bothered to write an error handler, was the error actually unexpected?)

Eventually, you must dismiss the error context because that error context interrupted the associated event's code. That code is still "pending" i.e. still in memory and waiting for execution to resume in "normal" event context.

This exception context can be ended in either of two ways. First and simplest, the error handler code can execute an Exit Sub or run into the End Sub and that cleans up both the faulting routine and its exception context. Since the error context is part of an event routine, cleanup of the event context implicitly cleans up any pending error context for that event routine.

The OTHER way is to use a Resume statement, which can be either Resume Next (which acts like an Exit Function, returning to the instruction AFTER the one that tripped the error) or Resume label (acting like a GoTo label and subject to the same label visibility rules as the event routine.) In that way, Resume acts like a GoTo that specifically terminates exception context without leaving the subroutine / function that encountered the error. The issue here is that it is meaningless in non-exception context. If you aren't in error context, there is no Next to which you can Resume. In fact, I recall that some years ago we had a report of a Resume Next that crashed an app because of that exact problem.

The important takeway from the previous paragraphs is that you must dismiss the exception context and allow normal context to resume operation. Your error, therefore, involved using syntax related to error handling outside the proper context of an error handler.

Excellent explanation—while a quick code fix might work, nothing beats a thorough breakdown for true understanding.

 

[Albert D. Kallal]

Well, the MSXML object does not support "sunk" events in VBA, but you CAN STILL call the MSXML library code as "asynchronous"

I suggest this code pattern for a 10 second timeout, and if it takes longer then 10 seconds, then you bail out - and do so without a freezing UI.

Sub Test()

    Dim Request         As Object
    Dim stUrl           As String
    Dim Response        As String
    Dim requestBody     As String
 
    stUrl = "xxxxxxxxxxxxxxxxxxxx"
 
    Set Request = CreateObject("MSXML2.XMLHTTP")
    requestBody = strData
        With Request
            .Open "POST", stUrl, True
            .setRequestHeader "Content-type", "application/json"
            .Send requestBody
        End With
     
    Dim MaxTry          As Integer
    Dim TryCount        As Integer
 
    TryCount = 0
    MaxTry = 10 ' try once each second
 
 
    ' wait up to 10 seconds for a response, or bail out...
    Do While Request.ReadyState <> 4
 
        TryCount = TryCount + 1
     
        If TryCount > 10 Then
            ' too many tries - bail out code here
            MsgBox "request fail"
            ' response = "fail"
            Exit Sub ' do whatever
        End If
     
        DoEvents
        Sleep 1000
    Loop
 
    ' if we get here, then request is done, and ok!!!
 
    Response = Request.ResponseText


End Sub

Note close the use of the True flag - that means "async" and don't wait......
.Open "POST", stUrl, True <----- use True here!!!

So, with above, the code will fall through - and you MUST then use a loop to determine if the data is ready for the taking....

This should allow a smooth request, and one that you can control/test the timeout in VBA. It will also not lock up Access....

R
Albert

 

[The_Doc_Man]

but you CAN STILL call the MSXML library code as "asynchronous"

Which means that when you do a "CreateObject" for that XML beastie, you are creating a child process, perhaps? If so, that is an exception to saying that event code is synchronous. A child object in another type of utility has its own execution context, as for example Word, Excel, Classic Outlook, or PowerPoint. In fact, if it is done on a multi-processor system, having semi-autonomous objects is a case where you can have TRUE parallel code execution.

 

[Albert D. Kallal]

Which means that when you do a "CreateObject" for that XML beastie, you are creating a child process, perhaps? If so, that is an exception to saying that event code is synchronous. A child object in another type of utility has its own execution context, as for example Word, Excel, Classic Outlook, or PowerPoint. In fact, if it is done on a multi-processor system, having semi-autonomous objects is a case where you can have TRUE parallel code execution.

True, but you still free to make asynchronous calls - even with CreateObject().
I mean, if you want to call a long running SQL stored procedure?
You can make that call as synchronous, and the VBA code will wait.
Or you CAN make that call as asynchronous - the code will NOT wait!!!

So, no, there is NOT some other process running in VBA here at all. If we send the request to the web site, we are done, and can let the object even go out of scope - the request call will continue to run - even if we turn off our computer.

Remember, in both above cases (calling SQL server store proc), or calling the web site endpoint?

That by "nature" is another computer and CPU process you are calling!
(this is for sure another process thread on another computer!!!)

So, then it is the web site or SQL server that we are waiting for, and that is MOST certainly another computer!!!

quote from original poster:

the code will keep waiting for over 45 minutes

And that code is NOT waiting for VBA or Access code - it's waiting for a response from the web site!!

So, while we can't when calling that SQL store procedure, or in this case a web service call sink events from that object into VBA using "with events"?

We can still make the call to the web server as asynchronous. And that means no 45 minute lockup or freeze up if we make the call as asynchronous as per my posted code example.

Since we not stuck waiting for the call to complete, then we are NOW free to use a polling loop as per my above example.
And with that polling loop, we could even add/allow the user to hit the ESC key to bail out. In my example, we bail out after 10 seconds.

So, the key concept here is that if the object in question supports asynchronous operations, even for one's that don't have VBA support for sinking events (VBA with events)?

We are often STILL free to make an asynchronous call - and 2 really great examples are calling SQL server stored procedures (or even long running updates), or in this example, making a call to a web server/service.

In both of these cases, the VBA code will not wait, and we don't have any VBA waiting - it's the other server doing the work now.

However, if one cares when the asynchronous call is done? (again, as per this web call example)? Then we have to resort to a polling loop, but we then gain and achieve some non locking code, and are free to poll, wait, or bail out after a given amount of time - exactly what we need and want for this example.

Now, if we did't want to use a polling loop, and REALLY did want to use "with events", and thus we have a event (code stub) that triggers when the code is done? Well, if the object in question does not support "with events", then the simple matter is to move the code to .net, sink the event back to VBA, and then not even the VBA requires a polling loop - but it will of course require the object to exist in memory while we wait for the response.....

Turns out both ADO, or web service calls from vb.net do allow events, and since it rather easy to build a vb.net class that will trigger events back to VBA? Then one could create a small vb.net class with the above code, and we would have both asynchronous operation, and also have events that trigger. (however, this means no late binding --- often in many cases, this means a polling loop is a better solution for numerous reasons).




R
Albert