How can I open a txt file from a FTP location using ACCES 1

[flavius005]

Hello,


I am trying to get a txt file from a FTP location, which I need to use in my application.
I don't know exactlly how I can do this using a mcaro or function...so if you have any ideea it would be very helpfull.

Thank you

 

[Remou]

Have you seen the FAQ?

http://www.tek-tips.com/faqs.cfm?fid=6533

http://lessthandot.com

 

[flavius005]

Yes, I've looked at this topic, but I don't understand exactlly how this function will be added in the event of the "Download" button.

If you have any sugestion for this...

Thank you

 

[HarleyQuinn]

Something like:

Private Sub cmdDownload_Click()
Dim blnSuccess As Boolean

' True if successful, false if not.
' All values prefixed with Your need you to substitute the correct values for your sitaution in

blnSuccess = FTPDownloadFile("YourHostName", "YourUserName", "YourPassword", "YourLocalFileName", "YourRemoteFileName", "YoursDir", "YoursMode")


End Sub

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[flavius005]

Hi,

I've tried your sugestion...but it does not seem to work.
In the "Download" button , I've put the following event:


Private Sub Command27_Click()
Dim blnSuccess As Boolean

' True if successful, false if not.
' All values prefixed with Your need you to substitute the correct values for your sitaution in

blnSuccess = FTPDownloadFile("ftp://ftp.microsoft.com/deskapps/", "", "", "readme.txt", "readme.txt", "c:\", "Binary")


End Sub


- ("ftp://ftp.microsoft.com/deskapps/" -this is the ftp path

-the next two parameters are empty, because no user name or password is required
- "readme.txt" - name of the file to download from the ftp
-"readme.txt" - name of the file on my hard disk

- "c:\" path on my disk
- "Binary" transfer mode

...but nothing happens


I can mail the mdb, if you can take a look to it.

Thank you

 

[HarleyQuinn]

No need to mail me the mdb, it's the paramters passed to the function that are causing the problem. Try using this:

blnSuccess = FTPDownloadFile("ftp.microsoft.com", vbNullString, vbNullString, "C:\readme.txt", "readme.txt", "/deskapps", "Binary")

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[flavius005]

I changed it...nothing happens...
When I copy manually the readme.txt from the ftp.microsoft.com/deskapps server (just for testing), it works.

But when I push the download button from the acces 2003 application nothing happens. no error, no message, the file not copied. I don't understand

 

[HarleyQuinn]

And blnSuccess returns as true?

Step through the function, make sure the values returned into hOpen and hConnection aren't 0 (though the function should cause a msgbox to be displayed and return a false value).

If that doesn't throw up anything significant can you post the exact code you have in the FTPDownloadFile function and the exact code you have in the call to the function.

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[flavius005]

It does not throw out anything...

I will put next the code:

Option Compare Database
Option Explicit

' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String, lpdwCurrentDirectory As Long) As Boolean

Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As Long, _
dwNumberOfBytesWritten As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias "FtpOpenFileA" _
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long, ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Public Declare Function FtpDeleteFile Lib "wininet.dll" _
Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long


Public Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
Alias "InternetGetLastResponseInfoA" _
(ByRef lpdwError As Long, _
ByVal lpszErrorBuffer As String, _
ByRef lpdwErrorBufferLength As Long) As Boolean



Function FTPFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean

On Error GoTo Err_Function

' Declare variables
Dim hConnection, hOpen, hFile As Long ' Used For Handles
Dim iSize As Long ' Size of file for upload
Dim Retval As Variant ' Used for progress meter
Dim iWritten As Long ' Used by InternetWriteFile to report bytes uploaded
Dim iLoop As Long ' Loop for uploading chuncks
Dim iFile As Integer ' Used for Local file handle
Dim FileData(BUFFER_SIZE - 1) As Byte ' buffer array of BUFFER_SIZE (100) elements 0 to 99

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Open Remote File
hFile = FtpOpenFile(hConnection, RemoteFileName, GENERIC_WRITE, IIf(sMode = "Binary", FTP_TRANSFER_TYPE_BINARY, FTP_TRANSFER_TYPE_ASCII), 0)

' Check for successfull file handle
If hFile = 0 Then
MsgBox "Internet - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If

' Set Upload Flag to True
FTPFile = True

' Get next file handle number
iFile = FreeFile

' Open local file
Open LocalFileName For Binary Access Read As iFile

' Set file size
iSize = LOF(iFile)

' Iinitialise progress meter
Retval = SysCmd(acSysCmdInitMeter, "Uploading File (" & RemoteFileName & ")", iSize / 1000)

' Loop file size
For iLoop = 1 To iSize \ BUFFER_SIZE

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, (BUFFER_SIZE * iLoop) / 1000)

'Get file data
Get iFile, , FileData

' Write chunk to FTP checking for success
If InternetWriteFile(hFile, FileData(0), BUFFER_SIZE, iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten <> BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Next iLoop

' Handle remainder using MOD

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, iSize / 1000)

' Get file data
Get iFile, , FileData

' Write remainder to FTP checking for success
If InternetWriteFile(hFile, FileData(0), iSize Mod BUFFER_SIZE, iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten <> iSize Mod BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Exit_Function:

' remove progress meter
Retval = SysCmd(acSysCmdRemoveMeter)

'close remote file
Call InternetCloseHandle(hFile)

'close local file
Close iFile

' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

Exit Function

Err_Function:
MsgBox "Error in FTPFile : " & Err.Description
GoTo Exit_Function

End Function

Sub ShowError()
Dim lErr As Long, sErr As String, lenBuf As Long
'get the required buffer size
InternetGetLastResponseInfo lErr, sErr, lenBuf
'create a buffer


Function FTPDownloadFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean


' Declare variables
Dim hConnection, hOpen ' Used For Handles
Dim fso As Object


Set fso = CreateObject("Scripting.FileSystemObject")
'check for existence of destination file
If fso.FileExists(LocalFileName) Then

'delete it if found
VBA.Kill LocalFileName

End If

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Set Download Flag to True
FTPDownloadFile = True

' Download File
If FtpGetFile(hConnection, RemoteFileName, LocalFileName, False, 1, 0, 1) = False Then
MsgBox "Download - Failed At FTPGetFile!"
FTPDownloadFile = False

End If

'ensure file is not saved as read-only
SetAttr LocalFileName, vbNormal + vbArchive


' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

End Function


sErr = String(lenBuf, 0)
'retrieve the last respons info
InternetGetLastResponseInfo lErr, sErr, lenBuf
'show the last response info
MsgBox "Last Server Response : " + sErr, vbOKOnly + vbCritical
End Function


' HERE IS THE CODE FOR THE FTPDownloadFile function

Function FTPDownloadFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean


' Declare variables
Dim hConnection, hOpen ' Used For Handles
Dim fso As Object


Set fso = CreateObject("Scripting.FileSystemObject")
'check for existence of destination file
If fso.FileExists(LocalFileName) Then

'delete it if found
VBA.Kill LocalFileName

End If

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Set Download Flag to True
FTPDownloadFile = True

' Download File
If FtpGetFile(hConnection, RemoteFileName, LocalFileName, False, 1, 0, 1) = False Then
MsgBox "Download - Failed At FTPGetFile!"
FTPDownloadFile = False

End If

'ensure file is not saved as read-only
SetAttr LocalFileName, vbNormal + vbArchive


' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

End Function


sErr = String(lenBuf, 0)
'retrieve the last respons info
InternetGetLastResponseInfo lErr, sErr, lenBuf
'show the last response info
MsgBox "Last Server Response : " + sErr, vbOKOnly + vbCritical
End Function



'AND HERE IS HOW I CALL THE FUNCTION


Private Sub Command27_Click()
Dim blnSuccess As Boolean

' True if successful, false if not.
' All values prefixed with Your need you to substitute the correct values for your sitaution in

blnSuccess = FTPDownloadFile("ftp.microsoft.com", vbNullString, vbNullString, "C:\readme.txt", "readme.txt", "/deskapps", "Binary")


End Sub


Thank you for the help

 

[HarleyQuinn]

What do you get if you use

Private Sub Command27_Click()
Dim blnSuccess As Boolean

' True if successful, false if not.
' All values prefixed with Your need you to substitute the correct values for your sitaution in

MsgBox FTPDownloadFile("ftp.microsoft.com", vbNullString, vbNullString, "C:\readme.txt", "readme.txt", "/deskapps", "Binary")


End Sub

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[flavius005]

I get a msg pop-up "Download- Failed at FtpGetFile!"
and if I press Ok, I get Runtime error 53. file not found

 

[flavius005]

It works now, after the last try I had the network down...

That's why I got the error message.

Thank you very much for your help.

It was a big help,

 

[Remou]

flavius005

It is always nice to reward a helpful person with a star. You can do this by clicking on the link above, it looks like this:

Thank HarleyQuinn
for this valuable post!

http://lessthandot.com