I have a client that asked me to develop his Windows Application and use MS Access instead of SQL database. I did and now he asked me that he deployed the Application through several of his offices.
Now he wished to have a "main Server" with all information from the "client applications" for Reporting purposes and also to update information per "client application". I proposed the idea of "EDI" through FTP, also converting the current app to connect thru SQL via WAN, but somehow he said that FTP is not an option and also WAN connection is impossible since the internet on their country is so slow that an mp3 must be downloaded for 20 minutes or so he says.
Is there any other way to communicate with the other DB(MS Access) and capture data or update data as necessary without using FTP EDI exchange?
Best Answer
I suggest start reading this article http://www.kallal.ca/Wan/Wans.html It explains in detail why Access on a WAN is not reliable. It shows also some alternatives, like
using MS SQL server as a backend store instead of access (I guess this will only help if you get those servers into a shared intranet)
create a web-based system (for example, a REST API)
use replication (which typically requires either ftp or a VPN) EDIT: read this SO post about Access replication, or the MS Access replication FAQ here. Replication of the local MDBs to the central office may be solution in your case.
use a terminal server (which I guess is not a solution for the problem you have described)
use a Share Point server
You could also try to utilize something like DropBox, GoogleDrive or MS Office 365 to replicate the access MDB from the local offices to the central office, but make sure you transfer only consistent copies of the MDB files this way and don't work directly on those files while replication is in progress.
You should clarify the following questions with your client:
which kind of shared network access is available?
how up-to-date must the data in the central office be?
EDIT: SMTP is not secure, but of course you can pack, encrypt and and send the MDB files programmatically by mail, if you have no shared file server available and don't want to use any of the "cloud" services I suggested above. Someone has to press the "send data to central office" button in the local offices, but if that is ok for you, why not.
Of course, this all boils down to "how to transfer files between offices without ftp or a shared file server", which is not an Access question any more (so you might choose a different title for your question).