I can't be in a unique situation, yet I don't understand how splitting my db would help me. At work we have a database used by ~30 people. It currently is one .mdb with no security measures implemented. It sits on a network drive and everyone clicks on it when they need to use it. Never more than 7 or 8 users in it at the same time, but everyone could be, conceivably. No problems with it running the way it is.
I am rewriting the database to streamline it (4 queries vs. 40+, etc...) and add in security accounts/permissions. That's all handled. I will have everyone put a shortcut on their desktop to temporarily join them to the Workgroup and open the database (still one .mdb on the network).
How will splitting the database in this case benefit me? I understand the advantages of data seperate from forms & reports leading to less data having to be pushed across the network. What I'm seeing that if I have to put a front end on everyone's desktop, then every time I need to make a change to the front end, all 30 users will have to copy a new front end to their workstation. That doesn't sound nearly as efficient (maybe the wrong word) as just kicking everybody out of the database and making the change.
Please let me know if I'm wrong here. I would love to be wrong if splitting the db would make things easier/better, but I just don't see it. Thanks for the help,
If it works for you at the moment I wouldn't bother changing the set up you have. I wouldn't recommend having all 30 users connect to the database at the same time - Access struggles if you have too many users connected.
Splitting a database is useful for a number of reasons - restricting access to a limited range of forms/users, slow network connections, database maintenance. Its best use is in a proper client/server environment where Access is used as a front end to a database server such as SQL Server
if you split the database you could still keep both parts on the server or you could put the front end on peoples desktops and use replication or write your own database update routine to keep users upto date with the latest version
There are several utilities available that will keep the user's front end up to date - some from the Microsoft gurus. Just search for "access front end update".
The best solution for us was a batch file that copies the front end to the workstation every time the user opens the database. Our front end is small enough that the users don't notice any lag in loading times.
When I make changes to the front end, I just drop the "new" one into place without having to kick the users out. The users see the changes the next time they open the database.
If you do split it you don't necessarily need the front end on every desktop - you can put each front end on a network drive. I have a database split with four copies of the fron end in one network folder, each used by a different user. I make changes and copy them to each front end. The reason I do this? I have found serious issues with not splitting the database this way, both on performance and stability. I have temporary tables (for letter generation) in the fron end, each user has her own table and can access it without problem.
You will find that splitting the database can be more convenient for you and your users. You can split your database and keep both pieces on the server. The benefit is that you don't have to copy the front end to each individual workstation. The other benefit is if there is a problem, then you can copy the front end to your system, make the change, and then at your convenience replace the front end with an updated front end. You will have to make sure everyone is out, but the will only need to be out for the time it takes to copy the front end.
You can also have a copy of the back end on your system. Which will allow you to put in test data and not worry about cleaning it up.
I also have databases that are split and each front end is on each workstation. The benefit for me in these situations is I can put a table in the front end that is used to save parameters, like a date range or the ID for a report. When I change the data in those tables to display different information in a report, I don't have to worry about 'timing'. If you try to do this in the backend and two users run a report with different date ranges it can be problematic.
Splitting a database does not really improve performance, but having two (three or four, you can put each table in a different database if you want)smaller databases can improve maintenance issues, like compacting and repairing.