VBA to download files from FTP site

Posted on 2011-05-03
Last Modified: 2012-05-11
I need to automate the following process in Access VBA:

1: connect to an ftp site with username and password

2: download all the files on the site to a folder on the network.

3: verify they were successfully downloaded.

4: delete all files from the ftp site that were downloaded or move them to an archive (just in case).

I need no user interaction on this feature as the database runs on a Windows 2003 server and automatically processes at 2AM in the morning.  I have to add this feature to the already existing items that are done.

Question by:sharpapproach
    LVL 16

    Accepted Solution

    Below is a Robo-FTP command script that should meet your requirments.  You could call Robo-FTP as a COM object from within your Access VBA or you could just set it up to run every morning at 2AM so it happens without being triggered by the VBA.  This logic only deletes files off the remote site if the download was successful.
    WORKINGDIR "\\MyNetworkServer\SharedFolder"
    LOGMSG "Can't access destination folder on network share."
    GOTO done
    FTPLOGON "" /user="UserID" /pw="secret"
    RCVFILE "*" /delete

    Open in new window

    Notice that I only put in one error handling branch but you could easily add others or make it send an email on failure instead of just writing to the log.  If you want to make it archive the remote files instead of deleting them on a successful download you can do that too, it just requires a few more lines of script because there is not a built-in archive-on-success logic for the download the way there is for the uploads.  
    LVL 84
    You've got several questions in one, and  you'd be far better off breaking this up into several smaller questions.

    Also: What code have you tried to do this? EE isn't really about someone providing you with a full working solution, but instead to give you direction on which way to go.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now