Tuesday, April 10, 2012 2:38 AM
I am looking to include some ftp functionality within an A2007 database.
Ideally I want to do the file transferring in the background and not hold up my front end while transfers are in process. It will be several smaller text based files (Up to 20 - 30 files at a time) both uploaded and downloaded; I am assuming this will unnecessarily bog down my app if it has to wait.
Of the two ftp options I find most used, ftp.exe or the InetTransferLib dll, I am not sure which may the better path to commence with. The latter dll seems better to control within code as opposed to bat files, but as I see it the ftp.exe can be shell executed autonomously.
Can the dll version work in the background? Is either of the two a better option for future support?
Tuesday, April 10, 2012 3:02 AM
I'm not thrilled about the ftp.exe route with a batch file. One thing is that it's more difficult to get status information from such process. If it fails to connect, I would want to show a nice error message. Hard to do with batch files.
If you use InetTransferLib, in its simplest incarnation it would be inline code that runs synchronously and thus the user has to wait.
But what if you have a second accde, which you run from your main app, and its purpose is to do the FTP thing asynchronously (its natural mode)? If an error occurs, it can pop up the error message.
-Tom. Microsoft Access MVP
Tuesday, April 10, 2012 3:31 AM
Thanks for the comment.
I did think of this as a possible path but wasn't too confident it was 'tidy' programming. When you say 'it can pop up an error message' I assume the primary FE can't have an error raised, or similar.
The solution doesn't seem a long way off using ftp.exe though. Either could save any errors (A file or table), for the FE to pick up and report to the user from main code. Ideally I want to do the transfers seamlessly so putting up an error would be a last resort.
Tuesday, April 10, 2012 4:32 AM
I have FTP VBA code that I can supply you. The FTP dll is a blocking process which means that it doesn't return until the process is complete. I don't see why the dll couldn't work in background mode. You would need some method of determining when the background process completely. A task like this may be better to create in Visual Studio than in Access VBA. From visual Studio you can eithe ropen an Access application or connect to your database.
You said Access was your Front end so I'm assuming the downlaoded files are stored in a BE database? Is there a reason you don't want to do a query that refreshes in background mode?
Tuesday, April 10, 2012 4:54 AM
I considered ftp.accde superior to ftp.bat because it can pop its own error messages. Why is it important to have the main app do that? The secondary program can appear like a Popup form and just be sitting on the side showing status of the up/download.
Of course popping up errors is a last resort, but as programmers we have to keep that in mind. The internet connection may be down, the site may be down, the network card may be disabled, all kinds of badness will happen if you try often enough. Ideally our program is robust enough to deal with these scenarios.
Yes, there are ways to have an error raised in the main app, but it requires a fair amount of Windows API or COM programming.
-Tom. Microsoft Access MVP
Tuesday, April 10, 2012 5:29 AM
Thanks Tom and Joel.
Tom the more I think about the ftp.accde the better it sounds. Even to save any errors in a table the main FE can later check. My situation doesn't require instant notification. I could also look to directly transferring the files into a ftp.accde table rather than via a file.
Joel (And Tom) I have a few varied situations I am trying to cover.
(1) is downloading small (~1k) csv data files generated by some dedicated industrial computers. These will need to be imported then processed by my db.
(2) is (attempting) to do some basic data transfer / sync with some mobile devices. Because my db is not (always) online I am considering use of ftp to 'park' some data, awaiting opportunity to upload / download as required from each end.
A key element is not requiring a permanent online presence hence trying to keep away from an online server style solution; this particularly extends to not keeping main data online, hence Access seemed like a good place to start.
Because some data must be in simple text format then I am looking to base the other needs around this also, esp when 'instant' isn't a requirement.
I am also mindful of issues (read about) of trying to use an mdb / accdb database online, and potential corruption. My thought of having a separate transfer action, then processing local files was what I was going to look at.
Now I can hear (real programmers) gasps at this approach, so am mindful of pitfalls and open to guidance. While gaining experience and relatively confident with Access, I am still only in a learning curve. To jump to alternative solutions (Be them online, or .net etc...) is realistically outside my skillset, available time and application requirements.
I am walking on the edge... but the brain work is enjoyable.
Tuesday, April 10, 2012 8:50 AM
Small 1k files that you need to open any way doesn't make a lot of sense to download and then open. If permision are set correctly you should be able to open the files directly at the server. I would mount a network drive to the remove server manually and then get the files like any other file. Yo can go into a window explorer (not IE) and in the manu
Tools - Map Network Drive add a folder in the remove computer. You can eithrt type IP address
Tuesday, April 10, 2012 9:57 PM
I hadn't thought of that way.
While what you say makes sense, it requires secondary activities by the user. I would prefer to keep it all contained within my application, especially when things go wrong and multiple checks (ie remounting drives, passwords, what drive is spare etc...) external to the app are needed to reestablish the system.
Could a UNC path be stored within the db, plus password / username, and the link created on the fly?
When you say 'doesn't make a lot of sense to download then open', is this simply because of wasted time downloading files?
Tuesday, April 10, 2012 11:44 PM
You could also get to the drives if the permissions are setup correctly by using the network pathname and the drive is listed as shared.
\\myserver\myfolder or \\18.104.22.168\myfolder
You can try this using your window explorer. If you have admin rights to the drive you can get access using \\myserver\myfolder\c$
Wednesday, April 11, 2012 5:34 AMDon't allow normal users to have access to administrative shares, especially C drive where it's commonly where operating system files live. The server would have got infected pretty soon. (And I don't think any sensible IT admins would be persuaded to allow this.)