FTP files into AccessXP (AccessXP)

Topic

New Reply

I would like to put a button on a form that does the following:
Go to an FTP site on the internet, login, and download a text file.
This does not work:
DoCmd.TransferText acImportDelim, , “UserUpdates.txt”, “ftp://www.somesite.com/somefolder/UserUpdates.txt”
I am getting the error “Runtime error 3652 Login failure”.
I tried manually doing the following:
File
Get external data
Import
Look in … FTP Locations
http://www.somesite.com (I think this works because I can see the files on the FTP site)
I select UserUpdates.txt
Import
…and I get the same error message.
I can open an FTP session using IPSwitch or Procomm program and import the file without trouble.
I would like to do it within Access. Should I be looking at using the Microsoft Internet Transfer control or some other utility?
Any and all suggestions welcome.

Reply | Quote

Replies

When you open an FTP session and do the transfer… is there a login ID and password?… or are you going in as Anonymous?

Reply | Quote

The ftp site requires a username and password. I entered username and password in my FTP Locatation when I set it up.
Thanks,

Reply | Quote

I’d recommend using the MS Internet Transfer Control (ITC), unless you’d prefer tangling with the Windows WinInet API directly – not recommended unless you need some functionality not provided by the ITC. The ITC provides a (relatively) user-friendly wrapper for the WinInet API. Example of use to download file via FTP:

Function ftpDownloadFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal RemoteFileName As String, _
ByVal LocalFileName As String) As Boolean

On Error GoTo Err_Handler

‘ Library: InetCtlsObjects
‘ C:WINDOWSSYSTEM32msinet.ocx
‘ Microsoft Internet Transfer Control (ITC) 6.0 (SP4)
‘ (distributed w/MS develoment tools – redistributable control)

Dim FTP As Inet
Dim strMsg As String

Set FTP = New Inet
With FTP
.Protocol = icFTP
.RemoteHost = HostName
.UserName = UserName
.Password = Password
.Execute .URL, “Get ” + RemoteFileName + ” ” + LocalFileName
Do While .StillExecuting
DoEvents
Loop
ftpDownloadFile = (.ResponseCode = 0)
End With

Exit_Sub:
Set FTP = Nothing
Exit Function
Err_Handler:
‘ Error handling, etc here

The function returns True if the operation succeeds. If specified local file already exists, function will fail. You need to set a reference to the MSINET.OCX file as noted. The control is included with MS developer tools, you’d have to distribute it to users who may not have the control installed on their pc. FTP does not support long filenames or spaces. You can use GetShortPathName API function to get MS-DOS 8.3 short file name if necessary. Example:

Declare Function GetShortPathName Lib “kernel32” _
Alias “GetShortPathNameA” _
(ByVal lpszLongPath As String, ByVal lpszShortPath As String, _
ByVal lBuffer As Long) As Long

Public Function apiGetShortPath(ByVal strFileName As String) As String

Dim lngStrLen As Long
Dim strPath As String
strPath = String$(165, 0)
lngStrLen = GetShortPathName(strFileName, strPath, 164)
apiGetShortPath = Left$(strPath, lngStrLen)

End Function

Example:
? apiGetShortPath(“C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE”)
C:PROGRA~1MICROS~1OFFICEMSACCESS.EXE

For more info on using ITC do a MSKB search on “Internet Transfer Control” you’ll find some sample code & downloads that may be useful.

HTH

Reply | Quote

Mark,
Thanks. I found some examples in MSKB. I tried this behind a command button:
Inet3.Execute “ftp://www.somesite.com”, _
“GET UserUpdates.txt C:My DatabasesUserUpdates.txt”
but got the message
Error 35754
Unable to connect to remote host

I checked the properties of the control and am sure the
user name, password are correct. The URL property looks like this
ftp://username:password@www.somesite.com (names changed to protect the innocent.)
protocol property is 2-icFTP
access type 0-acUseDefault
request timeout 60

I will copy your code into a function and call it from the command button and see what happens.
In the meantime, I found a good chapter in the Ekedhal book on implementing an FTP client.
Thanks

Reply | Quote

(Edited by MarkD on 23-Jul-03 20:16. Added link to MSKB Article.)

Here is example of how I used function in previous reply:

ftpDownloadFile “ftp.nowhere.com”,”markd”,”secretpwd”,”TEST.zip”,”C:ACCESSTEST.ZIP”

(Names changed to protect the guilty.) This command successfully downloaded “TEST.ZIP” file from the FTP site to local PC. You can also stick the ITC control on a form and call its properties/methods that way. I had this note on a test form:

‘ URL must be specified BEFORE UserName & pwd (ref: MSKB 173264)

Recommend check this MSKB article, possible bug involving the order in which URL, Username & password are specified:

HOWTO: Internet Transfer Control Using Username and Password

Here is another link (to MSDN) that may be useful:

Microsoft Internet Transfer Control

HTH

Reply | Quote

Mark,
Thanks for your help. I am able to GET files successfully if they are 8.3 names. I am working on the GetShortPathNames function now. I just found out that not all the users are using AccessXP. It turns out that some still use A97. I am going to make a run-time version to load on their laptops and see if it works.

Thanks again.

Reply | Quote