Pause VBA procedure if it already running

[Jim Franklin]

Hi,

I have a client application which is communicating with a server, sending
and receiving xml strings using the Winsock control. I am using Access 2003
and Win XP.

I have several different bits of code to generate the various commands I
need to send, but one procedure ProcessXML in a standard module which
connects to the Winsock control, sends the command and then processes the
response received. I have global variables in this code and one Winsock
control open on a hidden form.

My problem is that there are several different events running simultaneously
which may try to communicate with the Winsock and I am concerned about one
area of my application trying to run ProcessXML when it is already mid-way
through processing a previous communication. For example, one form updates
itsself using the OnTimer event to periodically request the latest
information from the server application, but the user can manually at any
time send other commands. Is there any way of suspending execution of
ProcessXML if it is already running, either until it has finished, or
waiting a set amount of time before trying again?

I did think about having some sort of global variable, say blBusy which I
test for at the start of the procedure using a Do...Loop and set to true
while it is running, but this seems very inefficient and I would have
thought will cripple performance.

e.g.
Do Until blBusy = False
Loop

blBusy = True

...run all the procedure code

blBusy = False

Is there a better way of doing it than this? Is DoEvents any use to me?

I hope all this makes sense! Many many thanks for any advice and pointers
anyone
can provide!!

Jim

 

[Jim Franklin]

Has no one got any ideas on this? Please help!

Thanks,
Jim

 

[Access Developer]

I haven't done exactly what you are doing, but it seems that you have a
procedure ProcessXML upon which you rely to receive a request, send,
process, and receive before it takes another request. That can be handled
with a message queuing approach... which can be implemented in one of
several ways... and I don't understand enough about your data and processing
to be more specific.

If you Google on "message queue" you will get a lot of hits on "Microsoft
Message Queuing", which I do not think is applicable... it is for queuing
messages to/from other servers or machines. My guess is that exploring that
will be a "dead end". It might, or might not, be adaptable to what you want
to do.

If your data is appropriate, you may be able to create an array in which to
queue your requests to ProcessXML and the associated data, and, if there is
not already a request queued, call ProcessXML. If there is a message
queued, you can either just queue the current request, or queue it and
respond to the calling program that it has been "accepted". At the end of
one complete execution of ProcessXML, it can check the array (queue) to see
if additional messages have been queued and, if so, take the next one, in
turn.

Note that I am suggesting an approach for you to follow, not implying that I
have a "solution". Serializing _forms_ is easy, because a dialog/popup will
prevent activity on the calling form, but it doesn't apply to VBA code
execution.

 

[Jim Franklin]

Hi Larry,

Thanks for your reply. I appreciate your time on this. It is an interesting
suggestion, to have an array of queued requests, but I don't know how easy
it would be to implement in my application.

All I really need is a way of suspending a procedure call if the procedure
is already running. Maybe the best way is indeed to have a global boolean
variable, which is set True at the start of ProcessXML and set False at the
end.

What concerns me, is if I have a separate thread, with some sort of
Do..Until loop testing the boolean before it runs ProcessXML, will the
application performance suffer greatly as a result?

Jim

 

[Access Developer]

What concerns me, is if I have a separate thread,
with some sort of Do..Until loop testing the boolean
before it runs ProcessXML, will the application
performance suffer greatly as a result?

Multi-tasking control and multi-threading, if any, are controlled behind the
scenes by Microsoft Access. It will depend on how your application is
designed. The problem would, I think, be avoided by a "message queing"
implementation.

It would also depend on long it takes ProcessXML to run. If it runs without
I/O, then chances are you may not ever have much of a queue built up. The
vast difference in speed between code execution and performing input/output
typically means that you are unlikely to have CPU-limited performance
problems with Access.

 

[Jim Franklin]

Depending on network traffic etc. it can take ProcessXML up to a full second
to run, as it has to wait for a response from the server. Unfortunately this
is a 3rd party application and there is nothing I can do about this.

I am trying to work out how I would implement a message queueing system, as
ProcessXML can be called from various different forms, and can handle a
variety of different commands. Depending on the command and the response, my
app then does all sorts of different things with the data; updates screens,
initialises secondary processes etc.

Does having a separate thread with a Do Until...Loop spinning away slow
things down that much? The client is using dedicated POS pc's, which are
slower and have less memory than my development ones.

Larry, thanks once more. I do appreciate it.

Jim

 

[Access Developer]

Does having a separate thread with a
Do Until...Loop spinning away slow
things down that much? The client is
using dedicated POS pc's, which are
slower and have less memory than
my development ones.

The best way to insure against "tight loops" is to include a "DoEvents"
statement in the Do Until loop. And, just about the only way to determine
whether it will be something no one will notice, or a discernable delay, is
to try it in the production environment. On the other hand, the Do Until
approach should be the very fastest to implement and test...

And, I'm interested to read how this works out... post back here, if you
have the time and energy.

You're welcome to the help -- that's primarily why I participate in this
newsgroup (I can't "pay them back", but a lot, really a lot, of people have
helped me during my long time in the computer business, and all they wanted
was for me to help someone else when I could). But, on a more selfish note,
it keeps me up to date on how people are using Access and the kinds of
issues they are encountering.

Best of luck,

 

[John Spencer]

In addition to DoEvents you might take a look at the code to
Make code go to Sleep
http://www.mvps.org/access/api/api0021.htm
Author: Dev Ashish


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Access Developer]

Thanks for the good catch, John.