Download and Wait

[AvGuy]

I need to download an mdb file from a web server and import tables from the downloaded file - and I need to do this in code. What's the best way to get Access to wait for the download to complete before continuing?
AvGuy

 

[ddiamond]

Here is how I have accomplished this. There is probably a better way, but it seems to work.

First you'll need to create the function ShellAndWait:

Option Explicit

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Const SYNCHRONIZE = &H100000
Private Const INFINITE = -1&

' Start the indicated program and wait for it
' to finish, hiding while we wait.
Public Function ShellAndWait(ByVal program_name As String, ByVal window_style As Long) As Boolean
Dim process_id As Long
Dim process_handle As Long

    ' Start the program.
    On Error GoTo ShellError
    process_id = Shell(program_name, window_style)
    On Error GoTo 0

    DoEvents

    ' Wait for the program to finish.
    ' Get the process handle.
    process_handle = OpenProcess(SYNCHRONIZE, 0, process_id)
    If process_handle <> 0 Then
        WaitForSingleObject process_handle, INFINITE
        CloseHandle process_handle
    End If
    
    ShellAndWait = True
    
    Exit Function

ShellError:
    ShellAndWait = False
    MsgBox "Error starting task " & _
         vbCrLf & _
        Err.Description, vbOKOnly Or vbExclamation, _
        "Error"
End Function

Then use visual basic 6 to create the executable WaitUntilFileExists.exe.

Option Explicit

Sub EndIfFileExists()
  If Trim(Command) = "" Then End
  If Dir$(Command) <> "" Then End
End Sub

Private Sub Form_Load()
  Me.Caption = "Waiting for file " & Command
  lblFileName.Caption = Command
  EndIfFileExists
End Sub

Private Sub Timer1_Timer()
  EndIfFileExists
End Sub

This contains one form with a timer control.

Finally,
In your access app:

If Not ShellAndWait("ShellAndWait.exe " & FileName, vbMinimizedFocus) Then
  'error logic
else
  'process file
end if

 

[ddiamond]

Originally, I had attempted to do this with a simple loop that checks for the existance of a file, but this really clocked the CPU. Using a timer control seems to be much more CPU friendly.

 

[AvGuy]

I'll give it a try. I had just conceived an approach to open a separate mdb for the actual DL and shell & wait from the calling mdb until the DL completes and the other incidence closes. This may be better. Thanks for all your hard work and your willingness to share it.
AvGuy

 

[PHV]

You may consider something like this:
rc = CreateObject("WScript.Shell").Run yourCommandHere, , True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AvGuy]

That may be an approach too. The actual HTTP download code using the Internet Control is as follows:

Private Sub Download_Click()
stSite = "

http://MyDomain.com/MyFile.mdb"

stDest = "C:\My_DL_Folder"
Dim objHTTP As InetTransferLib.HTTP
Set objHTTP = New InetTransferLib.HTTP
With objHTTP
.HttpURL = stSite
.DestinationFile = stDest
.PromptWithCommonDialog = True
If .FileExists Then .OverwriteTarget = True
If Not .IsConnected Then
MsgBox "Unable to establish Internet connection", vbCritical + vbOKOnly, "ProgramName"
Exit Sub
End If
.ConnectToHTTPHost
.WriteHTTPDataToFile
End With

 

[ddiamond]

PHV,

rc = CreateObject("WScript.Shell").Run yourCommandHere, , True

Is that equivalent to my ShellAndWait command? If so, that is certainly a simpler way to go. Any ideas on how to wait until a file exists without writing your own custome command like I have done?

- Dan

 

[AvGuy]

WScript.Shell seems to be more appropriate to server side scripting using IIS. See:

http://www.microsoft.com/technet/desktopdeployment/depprocess/osddlex_6.mspx

 

[ddiamond]

p.s. The code I provided works for windows 95, windows 2000, and windows 2003. It does not work for windows 98 or windows ME. Those 2 operating systems seem to be the microsoft odd balls, or in my oppinion the microsoft mistakes. Anyone, feel free to disagree.