Microsoft Access Database: How To Speed Up a Slow Microsoft Access Database System
I found the following article which gives you some pointers on how to speed up a slow running Microsoft Access database application when being shared over a network (LAN) and your Access database is playing the role of ‘Client/Server‘ as part of your business IT systems.
Take a look, read on…
Microsoft Access Database: How To Speed Up a Slow Microsoft Access Database Running Over a Network
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.
Article Source: http://EzineArticles.com/?expert=Richard_Rost
Microsoft Access Database Tutorial: My Additional Comments
The above article is a good start and covers the basics for you to check for performance leaks in a network shared environment. Microsoft Access databases are not conventionally designed to be used as your ‘high-end‘ database application but with some careful planning, can behave as one keeping your costs to an absolute minimum.
There are two main aspects to maintaining an optimised and fast performing Access database which are:
- IT networking – dealing with traffic and concurrent users (hardware)
- Good functional design – within Microsoft Access using forms with VBA correctly (software).
Each item is normally handled by different members who work as a team utilising their skills together.
First step! Learn and master Microsoft Access database and I suggest you take some action and look at my essential eBook bundle offer containing six eBooks at a reduced special offer price. It also comes with a money back guarantee and email support.