Waiting for Shell to Finish

[ggarz1]

I have a batch file that runs a perl script needed to refresh certain table in my Access database. Due to the data and software I am working with, each time I want to run a report (from a single form w/ 3 parameters) I need to delete the tables contents (to avoid redundant data) and run the batch file. I would like to have this occur in the the opening of the form, so that the user will always have the most up to date data. At this point everything works fine, but the user can click ok and proceed to the report before the batch file is finished. The dos window on the screen is the only way to tell the batch has not finished yet (which is not ideal). I am concerned that the user could click OK on the form before the batch file has completed filling the Access table. How should I go about this? I had the idea of either hiding the form or making the button inactive until the batch file is finished, but I am not sure how to execute this. Any other ideas would be great.

Geoff

[FishVal]

Hi, Geoff.

Take a look at TheScripts Tip of the Week #47 - Windows Script Host Object library.
As soon as it is a batch file you may put "echo" command at the and of the batch file and get the message in access code via WshExec.Stdout property.

Regards,
Fish

[ggarz1]

Thanks FishVal.

I had created the batch file in windows, just to automate the perl script I was running. The batch file is static and only calls perl and the script to run. Can I use the wsh echo command from a batch file/command prompt? Am I better of creating a wsh script instead of a batch file to execute the perl? Or should I execute the code in my vb script via shell instead? I would like to keep everything very basic for now.

[FishVal]

Frankly speaking, I don't see much differences between running the application directly or through batch file.

Best regards,
Fish

[NeoPa]

Check out ShellWait() Function.

[ADezii]

1. Place the code to DELETE your Tables and Run the Batch File as Normal in the Form's Open() Event. Place Code Line Numbers 1 and 2 as the 'first' items in the Open() Event. We will set the Timer Interval for the Form before we do anything. For demo purposes, I set it to 1 second:
   

   'Set the Form's Timer Interval for 1 Second
   Me.TimerInterval = 1000        'in milliseconds
   
   Dim lngCounter As Long
   
   'Simulate Table Deletions and Batch File Run
   For lngCounter = 1 To 10000
     Debug.Print lngCounter
   Next

2. Open your Form in 'Hidden' Mode via an AutoExec Macro.
3. What will now happen is that the Form will be Open but Hidden from view, its Timer Interval will be set to 1 second, Tables will be Deleted, your Batch File will be Run, all the while the Form is not visible to the User.
4. Place code in the Form's Timer Interval, which will now be executed every 1 second (you can change this value). At the end of 6 seconds, the Form will now become Visible, and the Timer will be disabled. All your code will have been executed while your Form was Invisible, with no possible intervention from the User.
   

   Private Sub Form_Timer()
   Static intTimerCount As Integer
   
   intTimerCount = intTimerCount + 1000
   
   If intTimerCount Mod 6000 = 0 Then      'at 6 Seconds
     Me.Visible = True
     Me.TimerInterval = 0
   End If
   End Sub

5. In case you are wondering, I have tried this logic and it works quite well. You circumstances are different, but I feel it may be worth a try.