Microsoft Access is the perfect tool for building databases for companies of all sizes. However, when it comes to sharing that database over a network, amongst multiple users, many database administrators soon discover that as the number of users working on the database increases, the performance of the database greatly diminishes.

The problem in situations like this is usually due to a network bottleneck. If you have, for example, twenty users all working on the same database, all of that data traffic is moving across your network. If you’re running an older network, you might want to see if you can upgrade your infrastructure to at least Gigabit ethernet (wiring, router, network cards, etc.). This will increase your performance significantly.

Next, you should take a look at the processing power of your server. Many companies set up an old machine as their “database server” and expect it to be fine. It might work for a handful of casual users, but if you’ve got an underpowered box acting as a de facto database server, it might not be able to crunch the data fast enough for you. A database server needs a fast processor and lots of RAM in order to operate efficiently. Try running the database locally, on the server machine itself. If it’s still sluggish, then you should upgrade.

If neither of those solutions helps, you might want to take a look and see if you can optimize your database itself for network traffic. For example, if you have users doing a lot of data entry, set up forms that are only for data entry (use the DataEntry property in the form design). This way when you open up a customer form, for example, Access doesn’t have to send all of that customer data down to the desktop. It will just place the user on a new blank record.

Also, if you have reports that are based on big, complex queries, you might want to see if you can simplify them to make them load faster. See, one of the things that a database server program (like Microsoft SQL Server or MySQL) does is that it performs all of the data crunching at the server itself. For example, if you request a query of all of your customers from New York, the server figures it out and just sends you those few records. With a non-server solution (just an Access database) the local copy of Access has to pull all of those thousands of customer records across the wire to your local PC and then figure out who the customers from New York are. This greatly increases the traffic on your network, slowing things down even more.

So, as you can see, there are a couple of things you can look at. While upgrading to a newer version of Access may speed things up a tiny bit for you, it probably won’t help your speed issue. In fact, if your computers are older, it might even make things worse. The newer versions of Access have higher system requirements than the older versions do.

To resolve the problem, look at your network bandwidth first, the processing power of your “server” machine second, optimizing whatever you can in your database third, and finally if all else fails consider upgrading to a real database server. Microsoft SQL Server is a little pricey, but there are free solutions available like MySQL.

Richard Rost is President of 599CD Computer Training and Access Learning Zone.