• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 984
  • Last Modified:

Microsoft Access 2010 Automate FTP

I need to be able to download from a website in the middle of the night, I will use task scheduler for this but I would like to be able to store the user name and password in the database and have it open the website and download the file. I have found some code in access but just haven't been able to get it to work Old program?? not sure. Can anyone help me with code for this using MS Access as much as possible or a step by step bat file that can be set to run in the middle of the night like from Access maybe?? The website requires a user name and password and requires you to select ok. However, It does allow you to save the password but not sure if that is the best solution. Thanks for any thoughts or code.
  • 7
  • 6
  • 4
  • +4
3 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The website requires a user name and password and requires you to select ok.
I don't think you can select the "OK" button via code. Do you have the URL of the website?

Have you contacted the website to see if they have alternate methods to manage this? Often a website that is designed for these sorts of things will provide you with a webservice you can connect with and download, without human intervention.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Understand that there are different "flavors" of FTP:   FTP, sFTP, and FTPs.

Depending on which the web site needs determines the method/client that you use to talk to it.

 What you want to do is entirely possible and I have one client now where we use FTP extensively.

There are two approaches to FTP in general:

1. Script out a batch file in VBA, then execute that, which calls a command line client.

2. Do the FTP directly yourself with something like the winet lib.

I like #1 myself.  It means I don't need to know all the details of a specific flavor of FTP and more imprtantly to me, I can manually execute the commands to test, then automate, which makes development of automation jobs far easier.

The FTP Clients I use are:

1. Microsoft client built into windows for normal FTP
2. MoveIt Freely client for sFTP
3. WinSCP client for FTPS

All these clients are free.

In a little bit, I'll post code for FTP download for one of the clients so you can see how this might be setup.

 In the meantime, you need to:

1. Find out what type of FTP the web site uses.
2. Get the client

 I would simply try the built-in Microsoft client and see if it works.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Here's a sample of a FTP download that is scripted.  This code won't run for you directly, but will give you a good idea on how this is done with a script (batch) file.


Function FTPDownloadFile(strLocalFileName As String, strFTPFilename As String, strFTPSiteName As String, strUserName As String, strPassword As String, Optional strTransferType As String) As Boolean

          ' Procedure to download a file from a FTP site.
          ' Sends e-mail to ITALERT if download fails and returns false.
          Const RoutineName = "FTPDownloadFile"
          Const Version = "1.4"

          Dim strFTPCommandFile As String
          Dim strFTPScriptFile As String
          Dim strFTPLogFile As String

          Dim lngHWnd As Long
          Dim intFileNum As Integer
          Dim strMailMessage As String
          Dim oOCS_SendMail As New OCS_SendMail

10        On Error GoTo FTPDownloadFile_Error

          ' Generate file names
20        strFTPScriptFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".txt"
30        strFTPCommandFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".bat"
40        strFTPLogFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".log"

          ' Write script file
50        intFileNum = FreeFile
60        Open strFTPScriptFile For Output As #intFileNum
70        Print #intFileNum, strUserName
80        Print #intFileNum, strPassword
90        Print #intFileNum, "Type " & IIf(strTransferType = "B", "binary", "ascii")
100       Print #intFileNum, "Get " & Chr$(34) & strFTPFilename & Chr$(34) & " " & Chr$(34) & strLocalFileName & Chr$(34)
110       Print #intFileNum, "quit"
120       Close #intFileNum

          ' Write command file
130       intFileNum = FreeFile
140       Open strFTPCommandFile For Output As #intFileNum
150       Print #intFileNum, "@ftp -i -s:" & strFTPScriptFile & " " & strFTPSiteName & " > " & strFTPLogFile
160       Close #intFileNum

          ' Execute
170       lngHWnd = Shell(strFTPCommandFile, vbHide)
180       WaitWhileRunning (lngHWnd)

          ' Check log file
190       If IsValidFTP(strFTPLogFile) Then
200           FTPDownloadFile = True
210       Else
220           If DebugMode() = True Then
230               Stop
240               FTPDownloadFile = False
250           Else
260               oOCS_SendMail.SetParams "ITALERT", ".", "."
270               oOCS_SendMail.Subject = "FTP download failed"
280               strMailMessage = "The file: " & strFTPFilename & " did not download." & vbCrLf
290               strMailMessage = strMailMessage & "Command, script, and log files are attached." & vbCrLf & vbCrLf
300               strMailMessage = strMailMessage & "App name:" & AppShortName() & " Version: " & AppVersion()
310               oOCS_SendMail.Message = strMailMessage
320               oOCS_SendMail.Attachment = strFTPCommandFile & ";" & strFTPScriptFile & ";" & strFTPLogFile
330               oOCS_SendMail.Send
340               FTPDownloadFile = False
350           End If
360       End If

370       On Error Resume Next

380       If Dir(strFTPCommandFile) <> "" Then Kill strFTPCommandFile
390       If Dir(strFTPScriptFile) <> "" Then Kill strFTPScriptFile
400       If Dir(strFTPLogFile) <> "" Then Kill strFTPLogFile

410       Close #intFileNum

420       Exit Function

430       UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
440       FTPDownloadFile = False
450       Resume FTPDownloadFile_Exit

