Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

FTP in Access

From Wiki

Jump to: navigation, search

One thing that drove me crazy in Access for quite a while was automating FTP uploads/downloads. I inherited a lot of old apps that actually would construct an FTP script on the fly, and execute this script through the shell. Needless to say, this was not very reliable, and made it difficult for users at times (because errors tended to pop up in odd places farther down in the process).

When I stumbled upon the Wininet.dll, I did not know how much easier it would make this. While this is nowhere near as good (IMO) as some of the managed FTP libraries available in .net, I have found using this library far superior to the old way we were doing things. The original sample function I found did not provide everything I needed (It was download-only, with extras like a status bar that I did not need), but I have kept its Declaration section and ShowError sub, primarily because of that old "If it ain't broke" thing.

Anyways, you can build the module as follows (I call mine WininetFTPFunctions):


Declaration section:

vbLine number Off | Hide | Select all
  1. Option Explicit
  2.      
  3.     ' Set Constants
  4.     Const FTP_TRANSFER_TYPE_ASCII = &H1
  5.     Const FTP_TRANSFER_TYPE_BINARY = &H2
  6.     Const INTERNET_DEFAULT_FTP_PORT = 21
  7.     Const INTERNET_SERVICE_FTP = 1
  8.     Const INTERNET_FLAG_PASSIVE = &H8000000
  9.     Const PassiveConnection As Boolean = True
  10.      
  11.     ' Declare wininet.dll API Functions
  12.     Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
  13.         (ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean
  14.      
  15.     Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" _
  16.        (ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String, lpdwCurrentDirectory As Long) As Boolean
  17.      
  18.     Public Declare Function InternetWriteFile Lib "wininet.dll" _
  19.     (ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As Long, _
  20.     dwNumberOfBytesWritten As Long) As Integer
  21.      
  22.     Public Declare Function FtpOpenFile Lib "wininet.dll" Alias "FtpOpenFileA" _
  23.     (ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long, ByVal Flags As Long, ByVal Context As Long) As Long
  24.      
  25.     Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
  26.     (ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
  27.           ByVal lpszRemoteFile As String, _
  28.           ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean
  29.      
  30.     Public Declare Function FtpDeleteFile Lib "wininet.dll" _
  31.         Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
  32.         ByVal lpszFileName As String) As Boolean
  33.     Public Declare Function InternetCloseHandle Lib "wininet.dll" _
  34.     (ByVal hInet As Long) As Long
  35.      
  36.     Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
  37.     (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _
  38.     ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
  39.      
  40.     Public Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
  41.     (ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, _
  42.     ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, _
  43.     ByVal lFlags As Long, ByVal lContext As Long) As Long
  44.      
  45.      
  46.     Public Declare Function FTPGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
  47.     (ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
  48.           ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal dwFlagsAndAttributes As Long, _
  49.           ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean
  50.      
  51.     Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
  52.           Alias "InternetGetLastResponseInfoA" _
  53.            (ByRef lpdwError As Long, _
  54.            ByVal lpszErrorBuffer As String, _
  55.            ByRef lpdwErrorBufferLength As Long) As Boolean

Download Function:

vbLine number Off | Hide | Select all
  1. Function FTPDownFile(ByVal HostName As String, _
  2.         ByVal UserName As String, _
  3.         ByVal Password As String, _
  4.         ByVal LocalFileName As String, _
  5.         ByVal RemoteFileName As String, _
  6.         ByVal sDir As String, _
  7.         ByVal sMode As String) As Boolean
  8.      
  9.      
  10.     ' Declare variables
  11.     Dim hConnection, hOpen ' Used For Handles
  12.     Dim fso As Object
  13.      
  14.     'check for file existence, delete if necessary
  15.     Set fso = CreateObject("Scripting.FileSystemObject")
  16.      
  17.     If fso.FileExists(LocalFileName) Then
  18.      
  19.     VBA.Kill LocalFileName
  20.      
  21.     Set fso = Nothing
  22.      
  23.     End If
  24.      
  25.     ' Open Internet Connecion
  26.     hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)
  27.      
  28.     ' Connect to FTP
  29.     hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)
  30.      
  31.     ' Change Directory
  32.     Call FtpSetCurrentDirectory(hConnection, sDir)
  33.      
  34.     ' Set Download Flag to True
  35.     FTPDownFile = True
  36.      
  37.      
  38.      
  39.         ' Download File
  40.         If FTPGetFile(hConnection, RemoteFileName, LocalFileName, False, 1, 0, 1) = False Then
  41.             FTPDownFile = False
  42.         End If
  43.      
  44.      
  45.     SetAttr LocalFileName, vbNormal + vbArchive
  46.      
  47.      
  48.     ' Close Internet Connection
  49.     Call InternetCloseHandle(hOpen)
  50.     Call InternetCloseHandle(hConnection)
  51.      
  52.     End Function

Upload Function:

vbLine number Off | Hide | Select all
  1. Function FTPUploadFile(ByVal HostName As String, _
  2.         ByVal UserName As String, _
  3.         ByVal Password As String, _
  4.         ByVal LocalFileName As String, _
  5.         ByVal RemoteFileName As String, _
  6.         ByVal sDir As String, _
  7.         ByVal sMode As String) As Boolean
  8.      
  9.     'If this function is not working, try playing with Flags and Context
  10.     '(2nd and 3rd parameters in FTPPutFile
  11.      
  12.     ' Declare variables
  13.     Dim hConnection, hOpen ' Used For Handles
  14.      
  15.     ' Open Internet Connecion
  16.     hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)
  17.      
  18.     ' Connect to FTP
  19.     hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)
  20.      
  21.      
  22.     ' Change Directory
  23.     Call FtpSetCurrentDirectory(hConnection, sDir)
  24.      
  25.     ' Set Download Flag to True
  26.     FTPUploadFile = True
  27.      
  28.      
  29.      
  30.         ' Download File
  31.         If FtpPutFile(hConnection, LocalFileName, RemoteFileName, 0, 1) = False Then
  32.             FTPUploadFile = False
  33.      
  34.         End If
  35.      
  36.     'Debug.Print LocalFileName
  37.     SetAttr LocalFileName, vbNormal + vbArchive
  38.      
  39.      
  40.     ' Close Internet Connection
  41.     Call InternetCloseHandle(hOpen)
  42.     Call InternetCloseHandle(hConnection)
  43.      
  44.     End Function

