Microsoft Access Database: How To Deal And Dispense The Myth That Access Can Not Handle Multi-Users

If I had a Pound (or dollar) every time I heard that Microsoft Access is not good enough when dealing with multiple ‘concurrent’ users and that performance degradation and locking issues are the main symptoms when trying to share an Access database; I’d be able to retire and give more of my time back into society which really needs my help!

In 2004, I was asked to build an Access Database that would handle over 30 users to utilise and place orders, processing them and have other members fulfil the goods in a manufacturing module, First of all, I had to establish not how many users but how many concurrent users would be expected to use the Microsoft Access database application which the client confirmed 25-30 users would were needed to hit the same database at the same time.

There are a lot of opinions whether Microsoft Access can actually handle multiple users and large volumes of data and for sure there are pros and cons (which is not going to be covered here), There’s a saying in any field of expertise “Put 7 experts in a room and ask them for an opinion and you will get 21 different answers.“

My view is simple; try Microsoft Access first and then at least if you need to move to a bigger application then you can move on knowing that at least you gave it a go!

So in order to help you along, there are 5 reasons to deal and dispense with the myth why Access can be used in a multi-user environment.

1. The most popular action to take is to split the Access database into two parts. Have all the tables in one database and the remaining objects in another. Make sure you store the ‘Tables’ database (commonly known as BE- Back End) on the server or shared network folder with the other database (commonly known as FE – Front End) on each user’s desktop which has a link to the BE database.
2. When designing your tables, learn to set indexing to the commonly used fields and not just set the ‘Primary’ key in a table. The other type of key often overlooked is known as the ‘Foreign’ key. This all helps with performance and speeds up the queries in Microsoft Access.
3. Loading forms can be memory intensive and to help the performance and avoid locking consider opening a form as ‘unbound’ to the data source. You can then populate the data via a macro or VBA code to handle the data when required.
4. Using forms and reports is an overhead that can be easily controlled (and is one reason why they are stored on the desktop). Keep the number of controls to a minimum which means fewer tab pages and unnecessary text-boxes to hold calculations and other hidden values. There are better ways to handle this but will require some simple VBA code knowledge.
5. Use ‘local‘ temporary tables to handle offline processing which can then be sent to the BE database as and when required helping to keep traffic as free as possible.

There are other techniques that can be added to help handle multiple ‘concurrent‘ users but first had experience says you can use a Microsoft Access database across a network as a simulated Client/Server database application.