Re: Reliability Problems split FE/BE. A better Way?
Here is an idea but it depends on your business model being compatible.
If you do not have a rock-solid network connection, you will almost certainly corrupt an Access app having a FE remotely connected to the BE by anything other than hard Ethernet or other long-haul network wiring.
However, if you can handle a model where you can build orders and take data on a local version and then build transactions to send to a central system, and if you can handle the idea that you would be working with point-in-time snapshots, you might be able to essentially "roll your own" form of synchronization.
You would have the "field" app that is a local Access DB, maybe even monolithic rather than split. You set it up so that your company info can be imported via some type of file, perhaps a spreadsheet. You have a routine in the FE that does this import in a way that it REPLACES rather than updates your local copy of the corporate data, which has to remain as an authoritative copy of everything EXCEPT what the sales reps do.
You also have a corporate app that remembers what the sales staff tell it in a way I will describe later. This corporate app has to be zealously protected because if it ever goes bad, the backbone of this idea falls apart. This corporate app has to have a way to export data that will be needed by the sales staff, in a format that can be downloaded as a distinct file, call it the "sync" file. The corporate app must build the "sync" file in time for the sales reps who need it.
So... the sales rep remotely connects to the corporate system to download the "sync" file. You download this file using Windows File Sharing methods or FTP methods. FTP is good because that protocol was designed for remote connections of uncertain stability (but it is only good for whole file transfer, so Access can't use it.) It allows for requerying and retransmitting data buffers after an error - but more importantly, it also knows when the transfer didn't work.
The sales rep takes the downloaded file and syncs it with his field app. Now he visits his customers and does whatever he can do with that snapshot. But it's local and should be highly reliable.
Later, he has done whatever needs to be done, returns to the hotel, and triggers a function to export any transactions that need to be sent to corporate offices. Again, he connects and uploads the files.
Now either by automation or through some sort of sales assistant clerk at HQ, the field transaction file gets processed. At this point, you have your order data and the sales person made those orders with the best possible info at the time.
The critical parts are the file exchanges and those can be retried until they are good. This does, however, mean that your sales people will be working with NEAR real-time data, not actual real-time data. If your sales model can handle this, you can implement what I said fairly easily with import and export functions and a few update or append queries.
If you cannot handle near-real-time data, there is no current solution that stays within Access 100%, though if Citrix is possible, it comes close. Even Citrix can have a loss of connection and at that point you run the risk of locking up the central database app anyway. However, Citrix is LESS likely to lead to corruption, particularly if you have it set up to allow session reconnection. Note, however, that most IT guys will tell you "Not only NO, but HELL NO" on session reconnection (the ability to reconnect to a disconnected session).
That's the only way I can see to do this and stay within Access.
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.