Given: Access linked database, FTP server and account, VBA code that creates/reads some data files
Problem: Send a file to server and read files from server in a reliable manner, without making complications with eventual installation and usage of the program.
-------------------------------------------------------
There appears to be 2 ways of using FTP with MS Access. One is to use a control object (http://www.databasejournal.com/features/msaccess/article.php/3513061/Simple-FTP-Methods-from-Microsoft-Access.htm) which has to be installed and registered on the user machines. Having made that mistake with the Fedex program, I try to avoid that process.
Second way is to use blocking shell function (http://bytes.com/topic/access/insights/841878-shellwait-function) and call cmd.exe ftp (http://miketurco.com/ms-access-ftp-using-vba-102199). Using command prompt output redirection, my program can even try to understand what is happening. This works neatly and sends/reads files with little code. It is certainly not without problems though!
Main issue is there is so much stuff that can go wrong, and the command prompt will simply end, with no warning. That's not reliable. Ideally, I'd like to have error codes returned... such as "Invalid login info", "No internet connection", "Server does not respond", "File already exists", etc. Instead, I have to parse the redirected output and deduce what happened. What a mess, this is going to be hard to debug and maintain in a month.
Second issue is that this process, though simple, seems to take a lot of time to run (I have to use blocking Shell command to avoid file errors). I bet the actual ftp transfers take less than half the time. This becomes a problem when my program needs to check the server periodically for response files - it is not good when every 10 minutes my code makes everything else slow for 5 seconds while checking the server. I suppose I am forced to make the user manually check for updates by clicking "Refresh" button.
Well, this is VBA, did I expect it to be anything but a mess?
Problem: Send a file to server and read files from server in a reliable manner, without making complications with eventual installation and usage of the program.
-------------------------------------------------------
There appears to be 2 ways of using FTP with MS Access. One is to use a control object (http://www.databasejournal.com/features/msaccess/article.php/3513061/Simple-FTP-Methods-from-Microsoft-Access.htm) which has to be installed and registered on the user machines. Having made that mistake with the Fedex program, I try to avoid that process.
Second way is to use blocking shell function (http://bytes.com/topic/access/insights/841878-shellwait-function) and call cmd.exe ftp (http://miketurco.com/ms-access-ftp-using-vba-102199). Using command prompt output redirection, my program can even try to understand what is happening. This works neatly and sends/reads files with little code. It is certainly not without problems though!
Main issue is there is so much stuff that can go wrong, and the command prompt will simply end, with no warning. That's not reliable. Ideally, I'd like to have error codes returned... such as "Invalid login info", "No internet connection", "Server does not respond", "File already exists", etc. Instead, I have to parse the redirected output and deduce what happened. What a mess, this is going to be hard to debug and maintain in a month.
Second issue is that this process, though simple, seems to take a lot of time to run (I have to use blocking Shell command to avoid file errors). I bet the actual ftp transfers take less than half the time. This becomes a problem when my program needs to check the server periodically for response files - it is not good when every 10 minutes my code makes everything else slow for 5 seconds while checking the server. I suppose I am forced to make the user manually check for updates by clicking "Refresh" button.
Well, this is VBA, did I expect it to be anything but a mess?
Regarding error codes.
ReplyDeleteIf hostname is wrong, I get either "Unknown host" or nothing (empty output file).
If no internet connection, everything fails even before my code runs because of linked tables. I suppose I do not need to make my code reliable in this case.
When user account info is wrong, there is a "Login failed" output.