Splitting the application into a frontend (Programs, code, queries) and the backend (Data Tables) is always a good programming concept. This is almost always true (particularily if the backend is updated by user(s). The only type of exception might be an Access application that only does reporting or saves no data.
Here are 3 good reasons to split:
1) If your data and forms are all stored in one file, then maintenance of forms and code becomes a nightmare. Yopu should never modify forms that users are currently running. And if you make a copy to develop and test, then the live data is not the same as the test data. So when your new version is complete, you need to import the live data. Much better if you just link to your data, test or live.
2) The form and code part of an Access mdb is much more likely to become corrupt, where the data is less prone to that. So, if split, then you would always have a good copy of the Frontend and the data backend will run more efficiently.
3) When Split, each user can run the Frontend on their local PC. This is more efficient and depending on how you have coded your program, the only way to insure data integrity.
Regarding running on local PC, each user running their own copy. Well, if you use global variables, or temporary work tables, then if user 1 builds a temp table to produce(say a report), well if user 2 runs the same report as user 1 with different criteria, well results could be unpredicable. This is just an example.
Regarding users, I have had good luck with up to 50 users all accessing an Access backend, provided that not all users are updating the same tables all at once. Just depends on your user needs and how you program the Frontend. In theory, access can have 255 users open the same mdb file at once, but don't try it. I have had one company where 10 users were updating the backend, but close to 100 users were connected and reporting.
Hope this Helps,
Hap
Access Developer
Access based Add-on Solutions
Access Consultants forum