Speed up your Access Database on a network connection with these tried and tested techniques from a database professional with over three decades of experience.
A slow Microsoft Access database on a network is painful for the staff who have to use the system. It makes using it, a tiresome, burdensome chore. Unnecessary slowness causes stress, which your staff could pass to your customers by way of expressing their bad feelings. How often have you waited at a counter while the staff member operating the computer firstly enters your name, then waits, while the screen loads, apologises to you for the delay caused by ‘the computer’. You can imagine what this poor staffer is like at the end of the day.
The good news is that it can be solved.
I have previously written about resolving slow Microsoft Access Databases over Wi-Fi connections. This article deals with slow Microsoft Access databases over wired connections
Two things that don’t work
1. More grunt
Don’t bother spending money to upgrade the computer, the speed gain will be minimal to non-existent at best, unless the computer is ancient.
2. Deleting old data
Some people like to clean old data out of a database. I never recommend it. There are a couple of reasons for this. First up, if information was worth someone’s effort to enter into a database why delete it. It must have been valuable sometime. Then isn’t it still valuable now? Historical data can be used to report trends. These can show patterns such as growth. When selling a business, or reporting on key performance indicators such data is immensely valuable.
Here then are …
10 Ways to Speed up Microsoft Access on a Network
Here are 10 ways to speed up multi-user Microsoft Access databases. They are in my preferred order of effectiveness.
1. A Fast Network Helps
Your computers should be connected to your network with a wired connection, usually a blue cable. All network hardware, the router or switch, the computer network interface card (NIC) should be rated as 1 Gigabit. You can usually check this by holding the mouse pointer over the network symbol on the task bar, or double clicking that symbol. This is the only hardware I recommend buying in order to speed up Microsoft Access databases.
2. Index only where necessary
When a Microsoft Access database grows in size, indexes can help if used correctly. The first thing I do when I take over a database is to examine all of the indexes, and ensure the ID field for each table is on the primary index and that there are indexes for ordering the tables used in drop down lists on forms.
A word of warning: Indexes make finding the record faster, on huge databases, but it also makes it slower to save, as the index also has to be updated for each new record. I have seen databases where every field is indexed and that is overkill.
3. Separate the forms, queries and reports from the tables
Microsoft Access databases should be split into a front end and a backend. Rather than go off topic here read all about splitting the database.
Once it is split use our automatic Microsoft Access table re-link tool so that it will automatically reconnect when it is updated on users machines.
4. Sub-forms slow speed
Sub forms are forms within forms. Some sub forms even have their own sub forms.
Each time a sub-form is used it requires another call to the database. While they might look great on screen, sub-forms slow the speed of the database due to the additional requests for data. This is especially noticeable on networked computers, but not so much on the developers computer. Each time a sub-form is opened another call to the database to pass more data.
Forms should be redesigned without using sub forms if at all possible.
5. Compact the Database
Occasionally compacting the database can help, particularly if it hasn’t been done in a while. Still it doesn’t hurt to do this regularly, say once a month is sufficient.
6. Catch the SQL Express
When there is too much data, or too many users it is time to outgrow Microsoft Access, at least on the back end. The easiest way to move to the next step is to install the free version of Microsoft SQL server, which is called Microsoft SQL Server Express. This is where the wheels meet the road, or the train tracks. This step will give the biggest improvement in speed along with the next step. It is not complicated, but you will require administrative privileges for the computer to be able to do it. There are wizards available to convert your database. If moving your data, don’t forget to make sure the new data locations are included on the backup.
7. Tighten up all the queries
Once the data is transferred to a SQL server, improvements to individual forms and queries can be made. The queries from which the form derives its data should be tightened, so that instead of retrieving every record in a table, it only loads a single record. For example, instead of select * from employees, the query should be select * from employees where employeeID = 123. Of course this involves having a good search beforehand so that the form is passed the employeeID before loading. Once you have done this a few times it becomes natural.
If you were pedantic then you could have select employee.given, employee.surname, etc from employees, But for me this then takes it longer to program, to make simple changes to the program, and in most cases when grabbing a record for a particular screen the majority of the data in the record is required.
8. Reduce the number of Combo Boxes on forms
So having reduced the loading of all records in the table to just one record we now need to cast our attention to the combo boxes, or more accurately the choices that sit in those combo boxes or drop downs boxes. For each combo box, the choices need to be loaded from a table via a query. This happens when loading the form. So that means instead of one query being executed when loading a form, one query for the form record, plus one query for each of the combo boxes is executed. In the case of the combo boxes however it is not one record that is being read, but all of the records in the combo’s query. If there are many records this can really bog down loading the form. Ask yourself is the combo box really needed? If no, then change it. If yes then that’s perfectly ok, but go on to the next step.
9. Just Stop … loading the combo boxes
Having established that the combo boxes on your form are indeed vital there are still tricks to speed up the form for the users. When the form first loads, there is no need to load all the combo boxes. The combo boxes only need the actual record stored in them. They don’t need all the rest of the records that you see when the combo box is clicked on, unless the user actually clicks on a particular combo box. This means instead of loading tens of records for each combo box, only one record need be loaded. If the user clicks into a combo box to change it, use the GotFocus event to load the combo’s records then.
In the case of creating a new record, preload all the combo box , but load time has already been saved as the system is not loading the main data record.
This is advanced programming, but there is no need to shy away from it. It makes your forms load like lightening.
10. Maybe you have outgrown Access?
In the previous steps we have moved away from Microsoft Access for the backend database. Maybe now it’s time to consider moving away from Access for the front end as well. There are other programming languages that can deliver even faster databases that are free to use, and just as quick if not quicker to get a beautiful database up and running. Some of them will even convert Microsoft Access Forms. Moving away from Microsoft Access lifts the heavy restrictions on the way Microsoft Access works. It is possible to create complete executable programs, that are much more user friendly. They also have less worry that a user may change some of the programming and don’t need the overhead of installing Microsoft Access on a user’s computer, something which I.T. Departments loathe.
Is all this additional work really necessary?
Only if you want a fast database that your users will love using.
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.
