Wait for multiple processes.

My boss believes in simple is best so of course he wants everything done using one button. I have written the code that will run multiple things when the button is clicked but I wonder if I should use wait() or something more robust. What concerns me is the the files the button activates can change in size from one line of data to >500 lines of data and there isn't really a good way to set the time for the wait. Any suggestions.

Private Sub GetData_Click()

Dim strFilename As String
Dim strFilename2 As String
Dim strFilename3 As String
Dim strFilename4 As String

Dim objFS As Object
Dim objTS As Object


DoCmd.OpenQuery "CadCamHistory" 'Appends current data from CadCam table to CadCamHistory table.
Wait (5)
DoCmd.OpenQuery "MetalsaHistory" 'Appends current data from Metalsa table to MetalsaHistory table.
Wait (5)
DoCmd.OpenQuery "PurgeCadCamUtilities" 'Deletes current data from CadCamUtilities table.
Wait (5)
DoCmd.OpenQuery "PurgeMetalsaAccept" 'Deletes current data from MetalsaAccept table.
Wait (5)

Shell "Y:\K1\NCRails\Metalsa\Getdata.bat", vbNormalFocus 'runs a dos bat file that pulls data from a
Wait (30) 'vendor ftp site into our ftp site.

DoCmd.RunMacro "Macro1" 'runs the Macro that transfer 3 text files into 2 tables
Wait (50)

'--The next batch of code goes out to the ftp site and deletes the data in each of the text files.
Const For_Writing = 2
strFilename = "Y:\K1\NCRails\CADCAM\Renton.txt"
strFilename2 = "Y:\K1\NCRails\Metalsa\Renton.txt"
strFilename3 = "Y:\K1\NCRails\Metalsa\Output.txt"
strFilename4 = "Y:\K1\NCRails\CADCAM\RentonOther.txt"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextfile(strFilename, For_Writing)

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextfile(strFilename2, For_Writing)

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextfile(strFilename3, For_Writing)

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextfile(strFilename4, For_Writing)

DoCmd.OpenQuery "NCBatchTest" 'runs a make table query
Wait (10)
DoCmd.OpenQuery "MetalsaAcceptTempMkTbl" 'runs a make table query
Wait (10)
DoCmd.OpenQuery "CadCamMkTbl" 'runs a make table query
Wait (10)

DoCmd.OpenQuery "UpdateBatchNC" 'runs an update query
Wait (10)
DoCmd.OpenQuery "UpdateNcSchAccept" 'runs an update query
Wait (10)

End Sub

Why do you need the Waits?

These commands will all processin sequence - you don't need these to wait to complete processing, before going to the next one.

If you open a form from a from then both forms will continue to run, and accept input etc. Sometimes you need to "freeze" one while the other completes, but wait isn't the right technique to do that, anyway.

I deleted out all the wait()'s and ran it but the bat file had not finished running before it moved on to the make table function which cause the bat file to finish prematurely so part of the data did not import as needed.

sorry, didn't see that, you do have to wait for the bat file to process before you can continue. I will have a look here for the asynchronous wait command, which will be here somewhere and post it for you.

Couldn't find this in the forum, but I dug out some old code.

Try this, this declares some windows API's, This value of synchronize may be appropriate for a bat file, I think. Put all this in a module, and then try calling your bat command as

launchapp32("your bat file name") instead of your shell command


Private Declare Function Openprocess Lib "Kernel32.dll" (ByVal _
dwAccess As Long, ByVal flnherit As Integer, ByVal hObject _
As Long) As Long

Private Declare Function WaitForSingleObject Lib "Kernel32" (ByVal _
hHandel As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CloseHandle Lib "Kernel32" (ByVal _
hObject As Long) As Long


Function LaunchApp32(MYAppName As String) As Integer
On Error Resume Next
Const SYNCHRONIZE = 1048576
Const INFINITE = -1&
Dim ProcessId&
Dim ProcessHandle&
Dim Ret&

LaunchApp32 = -1
ProcessId = Shell(MYAppName, vbNormalFocus)
If ProcessId <> 0 Then
ProcessHandle = Openprocess(SYNCHRONIZE, True, ProcessId&)
Ret = WaitForSingleObject(ProcessHandle, INFINITE)
Ret = CloseHandle(ProcessHandle)

MsgBox "Back From Shell: " _
& MYAppName & " Finished", 64
Else
MsgBox "ERROR: Unable to start " & MYAppName
LaunchApp32 = 0
End If
End Function

I will try to do as you instruct but it might be a little over my head.

I am going to go out on a limb and assume that in the code above I need to change MYAppName to the name of my bat file??

how will the launchapp know where the bat file is located?

you tell it

instead of calling

Shell "Y:\K1\NCRails\Metalsa\Getdata.bat", vbNormalFocus

call

launchapp32("Y:\K1\NCRails\Metalsa\Getdata.bat")

the launchapp contains code to set the shell process going but not to finish and go back to your calling progrcess until it knows the batch job has completed. Thats what all the windows API calls do. (assuming it works ago, but I'm sure it will. )



call

gemma-the-husky - thanks so much for this. works brilliantly and is the most succinct I was able to find of all variations of this function

You could add a line to end of the .bat file which creates a txt file when it's finished before quitting.
Len in your VBA after starting the .bat running, the next line checks for the existence of the text file, if it's not there, wait for a period of time then retry, if it is there delete the .txt file and carry on.

Not very elegant but simple to do and solves the problem.