Solved

How can I improve my existing FTP & table update process that uses a progress Bar to let the user know the system is not locked up.

Posted on 2006-06-28
1
315 Views
Last Modified: 2012-05-05
Hello,

GOAL:
*****
To improve the existing FTP/data update process

I have 9 files that I download from a FTP SERVER and after the files are downloaded I run another process to transfer and update the tables in the DB.  To monitor this process I use a splash screen to notify the user about the data refresh  process right before the FTP process starts and the progress bar pops up after the FTP process ends to track the remaining data refresh steps.

Summary:
1. user clicks data refresh button
2. ftpsplash screen opens for 3000
3. FTP: dos windows opens minized listing the files being transfered
4. popup progress meter form opens and performs the file transfer and table update using DoCmd

Questions:
********
1) How can the existing code be modified to sleep until all ftp 9 files are downloaded to the designated local directory and then open the POP form.  This will help me from using a lot of sleeps.

2) How Can I make the minized dos ftp window hidden--If possible.

3) How can I simplify and/or combine both the FTP and table refresh process to be tracked by the progress bar.  

Existing Code:
**********

1) Data Refresh Click Module called from the db custom menu bar command
*******************************************************
Option Compare Database

Public Function DataRefresh()

'Open & Close FTP Slplash Screen
DoCmd.OpenForm "frmFTPSplashScreen", , , , , acDialog
Sleep 1000
'DoCmd.Close acForm, "frmFTPSplashScreen"

'Step 1 Start FTP Download Process
Dim filename As String
Dim FilePath As String

Call Transmit_FTP(FilePath, filename)

'Step 2 Download File Location
FilePath = "ABC.com/downloads/TEST"

Sleep 15000
DoCmd.OpenForm "frmProgress", , , , , acDialog

End Function

**********************************************************

2) ftpstartup splash screen code
***********************

Option Compare Database
Dim sngStartTime As Single

Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
sngStartTime = Timer()
    Me.TimerInterval = 250   ' milliseconds

Private Sub Form_Timer()
    With lblBlinking
        If Timer() - sngStartTime > 30 And .Visible Then _
            Me.TimerInterval = 0
        .Visible = Not .Visible
    End With

'On the forms timer event close the start-up form
Sleep 2000
DoCmd.Close acForm, "frmFTPSplashScreen"

End Sub
***********************************************************

3) subFTP Module (see #1)
********************
Option Compare Database

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function Write_FTP_Script(Path As String, filename As String)

Dim db As Database
Dim rs As Recordset
Dim SQL As String

Dim FileNum As Integer
Dim SCPFileName As String
Dim Directory As String
Dim OutputLine As String


SCPFileName = "FTP.SCP"
Directory = "C:\DB\downloads"
FileNum = FreeFile()

If UCase(Dir(Directory, vbDirectory)) <> "downloads" Then
    MkDir "C:\DB\downloads"
End If

Open Directory & "\" & SCPFileName For Output Access Write Lock Write As FileNum
   
    OutputLine = "ABCinc"
    Print #FileNum, OutputLine
   
    OutputLine = "12345"
    Print #FileNum, OutputLine
   
    OutputLine = "lcd C:\DB\downloads " & Path
    Print #FileNum, OutputLine
   
    OutputLine = "prompt"
    Print #FileNum, OutputLine
   
    OutputLine = "binary"
    Print #FileNum, OutputLine
   
    OutputLine = "cd /"
    Print #FileNum, OutputLine
   
    OutputLine = "cd downloads/TEST"
    Print #FileNum, OutputLine
   
    'OutputLine = "lcd " & Path
    'Print #FileNum, OutputLine
   
    OutputLine = "mget " & Left(filename, 21) & "*"
    'OutputLine = "mget -p " & Path & "\" & Left(FileName, 21) & "*"
    Print #FileNum, OutputLine
   
    OutputLine = "cd /"
    Print #FileNum, OutputLine
   
    OutputLine = "bye"
    Print #FileNum, OutputLine
   
    Close #FileNum

Call Clear_Disk_Buffer

End Function

Public Function Transmit_FTP(Path As String, filename As String)

'User input/output
Dim Msg As String
Dim Title As String
Dim Buttons As Integer
Dim Response As Integer

'For holding the export file strings
Dim FileNum As Integer
'Dim Path As String
'Dim FileName As String

Call Write_FTP_Script(Path, filename)
Call Clear_Disk_Buffer

Shell "FTP -s:c:\DB\downloads\FTP.SCP ABC.com", vbMinimizedNoFocus

End Function

Public Function Clear_Disk_Buffer()
'This is called beecause sometimes the modern CPUs try to fire the FTP script before the _
 file has actually written it all to disk.  This writes at least an 8K file to the drive _
 to flush the buffers.
Dim i As Integer

Dim filename As String
Dim FileNum As Integer
Dim OutputLine As String
Dim Directory As String
'Dim x As Long

Directory = "C:\DB\downloads"
FileNum = FreeFile()


If UCase(Dir(Directory, vbDirectory)) <> "downloads" Then
    MkDir "c:\DB\downloads"
End If
'x = CLng(2000) * 365
i = 0
FileNum = FreeFile()
filename = "c:\DB\downloads\Buffer_Run.txt"
Open filename For Output Access Write Lock Write As FileNum
OutputLine = "1234567890123456789012345678901234567890123456789012345678901234567890123456789"
Do Until i > 30000
    Print #FileNum, OutputLine
    i = i + 1
Loop
Close #FileNum
Debug.Print filename & " size =" & FileLen(filename)
If Dir(filename) = "Buffer_Run.txt" And FileLen(filename) > 0 Then
    Debug.Print Dir(filename)
    Kill filename
End If

End Function

**********************************************************************

4) Progress Bar
************

