Updating a Website from Access using VBA and FTP

I have a few personal databases that I’ve developed in which I export files and upload to my website.  When I was using my ISP’s free hosting this was never an issue as I could just select the FTP site saved in My Computer.  Well time has past and my hosting needs changed and I wound up with GoDaddy hosting.  I was soon faced with the reality that a real hosting service was quite different from what I was accustomed to with my free ISP’s web space.  My major problem was my databases were no longer automatically saving the exported files to the web server.  I need a new way of updating a website from Access using VBA and FTP.

My first route tried to use Windows ftp.exe, but that does not play nice when passive (PASV) file transfer is required as is the case with GoDaddy.

I wanted to be able to click one button within my database to sent the updates to the website.  In this case there were 4 .xml files.  I previously had the code export the xml files to a temp directory so I just needed to find a solution that I could automate the upload process within Access using VBA.  Thanks to my dear friend, Google, I found NcFTP, a command-line FTP utility, which can be automated in the same way as Windows’ ftp.exe.  In my case this worked perfectly with my database and GoDaddy hosting.

The first step is to download and install the NcFTP Client (direct download)

Then head back to Microsoft Access and modify your VBA code.  My code begins by exporting the 4 .xml files to a temp directory.  I had a choice, I could tell NcFTP to upload each file or upload all files in a directory.  I choose the later as I wanted my entire website’s data to updated each time there is a change in the database.

Here is the VBA code I used:

Shell “ncftpput -u [USERNAME] -p [PASSWORD] [FTP.SITENAME.COM] [RemoteDestinationFolder] [LocalFile2Upload]”, vbNormalFocus

I included vbNormalFocus to show the NcFTP window during the upload process.  This can be hidden, but I prefer to see it working.

For the Local file to upload you can specify one file ‘C:\db_temp\filename.xml or a an entire directory C:\db_temp\*

Here is an example of how the command line would look if you wanted to upload all files in C:\db_temp to ftp.sitename.com to the remote directory sitename.com/data/

ncftpput -u USERNAME -p PASSWORD FTP.SITENAME.COM data/ C:\db_temp\*

There are a few way to implement this code.  For my database I created a table to store my site information less the password.  When the Update website button is clicked the files are exported, the command line is generated and the user is promted from any site credentials not stored in the table.  The files are uploaded from the temp directory.  Finally, the local temp files are deleted.