Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 11.5 Run Another Program and Pause Until It's Done

11.5.1 Problem

From
within your application, you sometimes need to run another Windows
application, or a DOS batch file or utility program that requires
some time to do its job. You'd like your Access
application to pause until this other program has finished its work.
Every time you try it, though, the code starts up the other
application but then keeps on going. Is there a way to make Access
wait until the other application has completed before moving on?

11.5.2 Solution

The Shell function
in VBA (and the ShellExecute function we will mention in the Solution
in Recipe 11.7) returns a unique long integer value representing the
running task. You can use this valuethe

instance
handle for the running applicationto track the
state of the application. Given an instance handle, you can use the
OpenProcess API function to retrieve the process handle for the
process. Armed with that process handle, you can then call the
GetExitCodeProcess function continually until it sees that the
process has shut down. Because this happens automatically once a DOS
application has finished running, you can use this technique to wait
until a DOS window has closed before moving on in your application.

The sample form in

11-05.MDB ,
frmTestWait, allows you to try starting both a DOS
application and a Windows application, and wait for either to
complete. There's also a button that allows you to
start a DOS application but continue the attached code. In each of
these cases, the sample code attempts to load the text file

C:\ACBTEST.TXT (choosing either of the first two
buttons sends the output of CHKDSK to

C:\ACBTEST.TXT for you) into a text box on the
form once the application you've started finishes
its work, as shown in Figure 11-6. (In the case
where the code doesn't wait for the other
application, of course, there's nothing to load.)
Use frmTestWait, try each command button to test the functionality
demonstrated in this solution. The first button runs CHKDSK, waits
until it has written its output to

C:\ACBTEST.TXT , and then loads the text file.
The second button runs CHKDSK and immediately loads the text file.
The final button, Run Notepad, loads a Windows application, Notepad,
and waits until you've closed it before loading the
text file.

Figure 11-6. frmTestWait after it has run an application

To use this functionality in your own applications, follow these
steps:

1. Import the module basRunApp from

   11-05.MDB into
   your application.

2. To run
   another application and wait for it to finish before going on with
   your code, call the acbRunAppWait subroutine, passing it two
   parameters: a command string telling it what to run, and an integer
   designating the window mode you'd like to use (see
   Table 11-3). These are essentially the same values
   you use when calling the ShellExecute Windows API function, as shown
   in the Solution in Recipe 11.7.

 vbHide

 vbNormalFocus

 vbMinimizedFocus

 vbMaximizedFocus

 vbNormalNoFocus

 vbMinimizedNoFocus

For example, to start the Windows calculator maximized, use a
statement like this:

acbRunAppWait "CALC.EXE", vbMaximizedFocus
MsgBox "Done with the calculator."

You won't see the message box until you finish with
the calculator.

11.5.3 Discussion

The secret to the acbRunAppWait
subroutine is its use of the Windows API function
GetExitCodeProcess. This function takes as a
parameter the process handle of an application, which you can
retrieve by calling the

OpenProcess API function
with the instance handle returned by the call to
Shell. GetExitCodeProcess
monitors a running process and retrieves that
process's exit code. As long as the process
continues to run, GetExitCodeProcess returns the
value STILL_ACTIVE (defined in basRunApp).

Consider the following code, which checks for the existence of a
running application:

Do
' Attempt to retrieve the exit code, which will
' not exist until the application has quit.
lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
Loop Until lngExitCode <> STILL_ACTIVE

Though this will almost do what you need,
it won't quite succeed. You've left
Access running a tight loop, waiting for the new application to
finish. Unfortunately, this loop grabs all of
Access's clock cycles, looping and waiting for the
other application to be done. While this loop is active, Access is
effectively dead. All the rest of Windows continues to work
perfectly, but Access's only thread of execution is
completely tied up. You'll see that Access simply
can't update its screen, for example, while
you're running Notepad.

The solution, then, is to be a good citizen, allowing Access its
processing time. To do this, you must add a
DoEvents statement inside the loop. This allows
Access to continue working while this code loops, waiting for the
application you started to finish. (See the Solution in Recipe 7.4 for more information on
DoEvents.) Thus, the

acbRunAppWait subroutine looks like this:

Public Sub acbRunAppWait(strCommand As String, intMode As Integer)
' Run an application, waiting for its completion
' before returning to the caller.
Dim hInstance As Long
Dim hProcess As Long
Dim lngRetval As Long
Dim lngExitCode As Long
On Error GoTo acbRunAppWait_Err
' Start up the application.
hInstance = Shell(strCommand, intMode)
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION Or SYNCHRONIZE, _
True, hInstance)
Do
' Attempt to retrieve the exit code, which will
' not exist until the application has quit.
lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
DoEvents
Loop Until lngExitCode <> STILL_ACTIVE
acbRunAppWait_Exit:
Exit Sub
acbRunAppWait_Err:
Select Case Err.Number
Case acbcErrFileNotFound
MsgBox "Unable to find '" & strCommand & "'"
Case Else
MsgBox Err.Description
End Select
Resume acbRunAppWait_Exit
End Sub

To use the Shell command, you must
specify an executable file. If you need to run a DOS internal command
or redirect the output from a program to a text file,
you'll need to load a copy of

COMMAND.COM to do your work. In addition,
you'll need to use the /C switch,
indicating to COMMAND.COM that you just want a
temporary instance that should quit when the program you run
finishes. For example, to run the CHKDSK.EXE
program directly, you could use the following function call (all
these examples assume that the necessary programs are available in
the DOS PATH):

hInstance = Shell("CHKDSK.EXE", vbMinimizedNoFocus)

To run DIR, on the other hand,
you'll need to start COMMAND.COM
first:

hInstance = Shell("COMMAND.COM /C DIR C:\*.BAT", vbMinimizedNoFocus)

To redirect the output from a program to a text file,
you'll also need to use
COMMAND.COM:

hInstance = Shell("COMMAND.COM /C CHKDSK C: > C:\ACBTEST.TXT", _
vbMinimizedNoFocus)

You may also want to study the FileRead subroutine in the sample form's module, which demonstrates how to open a text file and read its contents directly into a control on a form.