Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FTP files into AccessXP (AccessXP)

    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
    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.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FTP files into AccessXP (AccessXP)

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

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: FTP files into AccessXP (AccessXP)

    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

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FTP files into AccessXP (AccessXP)

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

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FTP files into AccessXP (AccessXP)

    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://usernameassword@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

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: FTP files into AccessXP (AccessXP)

    <P ID="edit" class=small>(Edited by MarkD on 23-Jul-03 20:16. Added link to MSKB Article.)</P>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

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FTP files into AccessXP (AccessXP)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •