Having had clients who have used Microsoft Access for over 19 years, I have seen more than my share of slow databases. They don’t start out that way. When they are new, with very little data, they run like the clappers. Then as the years go by, and more data and more users are added to the system the speed, especially of loading forms, gets slower.
What doesn’t work?
Before we look at how to speed up Microsoft Access databases, it is good to know what will not work, or things that don’t give as much speed for effort.
Upgrading the computer (Just for Access speedup)
It has been a very long time since Microsoft Access placed a huge load on the computer’s processor, or its hard drive. Since those days a computer’s internal speed has outpaced Access’s thirst for raw processing power. There are now better and much cheaper ways to gain improvements in speed.
Most computers are changed over every 3 to 4 years in any case, and this should satisfy any requirements Microsoft Access might have.
Clean out old unneeded data.
I never recommend that clients cleanse a database of old data.
The reason is I don’t believe any data that goes into a database is unneeded. If it is unneeded, then why was it collected and entered into the database in the first place? Some might say that the data is old and isn’t used any more. Maybe the data is time sensitive, such as booking information. To this I would answer that this historical data is an excellent source of information that can be used for many kinds of analysis. Trends can be established. Comparisons with current performance can be made. It is far better to keep the old data and try one of the methods below to improve speed.
With the above cautions having been stated, it will speed up the database, in conjunction with compacting the database.
There are cases where deleting data is required. A primary example is retail. A retail business does not need details of every can of baked beans sold from ten years past. However, overall sales data from that period can be valuable if the business was for sale. It could be used to show consistent sales, or a growth trend over a period of time. Again, I strongly recommend the data be archived to another database and backed up before it is deleted from the original database.
9 Microsoft Access Speed Improvements
Here are 9 ways to speed up multi-user Microsoft Access databases. They are in my preferred order of effectiveness.
1. Ensure the network is up to scratch
These days the network connection speed should be 1 Gb (Gigabit). It should be gigabit on the server and gigabit on the client. If not, purchase a new gigabit switch and gigabit cards for the clients and server. Do not even consider using Wi-Fi.
2. Ditch the Indexes
“But hang on; aren’t indexes meant to speed it up?”
Indexes will speed up retrieving records only when you search specifically using that index. When saving records it takes longer. Personally I would not use an index until I noticed a lag in searching for something vital like a surname, and then it would be a massive database on a full SQL server. I have seen DIY Access databases that have indexes for every single field. Needless to say the database was cumbersome to work with. The only index most Microsoft Access databases need is a primary index on the ID field.
3. Split the database into a front end and a backend
The quick way to do this is to copy the database using the operating system. Append “-data” to the end of one of the database names. Delete everything in this copy except the tables. In the original version delete all the tables.
Put the “-data” copy on a shared networked drive available to all users. Put the front end on all the users’ computers. Now link the two databases. This is the starting point for the next steps.
4. Compact the Database
Regularly compacting the database can keep it running smoothly. In the early days of Access it was vital to do this otherwise the database would corrupt and fall over regularly. Nowadays, compacting re-creates the indexes as well as checking the integrity of the database.
5. Install SQL Server Express
SQL Server Express is the limited, free version of Microsoft’s SQL Server.
For those who don’t know, SQL server is a backend database server. For Access users, it can store and serve your backend database to your clients. The front end of the database could still be Microsoft Access. The front end can be linked to the backend on the SQL server. No code needs to be changed.
The advantage of using an SQL server rather than a Microsoft Access database file is the speed.
In order to understand the speed difference, both methods should be explained.
With a Microsoft Access database backend,
- all the processing happens on the front end computer, and
- all of the data must be transferred to and from the backend, over the network to the front end, using extra, unnecessary bandwidth on your network and slowing down the application.
Alternatively with an SQL server,
- the front end (Microsoft Access) requests data from the SQL server, it sends the SQL query to the server.
- The server then processes the query, including all filter and sort operations.
- It then sends only the data requested back to the front end application.
SQL server minimises the network traffic.
All these processes make it very much quicker to retrieve the data from an SQL server rather a Microsoft Access database.
6. Tighten up all the queries
This will only work once the database is on an SQL server backend. All queries should restrict the data retrieved from the database to the absolute minimum needed for the user to perform the task at hand. This includes Access forms.
By default, forms, upon opening, will request all data in the table. Forms should be made to only request the single record that is needed. No more SELECT * FROM TABLENAME. Change it to SELECT FIELD1, FIELD2, FIELD_ETC FROM TABLENAME WHERE TABLENAME.TABLEID = 23.
This reduces the amount of data that must flow over the network, and thus speeds up the form and record loading dramatically.
7. Reduce the number of drop downs on forms
The other item that can slow down the loading of a form initially is drop down combos and drop down lists. If a form has a few of these; or worse, a whole lot, the form will load slowly, as it needs to load each full list for each drop down. Again, each list must be sent over the network. I acknowledge that this is more easily said than done.
8. Delay the loading of the drop downs
Using some programing code it is possible to restrict, or delay the loading of these drop downs until they are actually clicked on, or better yet, until the user enters the first three letters of the list item to be found. The latter truly makes the database fly as it only needs to retrieve a very limited amount of data when it loads.
9. Maybe it’s time to outgrow Access
There comes a time in some databases when it is time to wave goodbye to Microsoft Access. This could be either as a front end or as a backend or both.
The front end could be replaced with, amongst other languages, VB.Net, without too much of a learning curve for the developer. Having extensive experience in both Access and VB, I can create a front end much quicker and much more user-friendly in VB than in Access. The best reason to opt for a VB front end over Microsoft Access would be customisation and speed. In VB the programmer has easier absolute control over calls to the database. In Access you have to program against the way Access functions.
As mentioned above in point 5, SQL Server Express edition can replace the backend.
Is all this additional work really necessary?
Only if you want a fast database that will impress your clients.
Using all these techniques, I have taken a Microsoft Access database that took over 30 seconds to load one particular form, to instantaneous loading.
The beauty of Microsoft Access is that, contrary to many I.T. Departments’ belief, it is a scalable solution.