Error Notification (This can be used when you wish to deliver a more detailed error message, for the most part I do not use it, and just notify the user that file download/upload failed by looking at the function's return value):

vbLine number Off | Hide | Select all
  1. Sub ShowError()
  2.        Dim lErr As Long, sErr As String, lenBuf As Long
  3.        'get the required buffer size
  4.        InternetGetLastResponseInfo lErr, sErr, lenBuf
  5.        'create a buffer
  6.        sErr = String(lenBuf, 0)
  7.        'retrieve the last respons info
  8.        InternetGetLastResponseInfo lErr, sErr, lenBuf
  9.        'show the last response info
  10.        MsgBox "Last Server Response : " + sErr, vbOKOnly + vbCritical
  11.     End Sub


Finally, an example to call the function (from your form or another module, not the module you created to hold these functions):

vbLine number Off | Hide | Select all
  1. 'Attempt to Upload file
  2.     If WininetFTPFunctions.FTPDownFile("ftp.domain.com", "myUserName", "myPassword", "Full path and Filename of local file", "Target Filename without path", "Directory on FTP server", "Upload Mode - Binary (&H2) or ASCII (&H1)") Then
  3.     'notify user of completion
  4.     MsgBox "Upload - Complete!"
  5.     'or failure
  6.     MsgBox "Upload - Failed!"
  7.     End If

I hope this helps. It certainly has been invaluable to me.

Contributed by: AlexCuse

91 Rating: 3.0/5 (4 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/FTP_in_Access"

Powered by MediaWiki GNU Free Documentation License 1.2
Valid XHTML | Valid CSS