none
Upload and Download via FTP on Windows 7, 8, 10 and Server 2008 RRS feed

  • Question

  • All the FTP code for VBA that I see is very old. I am having trouble finding a way to use FTP in my Access project that can run on Windows Server 2008 and 2016. I've tried one that uses wininet.dll but I cannot get that to run on 2008 server, only on my windows 8 desktop. I haven't yet tested on Windows 2007 or Server 2016, but if I can't find one that works on 2008 server, I might as well not test if it works on the other OSes because this companies terminal servers are primarily 2008.

    The reason  is I'm hosting the backend using an Azure Database(not hosting a VM). I am storing files for a company's customers in a table in binary. When employees are downloading I just read the binary into a stream and create the file on the users computer. The Azure price tier I use is $35 per month, but only supports databases up to 250gb. I am about to run out of space very soon. The only way I see to get 500gb is to pay around $400 per month. I want to just use ftp and store the actual files on my web server.

    I have read through so many articles and haven't found FTP that works on a server. At least all my tests have failed, and the code I have does not return errors from the wininet.dll library so I can't really troubleshoot it. Anyone have any ideas?

    Friday, June 22, 2018 6:57 PM

All replies

  • Hi,

    Just curious, rather than hosting a FTP server, can you host a web server instead? I think there are VBA code available to download files from the Internet, so if you can host a web server for your files, you might be able to use them.

    Just my 2 cents...

    Friday, June 22, 2018 7:27 PM
  • I have a web server, and that is where I was going to store and FTP into.

    Are you suggesting I use http to download the files? I can create a website to do this pretty easily, but how do I download files using VBA that will work on all different windows OSes?

    How about uploading them? How would I do that?

    Monday, June 25, 2018 4:54 AM
  • Hi,

    For downloading a file from the Internet, have a look at this function from UtterAccess: DownloadBinary

    For uploading files (using HTTP), you may have to create a web service first for accepting a file and then use a POST technique similar to the one used in this article: HttpRequest Class

    Hope it helps...

    Monday, June 25, 2018 2:41 PM
  • Yes, that is alot of help. I'm getting closer.

    I'm trying to basically POST from VBA as you would a Form like I would on a web page. Not sure if this is the best way to do it.

    I'm having trouble figuring out how to create an object to hold the file and its metadata so I can put it into the Request header.

    When you read a Request object, don't the files have many properties like name, length, data? This is why I think I need an object. Otherwise I will have to send the file bytes, and add parameters for the other values?

    Here is what I have:

    Declare Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)
    
    Public Function UploadFile(src As String)
        Dim xhr As New XMLHTTP
        Dim webServiceURL As String
        Dim actionType, controllerType As String
        Dim postData As String
        Dim fileData() As Byte
        Dim fileString As String
        Dim fileSize As Long
        Dim fileNumber As Long
        
        src = "c:\temp\myVideo.mp4"
        webServiceURL = "http://localhost:52546/"
        controllerType = "Core"
        actionType = "UploadCustomerFile"
        
        fileNumber = FreeFile
        Open src For Binary Access Read As fileNumber
            fileSize = LOF(fileNumber)
            ReDim fileData(fileSize - 1&) As Byte
            Get fileNumber, , fileData
        Close fileNumber
        fileString = StrConv(fileData, vbUnicode)
        postData = "CustomerId=12345&cFile=" & fileString ' Not sure how to create a file object to pass.
        
        xhr.Open "POST", webServiceURL & controllerType & "/" & actionType, False
        xhr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        xhr.setRequestHeader "Content-Length", Len(postData)
        
        xhr.Send postData
    
    
        If xhr.status = 200 Then
          MsgBox xhr.getAllResponseHeaders
          Debug.Print xhr.responseText
        Else
          MsgBox xhr.status & ": " & xhr.statusText
          Debug.Print xhr.responseText
        End If
        
        Set xhr = Nothing
    End Function

    Thursday, June 28, 2018 9:45 PM
  • Hi,

    You may have missed what I said earlier about creating a web service on the host first to accept/handle the file upload.

    I can't say for certain this is the only way it will work, but it's been the way I have seen it work.

    For example, have a look at this other thread at UtterAccess.

    Hope it helps...

    Thursday, June 28, 2018 10:00 PM
  • I didn't miss that part. I created a service to accept/handle the uploaded file. I'm testing it on my Dev machine which is why the service URL is on LocalHost. I am able to place breakpoints in the service code and try to see if the file is in the Request object on the server. So far I can pass key/value of simple types.

    Is there a big difference between using a Form style post request as opposed to a Web API which would probably use JSON or some similar format requiring a library or something to serialize the data in Access.

    Given your examples, I'm closer but the problem still exists: how to build the data object or string containing a file to pass to the service. Its simple if I were just sending key/value pairs of non-complex types. But I can't figure out how to put a file in the post.

    Thursday, June 28, 2018 10:45 PM
  • Ok, I still can't figure out how to build the request text properly. And I'm experiencing some strange behavior too. There is a Cookie request header filled with data that is not from this request.

    Cookie: Tawk_59234d6a8028bb73270473a2=vs74.tawk.to::0; _ga=GA1.1.139483695.1511988483; __atuvc=3%7C48; LocationFilter=country=US|state=AZ|city=Tucson|postalcode=|distance=0|areaid=-1

    But the major issue is when I upload a 10mb video file, I can now see it in the request, and MVC model binding attaches it to the HttpPostedFileBase property of the model, BUT it is now a 16mb file which does not play.

    Here is my new code:

    Declare Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)
    
    Public Function UploadFile(src As String)
        Const Boundary As String = "---------------------------0123456789012"
        Dim xhr As New XMLHTTP
        Dim webServiceURL As String
        Dim postData As String
        Dim fileData() As Byte
        Dim fileString As String
        Dim fileSize As Long
        Dim FileNumber As Long
        
        src = "c:\temp\myVideo.mp4"
        webServiceURL = "http://localhost:52546/"
        
        fileString = GetFileString(src)
        postData = "CustomerId=12345"
        
        xhr.Open "POST", webServiceURL & "Core/UploadCustomerFile", False
        ''xhr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        xhr.setRequestHeader "Content-Type", "multipart/form-data; boundary =" & Boundary
        
        Dim D As String
        Dim FieldName, FileName As String
        FieldName = "CustomerFile"
        FileName = "myVideo.mp4"
        
        'D = "Content-Type: multipart/form-data; boundary =" & Boundary
        D = "--" & Boundary & vbCrLf
        D = D & "Content-Dis-data; name=""" & FieldName & """; filename=""" & FileName & """" & vbCrLf & vbCrLf
        D = D & fileString & vbCrLf
        D = D & vbCrLf & "--" & Boundary & "--" & vbCrLf
        
        Debug.Print "FileStringLen = " & Len(fileString)
        Debug.Print "DLen = " & Len(D)
        xhr.setRequestHeader "Content-Length", Len(D)
        xhr.send D
        
        If xhr.status = 200 Then
          MsgBox xhr.getAllResponseHeaders
          ''Debug.Print xhr.responseText
        Else
          MsgBox xhr.status & ": " & xhr.statusText
          Debug.Print xhr.responseText
        End If
        
        Set xhr = Nothing
    End Function

    Monday, July 2, 2018 7:23 PM