While theoretically Access can support 255 simultaneous connections, in reality, bottlenecks arise at even 20 to 25 simultaneous users. In this article, we plan the deployment in a manner than 100 users can be simultaneously supported.
Many small businesses tend to grow over time both in business size and employee count. With Access being the preferred database for small business users, there are numerous cases where people using Access in their company suddenly found that their line of business application needs to handle numerous users at the same time. While some experts suggest dumping Access altogether if you have, say 50 to 100 simultaneous users, the choice may be difficult for small businesses. Creating another application from scratch that too with a different database platform can prove costly. In such scenarios, you should look at using MS Access in an optimal manner and a well-designed Access application can even handle 200 simultaneous users at one time. Just follow the roadmap below to ensure your Access application can handle multiple users at the same time
1. Split the Access Database and use privileges to restrict write access.
If you are looking to support numerous users, it is quite likely that you are using a split access database. In case you have not, immediately split the database with the front end user interface being deployed on user machines while the backend data repository been kept on a different machine. In case you need help on how to split your Access application, try visiting Microsoft’s support site.
In split database model, use user privileges to grant writing authority to a limited number of users while allowing read authority for all else. Not everyone in your office may need to input data and thus you can limit these people to view access only.
2. Design effective input forms and use proper validations
The amount of data that moves during concurrent sessions can affect the performance of an Access application. Ensure the input forms are well designed and do not contain useless data fields. If possible avoid attachment options in input forms.
3. Restrict size of graphics
In case you are using images, especially inputting image files, make sure you restrict their size.
4. Enforce correct Record locking options
In a multiuser Access solution, it is critical to choose the right record locking option. Choose the default open mode as Shared while you need to record locking default to Edited Record. To access these options visit the File Menu and go to the Client Settings in the Options tab.
5. Adhere to standard Access optimization best practices
To ensure the application works perfectly, standard Access best practices need to be followed. They include the use of temporary tables wherever needed, smaller indexes and regular compacting of the backend database.
Prepare for Contingencies
When your application is being used by several users at any point in time, chances of errors and database crash is always present. Despite your best efforts, you may not be able to prevent an MS Access crash. To avoid incidents of data loss due to corrupt accdb or mdb errors, consider keeping an Access recovery tool like DataNumen Access Repair handy.
Author Introduction:
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql fix and excel recovery software products. For more information visit www.datanumen.com