FTP from Microsoft Access

This sample code demonstrated how to upload a file to a web server using Window’s FTP command.  A developer can easily create their own ftp script to perform a variety of tasks for transferring files to and from a web server.  Some scenarios where I have used FTP in my custom Access application include:

• Updating the back-end data of a web application.
• Downloading data from a web applications (for example: new orders in a shopping cart)
• Uploading user FE/BE and other files to my website (many times the files are too larger for email).

Click here – to view a web application which is updated by this FTP function.  In the searchable online directory, an asp file interfaces with an Access database.  The data in the online database search is updated though an internal custom Access application. I created a function to export a query to an mdb file and then FTP it to the web server.  A user simply clicks a command button anytime they want to update the website.

Function FTP_Data()
'=========================================================================
'FTP from Microsoft Access
'by Matthew V Carmichael
'Craetes FTP Batch File, FTP command file (txt)
'Default directory location of files to upload/download is
'the same location as the mdb file that contains this module.
'========================================================================='
On Error GoTo Err_Trap
 
    Dim pFile As Long
    Dim strPath As String
    Dim strFileName As String
    Dim ftpServer As String
    Dim strUserName As String
    Dim strPassword As String
    
    'Path and Name of file to FTP
    strPath = CurrentProject.Path & "\"
    strFileName = "Test.mdb" 'Name of file to upload
    'FTP Server Settings
    ftpServer = "Your FTP Server"
    strUserName = "Your FTP User Name"
    strPassword = "Your FTP Password"
    
    'Create text file containing FTP commands
    pFile = FreeFile
    Open strPath & "FTP_cmd.txt" For Output As pFile
    Print #pFile, "user"
    Print #pFile, strUserName
    Print #pFile, strPassword
    Print #pFile, "Put " & strFileName
    'Use the Put command to upload, use the Get command to download.
    'Print #pFile, "Get " & "Your File Name"
    Print #pFile, "quit"
    Close pFile
    
    'Create batch file to execute FTP
    pFile = FreeFile
    Open strPath & "FTP_Run.bat" For Output As pFile
    Print #pFile, "ftp -n -s:" & "FTP_cmd.txt " & ftpServer
    Print #pFile, "Pause"
    Close pFile

    'Execute FTP command
    Shell strPath & "FTP_Run.bat", 1
    
Err_Trap_Exit:
    Exit Function
    
Err_Trap:
    MsgBox Err.Number & " - " & Err.Description
    Resume Err_Trap_Exit
 
End Function