End Function
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Right - the FTP methods are simple. It's the "OK" button I'm not sure of ...
marlind605Author Commented:
JDettman, I am not comfortable posting the website here but looked at your bio I would like to send it to you but didn't see a way I could do that. Is there a way to send you a message. There is a link to the webmaster on the site should I ask him if so what should I ask him. I have never don't script before but have wrote my own code in Access in VBA and can do anything I want to in Access but just have done any scripting but willing to try.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
What your looking for is the method of FTP transfer.  As I said, I would first simply try the download with the Microsoft FTP client.

First you need the address.   Go out to the web site, enter your username and password, click the button and look at the URL your being directed to.  Most often,
 it will be something like:


If that's the case, then also more often then not, you can go directly to the FTP server without using the web page.

You can then click, start, run, enter CMD and click OK.  You'll get a DOS command window.  Type in FTP and hit return.

Then type OPEN and the address you got above.  Should get prompted for your username and password.   At that point, assuming you get a login, you can use a DIR to see the files, then a GET to download a file.

These would be the commands the script would need.

So first job is to find out how to get at the file.

If you forward me the URL at jimdettman"at"online-computer-services.net, I'll tell you how you need to do it.

Usually when someone talks about logging into a web site they are talking about HTML pages delivered over HTTP(S) so what is the FTP angle?
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Web sites can transfer files by HTTP or FTP.  At the moment, it's hard to tell which he needs.   He mentions a login on a web page, and that probably means HTTP, but sometimes a web site will simply generate a unique URL based on the login which is nothing more then a FTP transfer (most browsers have a built-in FTP client).   Also the question title mentions FTP.

So at the moment, we don't really know which it is, and it could be either.

Jim is correct, if it is an FTP anchor link in a web page then you can bypass the the HTTP server and go directly to the FTP server.  One problem you might run into is that web browsers and modern FTP clients tend to default to passive mode for FTP transfers but the command line ftp.exe client can only do active mode... you'll know if this is a problem if you can connect and authenticate with ftp.exe but it hangs trying to transfer files.  This issue can be resolved by modifying the firewall or by using a modern scriptable FTP client like Robo-FTP, etc.
marlind605Author Commented:
it shows websitename.org/ftp. The title of the page shows Index of /ftp/ then it list all the files. Does that help or am I being to protective of the site?
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I think a little too pretective if you need a user name and password to sign-on.   But if you really don't want to post it, that's fine.  Just send it to me off-line.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
OK, I got the url off-line and checked and it's not reachable by FTP, so it looks like this is a HTTP file download through the web site.   But marlind605 is going to double check with the folks hosting the site and see what's available.

marlind605Author Commented:
Thanks Jim and Alex. I will find out more in a few minutes.
marlind605Author Commented:
Sorry it took me so long to get back to you. It looks like what I see is what I get no separate FTP sight is available. Looks like I can construct a link using www.mylink.com/AA131911.ZIP for yesterday. So can how can I set up Access to open that website at 1:00 (Task Scheduler maybe?) and I think it will let me save it by just getting that link to the browser. I am kind of lost right now.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
If yu now the direct path the the file you can download it using this:

From Download a file from a URL

'--- place this code in a standard module ---'
Option Compare Database
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" _
     Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
     ByVal szURL As String, ByVal szFileName As String, _
     ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Public Sub bimDownloadURLtoFile(pURL As String, _
     pFullFilePath As String)
   Call URLDownloadToFile(0, pURL, pFullFilePath, 0, 0)
End Sub
''' end code ''' 

Open in new window

It may be possible to pass the username and password on the URL like this:

marlind605Author Commented:
Thanks thehitechcoach this didn't work for me but I did try this Dim DN
    Set DN = CreateObject("internetexplorer.application")
        DN.navigate "https://www.notrealweb.com/ftp/SQB_DSIM/CR131120.ZIP"
        DN.Visible = True
     I was able to get internet explorer to open but it wants me to click on yes or no to a potential security concern. It also wants me to click on save. After i log into to the site in Chrome and ask it to remember me I am able to just copy the url and paste it in the browser and it saves without me clicking okay password.
1.  How do I get rid of the "Potential Security Concern"
2.  Can I change my code to Chrome and get it to work.
Thanks for putting up with me on this question.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Curious, what did not work?

Using my code, will it  download the url:  



Call bimDownloadURLtoFile("https://www.notrealweb.com/ftp/SQB_DSIM/CR131120.ZIP", "C:\myfolder\CR131120.ZIP")

Open in new window



replacing username:password with the correct username and password

Do you get an error message?
marlind605Author Commented:
I've requested that this question be deleted for the following reason:

I had to give the project to someone else.
Bill RossCommented:
See below.
Bill RossCommented:
Lots of good info here...  Perhaps should keep for reference rather than delete.

Please don't delete this thread- it has useful code.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
marlind605Author Commented:
Does anyone have anything they can test it with to see what works?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You might consider using:

AutoHotkey - Free Mouse and Keyboard Macro Program
AutoHotkey is a free, open-source utility for Windows. With it, you can:* Automate almost anything by sending keystrokes and mouse clicks. You can write a mouse or keyboard macro by hand or use the macro .
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now