Creating FTP Script files with Access
by Chris Rowlett 
21st Century Work Place, Inc.
Dallas, TX   75254
214.435.4703
http://www.tfwpa.com/
  
The purpose of this project was to create an FTP download and data synchronization of real estate MLS Multiple Listing Service data and photos.  Part of that project included the downloading of text files that the MLS system provided on their FTP Site.  This code sample just deals with how to create the FTP scripting. 
  
First, you have to create a .bat file that is executed with a Shell command in Access. The code below shows how to write out a batch file from your Access VBA code.  The reason this is important is that it shows how you can create the batch file on the fly, and replace any text with data from your Access database.  

The batch file first makes sure that the program starts in the correct folder.  The FTP.exe program automatically downloads or uploads from the folder that is current at the time it executes, so you want to be sure it is in the correct folder at the start.  Then the batch file launches the FTP program.  The FTP program uses the text file that you included in the command line 
 

Open C:\FTPRootFolderName\BatchFileName.Bat For Output As #1
Print #1, "C:"
Print #1, "CD \"
Print #1, "CD FTPRootFolderName"
Print #1, "FTP -i -s:C:\FTPRootFolderName\FTPScriptFileName.txt"
Print #1, "echo fileiscomplete > C:\FTPRootFolderName\completed.txt"
Print #1, "exit"
Close #1

 
    
Then you create the FTP script text file, FTPScriptFileName.txt,  that is used by the FTP program.  This is where creating the script file using a database query is really helpful.  You can replace any of this text with data from your database.  For example, the variable vTheDataFileName, can be changed to any file name that you want thru your VBA code.
    
vTheDataFileName = "todaysstuff.tab"
Open "C:\FTPRootFolderName\FTPScriptFileName.txt" For Output As #1
Print #1, "open ftp.site.com"
Print #1, "username"
Print #1, "password"
Print #1, "ascii"
Print #1, "CD data"
Print #1, "CD full"
Print #1, "CD unzipped"
Print #1, "GET " & vTheDataFileName
Print #1, "Disconnect"
Print #1, "Quit"

 
   
Here is the VBA Shell code that launches the batch file that you created in the first step.
    
 

RetVal = Shell("C:\FTPRootFolderName\BatchFileName.Bat ", 1) 

 
 

You may wonder "What is that batch file code for?":

 echo fileiscomplete > C:\FTPRootFolderName\completed.txt"

This code is written to a file on the hard drive after the batch file completes its tasks.  You can scan for this file in your VBA code.  When the file is detected, then your Access VBA code can continue processing whatever it needs to do.  I put a 2 hour time delay scan in the program, so that if the completed.txt file never shows up due to some error, then the program automatically stops processing after 2 hours.

 

 

Other pages of interest

Microsoft Access Email Integration

Medical Insurance Audit Form System

Legal Process Server Management and Tracking

Purchase Order System For Home Builder

Real Estate Business Management