Launching Dos Batch file from Access 1

[Stickarm]

I am trying to launch a Dos Batch file from access which ftp's a file from our mainframe to a text file on a server. I have a line of code that runs the batch file, Call Shell("batchfile", 1), this runs fine, but the problem I am having is that once the batch file is done, I then need to import the text file it just created. I have the code to do this but it runs immediately after I start the batch file and doesn't give the file transfer time to complete. Is there any way from access to know when the dos batch file is complete prior to moving to the next line of code. I was thinking of just using a pause but because I am running it over a network the time the transfer takes could very.

Thanks in advance
Phil

 

[smedvid]

Here is a code snippet where I shell to DOS to execute a DIR command to create a text file. I then read the text file into a database table, line by line.

I did need to add some delay timers into the process, since the DIR to create a text file was not finishing before the next lines of code were finishing... htwh


Set oFSO = CreateObject("Scripting.FileSystemObject&quot

'create a directory listing by writing a batch file and executing it
sCommand = "dir " & rsBase("basepath&quot & "*.mdb /s /b /o-s >" & APP_PATH & "dir_list_mdb.lst"
Open APP_PATH & "dir.bat" For Output As #1
Print #1, sCommand
Close #1

'appLoop Shell(APP_PATH & "dir.bat&quot
wHandle = Shell(APP_PATH & "dir.bat&quot

'delay to wait for the directory list
'file to finish writing from the bat command. This is necessary becuase
'the Shell command is asyncronous
DelayTime 5
appLoop wHandle

'clear the working table
thisdb.Execute "delete from dir_list"

'load the directory listing into the working table DIR_LIST
While Not rsBase.EOF
'open the directory list file and load it into a table
Open APP_PATH & "dir_list_mdb.lst" For Input As #1
Set rsDirList = thisdb.OpenRecordset("select * from dir_list order by 1, 2&quot
Do While Not EOF(1)
Input #1, sFilePath
'strip the base path to save the overall number of characters
If InStr(1, sFilePath, rsBase(&quot;basepath&quot) <> 0 Then
sFilePath = Mid(sFilePath, Len(rsBase(&quot;basepath&quot) + 1)
End If
'add it to the table
With rsDirList
.AddNew
.Fields(&quot;basepath&quot = rsBase(&quot;basepath&quot
.Fields(&quot;filepath&quot = sFilePath
.Update
End With
Loop 'end doloop not EOF(1)
Close #1
.....


Public Function DelayTime(PauseTime As Integer)
Dim start
'PauseTime = 4 ' Set duration.
start = Timer ' Set start time.
Do While Timer < start + PauseTime
DoEvents ' Yield to other processes.
Loop

End Function

Sub appLoop(wHandle As Long)
On Error GoTo errorhandler
Do While 1
AppActivate wHandle, False
Loop

errorhandler:
Exit Sub

End Sub
Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://users.erols.com/medvid/

 

[bondm007]

You can shell to a system command and instruct Access to wait for that command to finish before continuing...
The code is at:

http://www.geocities.com/bondm007

Hope this helps....

 

[Stickarm]

Thanks Bond,
That accomplished exactly what I needed.
Works great.
Phil

 

[vr]

I create and call dat and bat files in VBA. This eliminates the need to deploy more than one file with the application.