Chapter 36 Use FTP in VBA
Chapter 36 Use FTP in VBA
A
ccess applications are all about manipulating and presenting data to the user.
Often, this data is held on another server and sometimes you cannot directly
connect to that data.
An example might be files of stock exchange closing prices or foreign currency values.
Lack of connectivity may be for a variety of reasons. The data could belong to a third-party
organization that does not want you to connect to their network. Database owners in your own
organization could have security concerns if they allow you to connect directly. They could also
have concerns about the effect on performance of their own database if a number of outside
connections are suddenly being made and many time-consuming queries are being executed.
One way around all this is to transfer the data via a text or CSV file, which Access is
already well equipped to deal with. However, there is the question of how the file is
transferred to your application. It could be e-mailed to you, but this becomes difficult to
automate so that your VBA code picks it up.
Many organizations now use FTP (File Transfer Protocol) servers for this purpose. This
uses an Internet connection to link to the FTP server, normally employing a user ID and
password, and allows you to view and transfer the files on the server as if they were folders
on your own PC.
If you open Windows Explorer and type in the address of an FTP server, such as
ftp://MyOrg.MyServer.net, into the path window at the top of the screen (providing the
required user ID and password when prompted), you will see the available folders and files
for that user ID. You will find that you can easily drag these files into your local folders and,
if your user ID has permission, drag files from your local folders back to the FTP server.
You may not have an FTP server available to you to experiment on, but if you Google
“publicftp servers,” you will find many available that allow public access. When experimenting,
stick with sample text files that you have uploaded yourself. Anything that contains code coming
from a public source could be dangerous to your PC.
337