Using FTP from Access Applications

We live in a “connected” world–almost every system has to exchange data with other systems. One of the oldest and most widespread communication protocols is FTP. In this article, Bogdan Zamfir shows how you can incorporate FTP access into your applications.

If you have an application that’s in use at more than one site, you know how difficult it can be to get data or version updates to the various sites. The answer for you may be three letters: FTP. FTP stands for File Transfer Protocol. Due to its simple and reliable design, users on very different computers and operating systems can use FTP to exchange files safely and efficiently. FTP allows a client application to connect to a server to upload or download files. The client application can be any software application that knows how to handle FTP–Access, for instance. For more information, see the sidebar, “FTP in Brief.”

Using FTP, your applications can download data files (text or binary) from an Internet site, and upload data files to a central site (again, sending either text or binary data). All of this is under the control of your code, thanks to a handy class module included in this month’s Download.

A typical FTP session works as follows:

  1. The client application connects to an FTP server and provides the username and password to authenticate.
  2. If authentication succeeds, the server logs the client on to a specific account, based on the username and password.
  3. The client application can then navigate through the folder structure of the FTP server, retrieve directory listings, upload and download files, create and remove folders, and rename or delete files. Some of those actions can be restricted, based on the rights of the accounts the user has logged into.
  4. The client application logs out from the server.

FTP is implemented on Windows platforms using the WinINET API. WinINET is available on all versions of Windows, starting with Win98/WinME and WinNT 4.0 (with Internet Explorer 3.0 or later installed). The API functions you might use in your FTP session are:

  • InternetOpen–Returns a handle to be used in subsequent calls.
  • InternetConnect–Starts an FTP session.
  • FTPGetCurrentDirectory–Returns the current directory name.
  • FTPSetCurrentDirectory–Changes the current directory.
  • FTPGetFile–Downloads a file from the FTP server.
  • FTPPutFile–Uploads a file to the FTP server.
  • FTPFindFirstFile and InternetFindNextFile–Used to enumerate files and directories on the FTP server.
  • FTPDeleteFile–Deletes a file on the FTP server.
  • FTPRenameFile–Renames a file on the FTP server.
  • FTPCreateDirectory–Creates a new directory on the FTP server.
  • FTPRemoveDirectory–Removes a directory from the FTP server.
  • InternetCloseHandle–Closes the handle opened in InternetOpen.

If this is looking like a lot to learn, don’t panic! I’ve taken care of most of the work for you.

An FTP class

Rather than fight with these commands directly, I’ve created an easy-to-use FTP class. The class’s properties are:

  • Server–The name of the FTP server to connect to.
  • Username–The default value is anonymous.
  • Password–The password used to authenticate on the FTP server. The default is empty.

The Server, Username, and Password properties are Read/Write when the FTP object isn’t connected to the FTP server and Read-only when connected. The values can either be set before calling the Connect method, or passed as parameters to the Connect method. If they’re passed to the Connect method, the value can be read from the property after calling the Connect method.

  • Port–The port number for the FTP connection. The default value is 21.
  • PASV–If set to True, PASV mode is used.

Port and PASV are Read/Write when the FTP object isn’t connected to the FTP server and Read-only when connected. They must be set before calling the Connect method.

  • PathSeparator–When not connected, returns an empty string; when connected, returns the character used as the path separator for the FTP server the application is connected to. Most FTP servers use / (forward slash) as the path separator. However, there might be some FTP servers running on the Windows platform that use \ (backslash) as the path separator. Read-only.
  • IsConnected–True if the class is connected to an FTP session; False otherwise. Read-only.
  • TransferType–Type of data transfer used when uploading or downloading files. This can be ASCII (used to transfer text files), binary, or image (both used to transfer binary files). This property must be set to one of the values TransferASCII, TransferBINARY, or TransferIMAGE, defined as constants of the FTP class (Read/Write properties).

I’ll introduce the class’s methods by working through a typical scenario. All of the methods return True if they succeed, False if they don’t. If a method fails because of an internal error, you can call the GetLastErrorNumber method to retrieve the number of the error that caused the previous method to fail and GetLastErrorMessage to get the matching error message. Some functions can fail because the requested operation isn’t allowed on the FTP server. For example, if an application connects to an FTP server using the anonymous account and tries to upload a file, the upload can fail because the anonymous account doesn’t allow uploading files. Even on authenticated accounts, some operations can be restricted (like creating directories, deleting files, and so on). The GetLastInternetMessage method can be used to diagnose these errors. For instance, the message that you’ll get when you’re not allowed to create a new folder will be:

Cannot create directory [MyOwnFolder]

550 Permission denied.

Getting started

The Connect method connects to an FTP server. The Connect method accepts three parameters: the name of the FTP server, a username, and a password. If some of its parameters are missing, Connect uses values stored in the appropriate properties. The only required parameter is Server (if the Username is missing, Connect uses anonymous). To close the FTP session, you call the Disconnect method, which accepts no parameters.

FTP servers allow client applications to enumerate files in directories. My class supports this through the ListDirectory method, which accepts three parameters:

  • oFiles–A collection that will contain all of the files retrieved from the FTP directory. Required.
  • cSkel–A wildcard-supporting skeleton of files to be retrieved (that is, this parameter can contain the characters * or ?). Optional.
  • cFlags–Attributes of the files to be retrieved. Setting this parameter to Empty means normal files will be returned, R means Read-only files, and D means Directories. File attributes can be combined in any way. The default value, RD, means to retrieve all files and directories. Optional and read-only.

The oFiles collection will be populated with bzFile objects. The bzFile class has only three public properties: filename, attribute, and filesize (in bytes). File attribute has the same as attributes as those passed to the cFlags parameter (DR).

The directory listing on some FTP servers doesn’t include double dots (..) for the parent folder. However, it’s always included in my oFiles collection.

Working with directories

The FTP class allows several operations to be performed on directories on the FTP server. Directories can be created or removed, the current directory can be changed, and the current default directory name can be retrieved.

  • CreateDirectory–Creates a new directory on the FTP server. The function can fail if the server doesn’t allow creation of directories.
  • RemoveDirectory–Removes a directory on the FTP server. The function can fail if the server doesn’t allow removal of directories, or if the specified directory isn’t empty.
  • SetCurrentDirectory–Changes the current directory. This method can fail if the directory doesn’t exist or if access to that directory is restricted.
  • GetCurrentDirectory–Returns the name of the current directory.

The first three methods accept a single parameter, the name of the directory.

Working with files

This is the real meat of the class. The GetFile method allows you to download a file from the FTP server. It can receive up to three parameters:

  • cRemoteName–The name of the remote file to download. Required.
  • Overwrite–If True, tells the class to overwrite any local file with the same name as the remote file when downloading. If set to False (the default) and a local file with the same name already exists, the download will fail. Optional.
  • cLocalName–The local name for the downloaded file to be saved to. If missing, the file is downloaded using the same name as the remote name to the current directory (usually the directory with the Access MDB file). The transfer mode for downloading the file is set using the TransferType property. Optional.

The PutFile function allows you to upload a file to an FTP server. The method accepts two parameters:

  • cLocalName–The name of the local file to upload. Required.
  • cRemoteName–The name to give the uploaded file at the server. If missing, the file is uploaded using the same name as the local name. Optional.

Besides uploading and downloading files, my FTP class allows you to delete and rename files on the server using the DeleteFile and RenameFile methods.

Using bzFTP

To demonstrate the class in action, the Download file for this article contains a simple, yet full-featured FTP client application built around my bzFTP class.

The interface of the application is shown in Figure 1. On the top band, there are boxes to let you enter a server name, port, username, and password. Below, there’s a multi-line textbox that displays the answer received from the FTP server after processing commands. There are two listboxes on the form, used to display the local current directory (on the left side) and the remote FTP current directory (on the right side).

Figure 1

