Microsoft Access - Access Performance -- split database on intranet shared drive

Picture, if you will, an Access 2007 db, split with FE and BE stored in separate folders on a shared drive.  The FE contains all the VBA, queries, forms, etc; the BE, only tables.

As I understand it -- and please correct me if I misunderstand it -- when the FE is opened by the user, it is opened locally on his machine -- I presume in RAM and on his C: drive. 

I further understand that if saved queries are run from the FE, they run locally and transact with the linked tables over the network, subjecting them to network latency.  Likewise any VBA-based queries.

It would stand to reason that, ideally, the data and the objects requiring the data should be as close together as possible in order to reduce latency and also to prevent any Access crash issues if the the network connection is interrupted.  I realize this is heresy to the "split-database" dogma, but nonetheless, it is the only conlusion I can draw.

If that is true, then it would be best to either a) run all queries in the BE or b) pull the necessary data into temp tables in the FE which will presumably reside locally with the FE and then query those.

Would appreciate any insight into this process, and if anyone wants to tell my how SQL Server will make life easier in this regard, I'm open to hearing that, too.

Yep, this is the core issue with using Microsoft Access as a shared database over a network drive.  It just doesn't perform well.  The issues will get worse with more users and more data.  If you have less than 5 users and a fairly small database, you'll be fine.  If your database gets into 100's of megabytes, start looking for trouble.

SQL Server is the preferred alternative to multi-user client server arrangements like this.  At least for professional corporate applications.

Thx, Robbe.  This db as max 3 users, almost never concurrently.

Just make sure you get all your indexes done right and you should be ok.