This documentation is archived and is not being maintained.

Running and Pausing Other Applications from Access 2007

Office 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to run an external application from within Microsoft Office Access 2007 and then prevent Office Access 2007 from continuing until that external application completes its task. (5 printed pages)

Office Visual How To

Applies to:2007 Microsoft Office system, Microsoft Office Access 2007

Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin, and Andy Baron. Copyright © 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.

Ken Getz, MCW Technologies, LLC

April 2009

Overview

Sometimes, from within your Microsoft Office Access 2007 application, you need to run another application or batch file that requires some time to do its job. As a result, you need a way to pause your Office Access 2007 application until this secondary program completes its task. This article demonstrates how to use a Windows API function to prevent your Access application from continuing until that secondary program completes its task.

See It

Running Other Applications from Access 2007 video

Watch the Video

Length: 09:07 | Size: 11.10 MB | Type: WMV file

Code It | Explore It

Download the sample database

The Shell function in VBA returns a unique long integer value that represents a running application. You can use this value—the instance handle for the running application—to 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. Then, by using that process handle, you can call the GetExitCodeProcess function continually until it sees that the process has shut down.

Use the sample form frmTestWait in RunAppAndWait.accdb to experiment. Try starting a command window and a Windows application, and then waiting for either to complete. You can also click a button to start a command window and continue to run the attached code. In each case, the sample code attempts to load the text file C:\WaitTest.txt into a text box on the form once the application that you start finishes its work.

NoteNote

In the case where the code doesn't wait for the other application, there is nothing to load. Also, choosing either of the first two buttons sends the output of CHKDSK to C:\WaitTest.txt for you.

The first button on frmTestWait runs CHKDSK, waits until it has written its output to C:\WaitTest.txt, and then loads the text file. The second button runs CHKDSK and immediately loads the text file. The last button, Run Notepad, loads the Notepad Windows application and waits until you close it before loading the text file.

Figure 1. Sample form frmTestWait after running the CHKDSK application

Sample form frmTestWait after running CHKDSK

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

  1. Import the module basRunApp from RunAppWait.accdb into your application.

  2. To run another application and wait for it to finish before going on with your code, call the RunAppWait subroutine and pass it two parameters: a command string that tells it what to run, and an integer from the following table that designates which window mode you want to use.

    Table 1. Window Display Options Using Shell

    Value

    Control VBA Constant

    Description

    0

    vbHide

    Hidden

    1

    vbNormalFocus

    Restored to its previous state (neither minimized nor maximized)

    2

    vbMinimizedFocus

    Made visible and minimized

    3

    vbMaximizedFocus

    Made visible and maximized

    4

    vbNormalNoFocus

    Displayed, but doesn't gain the input focus

    6

    vbMinimizedNoFocus

    Minimized (as an icon) when started

For example, to start the Windows calculator maximized, use a statement similar to the following.

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

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

The secret to the RunAppWait procedure is the Windows API function GetExitCodeProcess. This function takes the process handle of an application as a parameter. You can retrieve the process handle by calling the OpenProcess API function with the instance handle that is returned by the call to Shell. GetExitCodeProcess monitors a running process and retrieves the exit code of that process. As long as the process continues to run, GetExitCodeProcess returns the value STILL_ACTIVE (defined in basRunApp).

Consider the following code from the RunAppWait procedure, 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.
    Call GetExitCodeProcess(hProcess, lngExitCode)
    DoEvents
Loop Until lngExitCode <> STILL_ACTIVE

This code watches for the running process to complete. The DoEvents statement in the code is crucial; it leaves Access running a tight loop, waiting for the new application to finish. Unfortunately, while this loop is active, Access is effectively dead; its one thread of execution completely tied up. For example, Access cannot update its screen while you run Notepad. (Comment out the DoEvents code and try running Notepad to verify the behavior.)

The solution is to give Access processing time by adding a DoEvents statement inside the loop. The following version of RunAppWait lets Access continue to work while it is waiting for the secondary application to finish.

Sub RunAppWait(strCommand As String, intMode As Integer)
   ' Run an application, waiting for its completion
   ' before returning to the caller.

Const PROCESS_QUERY_INFORMATION = &H400
Const SYNCHRONIZE = &H100000

Const STILL_ACTIVE = &H103&

   Dim hInstance As Long
   Dim hProcess As Long
   Dim lngExitCode As Long

   On Error GoTo HandleError
   ' 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.
      Call GetExitCodeProcess(hProcess, lngExitCode)
      DoEvents
   Loop Until lngExitCode <> STILL_ACTIVE
   
ExitHere:
   Exit Sub

HandleError:
   Select Case Err.Number
      Case errFileNotFound
         MsgBox "Unable to find '" & strCommand & "'"
      Case Else
         MsgBox Err.Description
   End Select
   Resume ExitHere
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 must load a copy of the command processor, Cmd.exe, to do your work. In addition, you must use the /C switch to indicate to the command processor 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.

NoteNote

The examples assume that the necessary programs are available in the DOS PATH.

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

To redirect the output from a program to a text file, call the command processor instead by using code similar to the following.

Private Const FILE_NAME = "C:\WaitTest.txt"
Private Const COMMAND_TEMPLATE = "cmd /C CHKDSK C: > {0}"

Dim command As String
command = Replace(COMMAND_TEMPLATE, "{0}", FILE_NAME)
RunAppWait command, vbHide

To run an application and then return immediately to Access, you can call the simple RunApp procedure in basRunApp, which looks like the following code (as you can see, most of the code is error handling).

Sub RunApp(strCommand As String, intMode As Integer)

   ' Run an application, returning immediately to
   ' the caller. Just presented as a parallel for
   ' RunAppWait.

   On Error GoTo HandleError
   Shell strCommand, intMode
   
ExitHere:
   Exit Sub
   
HandleError:
   Select Case Err.Number
      Case errFileNotFound
         MsgBox "Unable to find '" & strCommand & "'"
      Case Else
         MsgBox Err.Description
   End Select
   Resume ExitHere
End Sub
Show: