Lilly, I looked at the Kallal article. It left out some considerations. Here are some issues I didn't see addressed in it.
Q: Why split a database and then distribute it?
A: Because every time you open a file, whether or not it is visible to anyone else, you go through the file system, which goes through the lock manager to take out locks on what you open.
If everyone uses the shared copy of the front end (FE) on the shared drive, everyone's locks must interact with all other current lock holders. HOWEVER, if all of the queries, forms, reports, macros, and modules are in a PRIVATE COPY of the FE file, then when you open THAT file, it is not visible to everyone else. The locks are still taken out but (a) they are all PRIVATE so (b) no interaction with users. Which means (c) no chance of being locked out of a particular form or report.
If a file is local to your system (private copy) then your system's internal lock manager performs the operation and is pretty fast since every lock is in local memory. If the file is shared, then the machine hosting the shared file must act as the lock manager and your lock interactions must cross the network.
Access itself does lock management beyond that done by the file system. The .LDB file that gets created in the same folder as the FE file is Access's lock database. If it is shared then all sharing users must update THAT file, again contending with lock competition. But for the FE file as a private copy? Locks are created - but there is no need to interact with everyone else so no competition.
Of course, the shared back end (BE) still has this problem of lock interaction. So things are still tougher than they would be for isolated files. But this would be the same for shared or non-shared FE so no BE lock difference between those cases.
Q: Is a split & distributed DB faster than one where all the files are on the shared server?
A: Yes but sometimes not by much. And using an "auto download" batch file negates the tiny speed difference.
Access with a native FE and native BE is essentially a file-based application. It uses SMB protocol, which is the Windows file sharing protocol. If you open a local copy of MS Access (.EXE file) but are launching a shared FE, that file has to be copied in part or in total to the working memory of your PC using SMB protocol. That happens because MSACCESS.EXE has to find everything in the database so that when you open something, it knows where to look for it. If the FE is local however, that's just a disk read. So the difference in speed is due entirely to the difference between a disk operation and a network operation - a miniscule difference.
Speed improvements can occur for local copies of FE databases that have lots of code behind the scenes, lots of queries that open dynamically, lots of macros that get triggered. But for a typical user who opens one form at a time, one report at a time, the speed difference between local copies and network copies can be hard to see.
Speed differences don't exist for operations on the BE because they are always based on file transfer operations across the network. The only way to make that faster is to either optimize the database structurally OR buy a faster network.
Q: How do I optimize the database structurally?
A: For that, use the search feature of this forum to look up articles on database optimization. If you are not familiar with it, the SEARCH feature is 3rd from the right in the ribbon near the top of this page, just under the box that identifies who you said you were when you logged in.
In general, judicious indexing will make some functions faster. Persistent connections between FE and BE can help. And tailoring of all queries, forms, and reports to use either NO LOCKS (for read-only cases) or OPTIMISTIC LOCKS (for read/write cases) will help by a small amount. However, be aware that the most optimized database you can build will still face a bottleneck if you have a slow network.
The user who was physically closer to the shared drive saw faster response. This means that you have a very clear proof that the network's speed IS an issue and will have to be "lived with" for your database users who aren't so close.
As long as your network is all hard-wired, you should be stable if slow. If anyone starts using Wi-Fi connections however, you run a SERIOUS risk of corrupting the database. Unstable networks, because they can drop connections, can disrupt file transfers. But Access WORKS based on file transfers. That is the backbone of its operation. If you break the backbone, you've got huge troubles.
