[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Microsoft Access 2010 Automate FTP

Posted on 2013-11-14
Medium Priority
Last Modified: 2014-01-01
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.
Question by:marlind605
  • 7
  • 6
  • 4
  • +4
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 39650910
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.
LVL 58
ID: 39650916
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.

LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 668 total points
ID: 39650924
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 85
ID: 39650927
Right - the FTP methods are simple. It's the "OK" button I'm not sure of ...

Author Comment

ID: 39651154
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.
LVL 58
ID: 39651214
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.

LVL 16

Expert Comment

ID: 39651226
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?
LVL 58
ID: 39651260
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.

LVL 16

Expert Comment

ID: 39651263
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.

Author Comment

ID: 39651337
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?
LVL 58
ID: 39651380
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.

LVL 58
ID: 39651422
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.


Author Comment

ID: 39651434
Thanks Jim and Alex. I will find out more in a few minutes.

Author Comment

ID: 39664584
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.
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 664 total points
ID: 39664692
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:


Author Comment

ID: 39667827
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.
LVL 21
ID: 39670508
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?

Author Comment

ID: 39697136
I've requested that this question be deleted for the following reason:

I had to give the project to someone else.
LVL 14

Expert Comment

by:Bill Ross
ID: 39697133
See below.
LVL 14

Expert Comment

by:Bill Ross
ID: 39697137
Lots of good info here...  Perhaps should keep for reference rather than delete.

LVL 12

Expert Comment

ID: 39697152
Please don't delete this thread- it has useful code.
LVL 21
ID: 39697477

Author Comment

ID: 39698036
Does anyone have anything they can test it with to see what works?
LVL 21
ID: 39734175
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 .

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

872 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