Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-05-04
20
Medium Priority
?
403 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:Joar
  • 9
  • 5
  • 3
  • +3
20 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 35696041
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
 
LVL 40

Expert Comment

by:als315
ID: 35696243
You can also connect folder from ftp server as network disk with letter and work with it as with usual network disk
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35696478
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Expert Comment

by:duncanb7
ID: 35700763
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
 

Author Comment

by:Joar
ID: 35726789
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
 

Author Comment

by:Joar
ID: 35727465
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
 
LVL 40

Expert Comment

by:als315
ID: 35732150
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
 

Author Comment

by:Joar
ID: 35735320
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
 

Author Comment

by:Joar
ID: 35735782
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
 
LVL 40

Expert Comment

by:als315
ID: 35736854
0
 

Author Comment

by:Joar
ID: 35744181
I guess you mean the code in the accepted solution?
I get a compilation error....se the attached image
DownLoadComperror.png
0
 
LVL 40

Expert Comment

by:als315
ID: 35744258
I've tested last solution (comment 16208871). Accepted is previous.
0
 

Author Comment

by:Joar
ID: 35744358
Get the same type of compilation error in that code
(I am using Access 2007 (mdb)
DownLoadComperror.png
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 35744890
I hope you have included all this text in module, not in a form code?
0
 

Author Comment

by:Joar
ID: 35749673
No, all in a form. Why should that do any difference?
0
 
LVL 59
ID: 35751243

 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
 

Author Closing Comment

by:Joar
ID: 35752096
Excellent help! Functions! Sorry for not having awarded the other contributers if I should have done.
0
 
LVL 59
ID: 35753113

 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
 

Author Comment

by:Joar
ID: 35753511
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
 
LVL 59
ID: 35753709
<<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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question