The user can select files from the local directory list and upload them to the FTP server by clicking the Upload button (the right arrow button). To download files, the user selects the file from the FTP directory list and clicks the Download button (the left arrow button).

Using the bzFTP class is very simple. In the FTP client form, I declared a variable to work with the bzFTP class:

Dim oFTP as bzFTP

The oFTP object is created in the Form_load method:

Private Sub Form_Load()

Set oFtp = New bzFtp

End Sub

When the user clicks the Connect button, the application tries to connect to the FTP server and, if successful, retrieves the directory listing for the current remote folder. Much of the following code isn’t related directly to using FTP (my class takes care of that). Instead, most of the code handles making sure that a username and password have been entered, checking to see whether the client is already connected, loading the listbox, and other user-interface management tasks:

Private Sub cmdConnect_Click()

' if not connected

If Me.cmdConnect.Caption = "Connect" Then

If Nz(Me.txtServer, "") = "" Then

MsgBox "Invalid server name", vbCritical


Exit Sub

End If

Me.txtExtraMsg = "Connecting …"


If Me.txtPort <> 21 Then

oFtp.Port = Me.txtPort

End If

oFtp.PASV = Me.ckPasv

If Not oFtp.Connect(Me.txtServer, Me.txtUser, _

Nz(Me.txtPassword, "")) Then

Me.txtExtraMsg = "Failed to connect to " & _

Me.txtServer & vbCrLf & _



' config form as connected

Me.txtExtraMsg = oFtp.GetLastInternetMessage

Me.lblConnect.Caption = "Connected to " & _

Me.txtServer & " as " & Me.txtUser

Me.cmdConnect.Caption = "Disconnect"

' …..

' and load remote dir into remote dir listbox


End If


' if connected, disconnect from FTP server


' config form as disconnected

Me.lblConnect.Caption = "Disconnected"

Me.cmdConnect.Caption = "Connect"

' clean remote dir listbox

LoadRemoteDir true

End If

End Sub

After the user selects one or more files in the local directory and clicks the Upload button, the application calls the UploadSelected function to upload files onto the FTP server:

Function UploadSelected() As Boolean

Dim i As Integer, cFileName As String

' for all entries in local dir listbox

For i = 1 To Me.lstLocalDir.ListCount

' if selected and not directory

If Me.lstLocalDir.Selected(i) And _

Me.lstLocalDir.Column(1, i) <> bzftpDir Then

cFileName = Me.lstLocalDir.Column(0, i)

Me.txtExtraMsg = "Uploading " & cFileName & _

"..." & vbCrLf


' try to upload selected file

If Not oFtp.PutFile(cFileName) Then

Me.txtExtraMsg = "Uploading " & cFileName & _

" failed" & vbCrLf & _



Me.txtExtraMsg = "Uploading " & cFileName & _

" OK" & vbCrLf & oFtp.GetLastInternetMessage

End If

' unselect the uploaded file

Me.lstLocalDir.Selected(i) = False

End If


' after uploading, reload remote dir



Me.cmdUpload.Enabled = False

End Function

The function that downloads the selected files works the same way as UploadSelected, except it reads from the right-hand listbox and uses the GetFile method. Also, it uses the checkbox option, which determines whether overwriting an already existing local file is allowed. I’ll omit that code here, but you can find it in the Download file.

To change the FTP remote directory, the user has to double-click on a directory entry in the remote directory list. That calls this function:

Sub lstRemoteDir_DblClick(Cancel As Integer)

If Me.lstRemoteDir.Column(1, _

Me.lstRemoteDir.ListIndex + 1) = bzftpDir Then

If oFtp.SetCurrentDirectory(Me.lstRemoteDir.Column(_

0, Me.lstRemoteDir.ListIndex + 1)) Then

Me.txtExtraMsg = oFtp.GetLastInternetMessage



Me.txtExtraMsg = "Cannot change directory to [" _

& Me.lstRemoteDir.Column(0, _

Me.lstRemoteDir.ListIndex + 1) & "]" & _

vbCrLf & oFtp.GetLastInternetMessage

