How to do ftp transfer between ms Access vba and a web server?

I have an access app (2007) that needs to upload local files to a web server.
I want to use FTP (or other method if it exists and is better).
This must be done within the vba code!

Later on I will need to download theese files back to the local disc.
How do I do that?
Do I have to save them to a local file before reading them into the app for manipulating?
Normally the files are of type pdf.

JoarAsked:
Who is Participating?
 
als315Commented:
I hope you have included all this text in module, not in a form code?
0
 
sshah254Commented:
You can get ActiveX controls for FTP and manipulate them to upload / download files - this is the appropriate way to do it.

You can excecute command line FTP from within VBA using various methods (and command line ftp can be set to use the commands from a script file).

Check this out - http://bytes.com/topic/access/answers/210987-code-controling-ftp-access-vba

Ss
0
 
als315Commented:
You can also connect folder from ftp server as network disk with letter and work with it as with usual network disk
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
duncanb7Commented:
Try this the code  is finally working fine in VBA Excell, so similar to Access

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_26599510.html
0
 
JoarAuthor Commented:
duncanb7:
I have followed your link: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_26599510.html 

and was able to upload a file from my ftp server on the web, but:

1) I am only able to upload to the root level, not to a sub folder.
    I cannot see how I can modfy this somewhat cryptical code to do that (se attached code)

2) May I easely change the code to download a file as well?
    I have seen there exists a ftpGetfile, but I cannot understand how to use it.
Public Function FtpUploadFile( _
    ByVal server As String, _
    ByVal username As String, _
    ByVal password As String, _
    ByVal localFile As String, _
    ByVal remoteFile As String) As Boolean
    
    Dim fOk As Long
    
    hOpen = InternetOpenW(0, INTERNET_OPEN_TYPE_PRECONFIG, 0, 0, 0)
    
    hConnect = InternetConnectW(hOpen, _
                                    StrPtr(server), _
                                    INTERNET_DEFAULT_FTP_PORT, _
                                    StrPtr(username), _
                                    StrPtr(password), _
                                    INTERNET_SERVICE_FTP, _
                                    INTERNET_FLAG_PASSIVE, 0)
    If hConnect = 0 Then
        Debug.Print "InternetConnect failed."; Err.LastDllError
        CleanUp
        Exit Function
    End If
    
    fOk = FtpPutFileW(hConnect, _
        StrPtr(localFile), _
        StrPtr(remoteFile), _
        FTP_TRANSFER_TYPE_UNKNOWN, 0)
        
     
    CleanUp
    FtpUploadFile = fOk
End Function

Open in new window

0
 
JoarAuthor Commented:
als315 said: You can also connect folder from ftp server as network disk with letter and work with it as with usual network disk

This seemed to be the simplest solution!
I wonder:
Can I do this folder connection from within Access VBA, specifying user name and password?
This way I just have a full access to the ftp server while the access app is uploading files.

0
 
als315Commented:
I don't know how to add network drive from VBA (it seems simple, but I can't find solution).
For downloading file you can use symmetric function:

Public Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileW" (ByVal hFtpSession As Long, ByVal RemoteFile As String, ByVal LocalPath As String, ByVal FailIfExists As Boolean, ByVal FlagsAndAttributes As Long, ByVal Flags As Long, ByVal Context As Long) As Boolean
0
 
JoarAuthor Commented:
I will look at the FtpGetFile...
But first I will continu the search for a method for adding the web as a network drive from VBA, because this seemes to give me the simplest solution. Then I may read and save files as with ordinary disc drives. If somebody has a tip on that I will be happy.  Comming back....
0
 
JoarAuthor Commented:
I am not able to have the FTPGetFile to do the job..and I do not know how to get the excat error from that function.

I have a function that uploads a file using the ftpPutFil.
This functions quite well.
Do you see anything wrong in my ftpGetFile code below?
It is this part of the code that returns false (0):

  fOk = FtpGetFileW(hConnect, _
        StrPtr(RemoteFile), _
        StrPtr(localFile), _
        False, _
        0, _
        FTP_TRANSFER_TYPE_UNKNOWN, 0)


Here is my code:
Private Declare Function FtpGetFileW Lib "wininet.dll" (ByVal hFtpSession As Long, ByVal RemoteFile As String, ByVal LocalPath As String, ByVal FailIfExists As Boolean, ByVal FlagsAndAttributes As Long, ByVal Flags As Long, ByVal Context As Long) As Boolean


Public Function FtpDownLoadFile( _
    ByVal server As String, _
    ByVal username As String, _
    ByVal password As String, _
    ByVal RemoteFile As String, _
    ByVal localFile As String) As Boolean
   
    Dim fOk As Long
   
    hOpen = InternetOpenW(0, INTERNET_OPEN_TYPE_PRECONFIG, 0, 0, 0)
   
    hConnect = InternetConnectW(hOpen, _
                                    StrPtr(server), _
                                    INTERNET_DEFAULT_FTP_PORT, _
                                    StrPtr(username), _
                                    StrPtr(password), _
                                    INTERNET_SERVICE_FTP, _
                                    INTERNET_FLAG_PASSIVE, 0)
    If hConnect = 0 Then
        Debug.Print "InternetConnect failed."; Err.LastDllError
        CleanUp
        Exit Function
    End If
   
    fOk = FtpGetFileW(hConnect, _
        StrPtr(RemoteFile), _
        StrPtr(localFile), _
        False, _
        0, _
        FTP_TRANSFER_TYPE_UNKNOWN, 0)
       
     
    CleanUp
    FtpDownLoadFile = fOk
End Function
0
 
JoarAuthor Commented:
I guess you mean the code in the accepted solution?
I get a compilation error....se the attached image
DownLoadComperror.png
0
 
als315Commented:
I've tested last solution (comment 16208871). Accepted is previous.
0
 
JoarAuthor Commented:
Get the same type of compilation error in that code
(I am using Access 2007 (mdb)
DownLoadComperror.png
0
 
JoarAuthor Commented:
No, all in a form. Why should that do any difference?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 AddressOf is only allowed in a standard code module.  It cannot be in a class module (which is what form and report modules are).

JimD.
0
 
JoarAuthor Commented:
Excellent help! Functions! Sorry for not having awarded the other contributers if I should have done.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 I just re-read my comment, which was a bit off; the procedure that addressof specifies cannot be in a class module.  That's almost the same thing, but not quite.

JimD.
0
 
JoarAuthor Commented:
JimD:
I did put it in a standard module and then it compiled OK. May be you should have been rewarded for that, but it is to late now..
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I did put it in a standard module and then it compiled OK. May be you should have been rewarded for that, but it is to late now.. >>

  Not an issue, Als315 was already there.  I just added a bit, that's all.

JimD.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.