VBA to download files from FTP site

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.

Who is Participating?
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 "ftp.mydomain.com" /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.  
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.