Can a split Access database tolerate 50 users?

[BittenApple]

Hello team,
I am going to split Access database among 50 people. If all of them (in a most improbable case) start working on the frontend, can this load be handled by Microsoft Access?
Please advise me.
Regards,
ttib

[Wendell]

Yes, an Access database is being used by as many as 250 users at one of our former clients. However, we always split the database into a front-end that is Access installed on the users' PC, and put the backend into SQL Server. An access back-end with 50 users would likely be very slow - in fact we use SQL Server any time we have more than 5 users. There are several reasons we prefer SQL Server in addition to performance. It is less prone to corruption, has automated backup features, and a robust security model.

With that many users, you will likely want to invest in a deployment manager so changes to the front-end can be automatically applied to workstations, either each time they start the database, or each time you deploy a set of changes to the front-end.

[BittenApple]

Hello Wendell,

I am going to split the database. The database doesn't involve in many activities, it has a several text boxes and one combo box. End user selects an item from combo box and populates the subform and edits the texts in textboxes on subform.

Do all the users have to have access into SQL Server? Or SQL Server resides on one machine only?

What is deployment manager?

Regards,
ttib

[HansV]

If most people will only view data, not enter or edit them, you might just get by with a backend Access database in a shared network folder, and a local copy of the Access frontend database for each user. But if several people need to enter/edit data, it would probably become problematic.

If you use SQL Server, the backend would run on a single server machine. Each user would again have a local copy of the frontend Access database. The difference is that the frontend now connects to SQL Server instead of to an Access backend.

[BittenApple]

Hello team,
They edit records only. They have a look up on Main form and a text boxes on subform. They need to edit the record.
Would be it hard to connect to a sql server sitting on a shared network drive? Or does that take only going through a wizard and entering some information that some of them might be provided by IT.
Regards,
ttib

[HansV]

Somebody needs to set up the SQL Server database for you, and give you access to it. After that, you can design tables there, or import tables from Access.

[BittenApple]

Thanks!

[BittenApple]

Hello team,
What is deployment manager? Is it available in Access or any other program?
Regards,
ttib

[HansV]

There are several programs/utilities named Deployment Manager or similar; some work with databases and some don't.

One such utility for Access is Total Access Startup by FMS Inc (not free!).
Another is Auto FE Updater by Tony Toews (not free either).

[Wendell]

The least expensive solution is to run a small batch file each time a user wants to start the database. That batch file simply copies the current production front-end from the server to the user workstation, and then opens the new copy. But for 50 users, that could generate some significant delays if all users start the database at about the same time (i.e. when they arrive at work). On the other hand, it beats having to manually copy a new production front-end to each of 50 workstations. You should also be aware of some issues with linking to the production back-end. For a tutorial on some of these subjects, see Why Split a Database?.

For a more general discussion on doing development and maintenance in a split database environment, you might want to read this thread on UtterAccess. Finally there are some issues with workstation setup and deployment should you end up using SQL Server for your back-end production database. If you make that choice, I suggest you start a new thread to identify those issues.

[BittenApple]

HansV and Wendell,

I have split a database before and I have put them in one shared drive in the Network. It worked perfect. I am going to go through it again. In the worst situation, I am going to email the front end to all the users and ask them to save it in their machine. Let us see how it will go. I will give updates soon.

So many thanks for the response and the thread link.
ttib

ttib