End If

End If

End Sub

The function that creates that remote directory uses a table to store the names of the files on the server. I use this table to populate the remote directory listbox. The function can receive an optional parameter, CleanOnly, which (when True) cleans up the remote directory listbox when the application disconnects from the FTP server:

Sub LoadRemoteDir(_

Optional ByVal CleanOnly As Boolean = False)

Dim oFTPFiles As Collection, oFile As bzFile

Me.txtRemoteDir = oFtp.GetCurrentDirectory

Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb

db.Execute "delete from bzRemoteDir"

If CleanOnly Then

Me.txtRemoteDir = ""


Exit Sub

End If

If Not oFtp.IsConnected Then

Set db = Nothing

Exit Sub

End If

If oFtp.ListDirectory(oFTPFiles) Then

If Not oFTPFiles Is Nothing Then

Set rs = db.OpenRecordset("bzRemoteDir")

For Each oFile In oFTPFiles

If oFile.FileName <> "." Then


rs!FileName = oFile.FileName

If InStr(oFile.Attributes, "D") > 0 Then

rs!Size = bzftpDir


rs!Size = Right(Space(12) & _

Trim(oFile.Size), 12)

End If


End If



Set rs = Nothing

End If

End If

Set db = Nothing

' display the message received from FTP server

' and refresh remote dir list

Me.txtExtraMsg = oFtp.GetLastInternetMessage


End Sub

One warning: My class module uses synchronous transfer mode. This means that when you call a method in my class module, the method doesn’t return control to you until it’s finished its job. This is fine for short operations like retrieving a directory listing, working with directories, and transferring small files. However, it can be a problem when uploading or downloading big files because the application seems to be locked while the transfer is in progress.

While there are many third-party libraries and ActiveX controls that implement the FTP protocol, there are several benefits to using my class library. My code uses the WinINET API as the foundation for implementing FTP, an API that’s available on virtually all Windows platforms. The application is self-containing; it doesn’t rely on external components, which, if not properly installed, can prevent an application from running. There are no potential problems related to setting up references, which can be a problem when using COM or ActiveX components. You’re ready to start moving data around.

Sidebar: FTP in Brief

Most FTP servers feature a special account, known as anonymous, that allows access to any public folder on the site. Usually, the anonymous account allows only read-only access. Some FTP servers allow a single session for a given user at a certain moment, some allow a limited number of active concurrent sessions, while others allow unlimited concurrent sessions for the same user. An anonymous account allows unlimited concurrent sessions, because it can be used by any number of users who want to connect to the server at the same time. These differences are important when deciding how to implement a client application. For instance, if you drop your connection to an FTP site that doesn’t support concurrent logins, the server won’t immediately release the session thread. Because of this, if you try to reconnect immediately to the FTP server, the connection is refused because the server thinks your account is still connected. In these situations, it’s essential that you quit the session before you drop your connection.

FTP can be used in two modes–normal or PASV. In normal mode, when an FTP session is started, a connection is opened between the client and the FTP server (typically on TCP port 21). This is called the control connection. This connection is used to send commands to the FTP server and to receive responses but not to move any actual data. When the client application tries to upload or download a file to the FTP server, another connection is opened between the server and the client, initiated by the server (typically on TCP port 20). This is the data connection, and it’s used to transfer the file between the client and the server.

Since firewalls block several TCP ports (including port 20), normal mode can’t be used on a site behind a firewall. It’s possible to configure a firewall to allow access on this port, but this creates a security breach. To handle this, the FTP protocol was enhanced to allow PASV mode. When the connection is opened in PASV mode, the server doesn’t try to open a data connection to the client application on port 20, but instead lets the client application open a data connection back to the server on an arbitrary port chosen by the server. The server sends the port number to the client as the answer to the PASV command (hence the name of this mode). For more information about FTP, read FTP RFC 959 at


Other Pages You May Wish to Read

This entry was posted in VBA. Bookmark the permalink.

One Response to "Using FTP from Access Applications"

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.