Best way to manage heavy ftp files

[freuzo]

Hi guys,

I'm creating an access+mysql app and everything is fine except one part.
The app will upload/download files to/from a ftp server. The problem is that those files can weigh up to 10mo. Bandwidth is really not good around here and in a previous app that I built, downloading files was taking too long and the whole app was frozen for several minutes.

How can you suggest me to design this functionality in my new application ?

Thanks in advance.

 

[CJ_London]

if you have large files and narrow bandwidth it is always going to be a problem. It may be you can compress the files - but the server will need a means to compress/decompress. Or perhaps review the file contents - can they be reduced in some way?

Or maybe break the file up into smaller elements? won't be any quicker but perhaps a bit more flexible for time management?

Or use a timer to upload overnight?

 

[freuzo]

Hi,
Sorry for my late answer. Thanks for your suggestion.
Devide the files (scanned PDF documents) will be too much complicated for the users.

The timer is interesting but the downloads will be done during the day by users.

I am open to other ideas.

 

[CJ_London]

sorry, no other ideas - did you consider compression? tho' not sure how much a pdf would compress

 

[monheimx9]

What about calling a VBScript that is going to download/upload files instead of blocking the Access thread ?
Chillkat has lots of examples on his website example-code.com/vbscript/ftp.asp

If you don't like VBS it's also possible to do it easy with Python

 

[XPS35]

Is it perhaps an option not to upload and download directly from the application via ftp? Export and import the files to and from hard drive. Sending and receiving via ftp also runs via HD. The application will then not be bothered by the slow ftp.

 

[KitaYama]

Devide the files (scanned PDF documents) will be too much complicated for the users.

No need the users know what's under the hood. Both winRar and winZip can be used from command line. It means that with only one line of code in VBA you can split a given file to several zipped files (each one from 5KB or above).
Then another line can extract and combine them.
Users only select a file and click a button.

winRar command line :

Using the WinRAR Command-line tools in Windows

How to use the WinRAR command-line tools to compress and uncompress files in a directory using batch files.

cects.com

tho' not sure how much a pdf would compress

I just tested. A 47MB file zipped with winrar went down to 36MB

 

[freuzo]

sorry, no other ideas - did you consider compression? tho' not sure how much a pdf would compress

No need the users know what's under the hood. Both winRar and winZip can be used from command line. It means that with only one line of code in VBA you can split a given file to several zipped files (each one from 5KB or above).
Then another line can extract and combine them.
Users only select a file and click a button.

winRar command line :

Using the WinRAR Command-line tools in Windows

How to use the WinRAR command-line tools to compress and uncompress files in a directory using batch files.

cects.com

I just tested. A 47MB file zipped with winrar went down to 36MB

I don't think compression would do it for the app. But the suggestion below is interesting.

What about calling a VBScript that is going to download/upload files instead of blocking the Access thread ?
Chillkat has lots of examples on his website example-code.com/vbscript/ftp.asp

If you don't like VBS it's also possible to do it easy with Python

If I can create a little thing outside of MS Access that would be great (something that can be launched by a button in Access).
I will take a look at this site.

 

[freuzo]

Is it perhaps an option not to upload and download directly from the application via ftp? Export and import the files to and from hard drive. Sending and receiving via ftp also runs via HD. The application will then not be bothered by the slow ftp.

I didn't quite understand your process.

 

[monheimx9]

On this stackoverflow post there's also someone mentioning calling directly winscp from Commandline so it might be easier

stackoverflow.com/questions/37942715/vbs-ftp-download-wait-time

 

[Minty]

I write a Putty script on the fly in VBA, that is then executed from Access to perform an SFTP transfer.
Admittedly, it's a small transfer, but it would work for any size I assume.
It's independent of access, and you can hide any notifications/command line echoing relatively easily.

 

[freuzo]

On this stackoverflow post there's also someone mentioning calling directly winscp from Commandline so it might be easier

stackoverflow.com/questions/37942715/vbs-ftp-download-wait-time

but will the user get a notification that the file has been up/down?

 

[gemma-the-husky]

how big are the files? 10mo?
What does that mean?

Try filezilla
How long does it take to download a file?

I added support for winscp when we had a port problem with ftp.exe, but we got ftp working. Would there be much difference?

could you automate a scheduled process to page for new files from the ftp source every so often, and collect any new ones.
the other way round to submit uploads. Then you just need your access programme to check the inbound folder every so often for new files, and to put the files for sending into the outbound folder.

 

[The_Doc_Man]

Your problem is simple: An Access class module routine is synchronous when executing event code. Another class module can execute, perhaps, if more than one form is open. However, each form's code is always executed synchronously.

The solution is to force creation of another thread - such as what happens with the SHELL command. However, Access will have a hard time knowing when an independent thread is finished unless you create a SHELL script that leaves behind an indicator that could be read by your app.

The real problem is that without doing something REALLY complex involving an API to execute a process FORK operation, you still have the issue that you want the best of two distinct worlds. Easy coding and easy status returns - implying synchronous operation - or faster operation that doesn't block what you are doing within the app - implying asynchronous operation. You cannot have both without a lot of work.

 

[JMongi]

Sorry I don't have a direct solution for you. Perhaps this article on VBA wrapper for powershell commands might be useful for you?

VBA - Run PowerShell Command | DEVelopers HUT

A couple examples of how you can execute a PowerShell command through VBA a retrieve the response.

www.devhut.net

As @The_Doc_Man says, you need a return value to check from your indepenedent thread. I believe you should be able to use these VBA wrappers around PS commands to receive such an output to track.

 

[RogerCooper]

if you have large files and narrow bandwidth it is always going to be a problem. It may be you can compress the files - but the server will need a means to compress/decompress. Or perhaps review the file contents - can they be reduced in some way?

Or maybe break the file up into smaller elements? won't be any quicker but perhaps a bit more flexible for time management?

Or use a timer to upload overnight?

It is worthwhile to check whether you even need such files. Unless the application inherently involves images, you may be able to reduce the data transfer. For example, sending the information needed to create an invoice rather than an image of the invoice.