While searching the newsgroups for a better solution, I ran across some code published by Dev Ashish at the Access Web site run by Access MVPs. I searched the site for the keyword "ftp" and found a simple and elegant piece of code, which I have been using for years. You can see it for yourself, compliments of Dev, at the Access Web MVP site ...

Over the years, I have expanded that simple code and recently I incorporated it into one of my standard library modules. As I did so, I worked out some of the bugs and created an intuitive user interface. Thinking that maybe other Access developers may find this tool useful, I have provided the source code in the download for this article. Below is a brief explanation of the Demo Tool and the basics for the simplest method to execute FTP commands from Microsoft Access.

The Demo User Interface

The image below should give you an idea of how this tool works. You must first, of course, supply the address of an FTP server, along with login credentials and a start location, a target folder on the FTP server, which contains the files you seek.

For the sake of users who wish to test my code, I have created a login to an FTP server and the demo tool will default to these parameters. You can play with the app, test the functions and step through the code using my server, or point the tool to one of your own. I have not, however, made provision for anonymous login sites. After considering the code behind this demo, you will no doubt understand how you might implement such a login yourself, but since my clients have always required secure FTP access, it is not something that ever made it into my code.

Extending Dev Ahsish's code, I added the ability to read an FTP folder, grab the list of files with their size and create date, and load it into an Access table for consideration. Clicking the Read Folder Files button refreshes the sub datasheet with the current file list. (As more readers log on and upload files, the list of downloadable files will grow.)

Once the list of files has been loaded, you may select a file from the Download File combo box and click the Download button to initiate the code. Same for the Upload button, which allows you to upload any of the files exposed in the drop down list. (The combo box is populated with whatever files are found in the local folder. Startup code behind the form creates three files automatically upon opening the application.)

The bottom text area displays the simple FTP commands that carry out the request. It should be noted that typing the commands at a DOS prompt would accomplish the same result. The VBA code simply saves the required commands to a text file and pipes it to the ftp.exe windows application for processing.

The Code

I have simplified the following code somewhat from what you will see in the demo app. What you see below is the part that does the FTP work and is not concerned with minutia like testing to see if a file or folder exists and reporting success or failure to the user. The above UI handles those issues, but for the sake of this article, we want to focus on the code that executes the actual FTP commands.

The demo includes code to perform the following functions:

• List files on an FTP server
• Download a file
• Upload a file
• Delete a file

The only difference for any of these functions is the actual FTP commands used. To illustrate the process, we will focus on the download function. First, we create a text file named download.scr and populate it with the commands. Given the FTP server and login parameters above, and a target file named JustForKicks.txt, the scr file will contain the following lines of commands:

  open ftp.amazecreations.com
  DBJUser
  start123
  cd DBJ
  binary
  lcd "C:Access DemoDBJ"
  get "JustForKicks.txt" "C:Access DemoDBJJustForKicks.txt"
  bye

All we have to do is to create VBA code that generates that text file and then shells it out to ftp.exe for processing. The code below opens a file For Output and Prints the commands, one line at a time. When finished, the file is closed and a command is prepared to send to the ShellWait API method. The code behind this API call is beyond the scope of this article, but it is included with the demo application. ShellWait causes processing to halt until the FTP command is complete. This is extremely useful when you want Access to process the download before moving on to any other code, which may be predicated upon the existence of the file being downloaded.

Public Function DownloadFTPFile(ByVal sFile As String, _
                                sSVR As String, _
                                sFLD As String, _
                                sUID As String, _
                                sPWD As String) As String
   Dim sLocalFLD As String
   Dim sScrFile As String
   Dim sTarget As String
   Dim iFile As Integer
   Dim sExe As String
   Const q As String = """"
   On Error GoTo Err_Handler
   sLocalFLD = CurrentProject.Path & "" & sFLD
   ' The scr file will contain the FTP commands
   ' (If it exists from previous run, delete it)
   sScrFile =
   sLocalFLD  & "download.scr"
   If Dir(sScrFile) <>  "" Then Kill sScrFile
   
   ' For a download, the Target is the destination file
   sTarget = q & sLocalFLD & "" & sFile & q
   sFile = q & sFile & q
   ' Open a new text file to hold the FTP script and load it 
   ' with the appropriate commands.  (Thanks Dev Ashish !!!)
   ' ///////////////////////////////////////////////////////
   iFile = FreeFile
   Open sScrFile For Output As iFile
   Print #iFile, "open " & sSVR
   Print #iFile, sUID
   Print #iFile, sPWD
   Print #iFile, "cd " & sFLD
   Print #iFile, "binary"
   Print #iFile, "lcd " & q & sLocalFLD & q
   Print #iFile, "get " & sFile & " " & sTarget
   Print #iFile, "bye"
   Close #iFile
   sExe = Environ$("COMSPEC")
   sExe = Left$(sExe, Len(sExe) - Len(Dir(sExe)))
   sExe = sExe & "ftp.exe -s:" & q & sScrFile & q
   ' The download contains the API functions, including ShellWait.
   ShellWait sExe, vbHide
   DoEvents
   ' ///////////////////////////////////////////////////////
Exit_Here:
   Exit Function
Err_Handler:
   MsgBox Err.Description, vbExclamation, "E R R O R"
   Resume Exit_Here
End Function

Conclusion

One important issue that revealed itself was the matter of Long File Names. I had originally tried using an API call to convert long file names to short names, but that screwed up the FTP commands. Ultimately, I realized that everything would work fine so long as I delimited all the file paths with double quotes. In the code above, I created a constant named "q" which is inserted everywhere a quote is required.

The hardest part of preparing this demo application was getting the FTP commands correct. Who uses DOS anymore and who remembers FTP commands? Well, I am sure those who work with it every day can pound them out the first time without errors, but I had to delve back into newsgroup archives to refresh my memory on how to accomplish anything with simple FTP. You may too, depending on how fancy you want to get.

The point is that the above code is just the vehicle through which the FTP commands are executed. Using it, along with whatever you need FTP to do, you can automate it using Access and VBA. This has proven very valuable to my clients and greatly extends the usefulness of Microsoft Access.