Option Compare Database
'Dim lCounter As Long
'Dim sngStartTime As Single

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False
DoCmd.Restore

' As soon as you click the command button, start reading records. Because
' you've started reading records, update the status caption to READING.
Me.Status.Caption = "Refreshing Data. Please Wait...."
'sngStartTime = Timer()
'Me.TimerInterval = 3000  'milliseconds

'Step 1. Clear_Disk_Buffer()
Sleep 30000

'Step 2 Delete Primary Existing Records
DoCmd.RunSQL "DELETE tbl1.* FROM tbl1"
DoCmd.RunSQL "DELETE tbl2.* FROM tbl2"
DoCmd.RunSQL "DELETE tbl3.* FROM tbl3"
DoCmd.RunSQL "DELETE tbl4.* FROM tbl4"
DoCmd.RunSQL "DELETE tbl5.* FROM tbl5"
DoCmd.RunSQL "DELETE tbl6.* FROM tbl6"
Me.LBL1.Visible = True
Me.LBL2.Visible = True
Me.LBL3.Visible = True
Me.Progress.Caption = "30%"
DoEvents

'Step 3 Delete Secondary Existing Records
Sleep 5000
DoCmd.RunSQL "DELETE tbl7.* FROM tbl7"
DoCmd.RunSQL "DELETE tbl8.* FROM tbl8"
DoCmd.RunSQL "DELETE tbl9.* FROM tbl9"
Me.LBL4.Visible = True
Me.Progress.Caption = "40%"
DoEvents

'Step 4' Making sure there is enough time between table delete and the import.
Sleep 30000

'Step 5 Transfer file from local to access directory
DoCmd.TransferText acImportDelim, "Tbl1Import ", "tbl1", "C:\DB\downloads\tbl1.txt"
Sleep 1000
DoCmd.TransferText acImportDelim, "TBL2Import", "tbl2", "C:\DB\downloads\tbl2.txt"
Sleep 1000
DoCmd.TransferText acImportDelim, "TBL3Import", "tbl3", "C:\DB\downloads\tbl3.txt"
Sleep 1000
DoCmd.TransferText acImportDelim, "TBL4Import", "tbl4", "C:\DB\downloads\tbl4.txt"
Sleep 1000
DoCmd.TransferText acImportDelim, "TBL5Import", "tbl5", "C:\DB\downloads\tbl5.txt"
Sleep 1000
DoCmd.TransferText acImportDelim, "Tbl6Import", "tbl6", "C:\DB\downloads\tbl6.txt"
Me.LBL5.Visible = True
Me.LBL6.Visible = True
Me.Progress.Caption = "60%"
DoEvents

'Step 6 Update Part & System Install Data
Sleep 2000
DoCmd.TransferText acImportDelim, "Tbl7Import", "tbl7", "C:\DB\downloads\tbl7.txt"
Sleep 1000
DoCmd.TransferText acImportDelim, "Tbl8Import", "tbl8", "C:\DB\downloads\tbl8.txt"
Sleep 1000
DoCmd.TransferText acImportDelim, "Tbl9Import", "tbl9", "C:\DB\downloads\tbl9.txt"
Me.LBL7.Visible = True
Me.LBL8.Visible = True
Me.Progress.Caption = "80%"
DoEvents
Sleep 1000

'Step 7 Update Mean Data
Sleep 2000
DoCmd.RunSQL "DELETE tblFigure2.* FROM tblFigure2"
Sleep 500
DoCmd.OpenQuery "Figure2"
Sleep 1000
DoCmd.RunSQL "DELETE tblMean1.* FROM tblMean1"
Sleep 500
DoCmd.OpenQuery "qryMean1"
Me.LBL9.Visible = True
Me.LBL10.Visible = True
Me.Progress.Caption = "100%"
DoEvents
Sleep 1000

' Set the caption for the Status label to DONE.
Me.Repaint
Me.Status.Caption = "Data Refresh Complete."
API_PlaySound "c:\windows\media\goodbyefemale.wav"

' Set the form's mouse pointer back to the default mouse pointer.
Screen.MousePointer = 0

Sleep 1000
DoCmd.Close acForm, Me.Name

DoCmd.SetWarnings True
End Sub

Private Sub Form_Timer()

'With Status
 '       If Timer() - sngStartTime > 100 And .Visible Then _
 '           Me.TimerInterval = 0
 '       .Visible = Not .Visible
  '  End With

End Sub
***************************************************

Thank you in advance :)

0
Comment
Question by:cesemj
1 Comment
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
Regarding FTP, u can use WinInet to do your own FTP sessions without shelling out

sample code can be found at  http://www.mvps.org/access/modules/mdl0037.htm

Now if u control your own ftp, u should know when all files been transfered etc etc etc






Harfang came up with an idea the other day. Use Animations
How does that grab you?

If u want to play a animated GIF in your form, u unfortunately can't just include it.
The workaround is to create a webpage with that gif in it
then create a webbrower control in your form, resized with no borders, add as a subform and thats it!


I did this before - just to see animated gifs in a form.
What I did was created one form (borderless, no toolbars, scrollbars etc) and placed my webbrowser in there
I added this form as a subform.

In the code, how I set the url to load was like this

Private Sub Form_Load()
    WebBrowser1.Navigate "C:\ee\pic.html"
End Sub


I force the form to always navigate to a fixed page





0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now