Downloading XML files into access
I use this function to download xml files from a ftp server and read the contents into an Access database.
This function deals with the FTP process. I use a licensed product component from chilkatsoft.com call chilkatftp.
Function DownloadFiles()
On Error GoTo ErrorHandler
Dim ftp As New ChilkatFtp2
Dim success As Integer
Dim n As Integer, i As Integer, rst As Recordset, fname As String
Dim tmpFTP, tmpUsername, tmpPassword, tmpRemote, tmpLocalFolder
Application.echo true, "Start FTP Download Check.." & Now()
tmpLocalFolder = "set your local folder here"
tmpFTP = "Enter you FTP Address"
tmpPassword = "Password"
tmpRemote = "Remote ftp folder"
tmpUsername = "Username"
If Right(tmpLocalFolder, 1) <> "" Then
If Right(tmpLocalFolder, 1) = "/" Then
tmpLocalFolder = Left(tmpLocalFolder, Len(tmpLocalFolder) - 1) & ""
Else
tmpLocalFolder = tmpLocalFolder & ""
End If
End If
' Any string unlocks the component for the 1st 30-days.
success = ftp.UnlockComponent("enter_your_unlock_code")
If (success <> 1) Then
Forms![frmFTPSettings]![txtProgress] = ftp.LastErrorText
Exit Function
End If
Call UpProgress("Connected to Site")
ftp.Hostname = tmpFTP
ftp.UserName = tmpUsername
ftp.Password = tmpPassword
' Connect and login to the FTP server.
success = ftp.Connect()
If (success <> 1) Then
Forms![frmFTPSettings]![txtProgress] = ftp.LastErrorText ' open form to display the error
Exit Function
End If
' Change to the remote directory where the files are located.
' This step is only necessary if the files are not in the root directory
' of the FTP account.
success = ftp.ChangeRemoteDir(tmpRemote)
If (success <> 1) Then
Forms![frmFTPSettings]![txtProgress] = ftp.LastErrorText
Exit Function
End If
ftp.ListPattern = "*.xml"
' NumFilesAndDirs contains the number of files and sub-directories
' matching the ListPattern in the current remote directory.
'
n = ftp.NumFilesAndDirs
If (n < 0) Then
Forms![frmFTPSettings]![txtProgress] = ftp.LastErrorText
Exit Function
End If
Application.echo true, n & " Files downloaded "
If (n > 0) Then
For i = 0 To n - 1
'
fname = ftp.GetFilename(i)
CurrentDb.Execute ("INSERT INTO tblFilesDownloaded ( FTP_FileDownloaded, FTP_Date, FTP_Processed ) SELECT " & Chr(34) & ftp.GetFilename(i) & Chr(34) & " AS Expr1," & "#" & Now() & "#" &" AS Expr2, 0 AS Expr3")
' Download the file into the current working directory.
success = ftp.GetFile(fname, tmpLocalFolder & fname)
If (success <> 1) Then
Forms![frmFTPSettings]![txtProgress] = ftp.LastErrorText
Exit Function
End If
' Now delete the file.
success = ftp.DeleteRemoteFile(fname)
If (success <> 1) Then
Forms![frmFTPSettings]![txtProgress] = ftp.LastErrorText
Exit Function
End If
'
Next
End If
'
ftp.Disconnect
'
Exit Function
ErrorHandler:
application.echo true, "FTP - An error occurred " & Err.Number & " " & Err.Description & " At:" & Now())
Resume Next
End Function