5 Common Mistakes Affecting Access Database Optimisation

Access Database Optimisation

if you are running an MS Access database and want to ensure you are making the most of it, there are some key steps you can take to get the most from your database.

Many of us don’t really think of a database as a living, breathing thing. So we don’t make sure the indexes are properly indexed, the constraints are enforced, the tables are defragmented, tables are split across databases (and we don’t really think about the ‘split’), and of course, we don’t really think about whether the database can be compressed.

So, let’s discuss some of the common mistakes that are hampering you from optimising your Access database to the fullest.

5 Common Mistakes with MS Access

Here are the 5 common mistakes users and developers need to be aware of and handle accordingly:

1. Designing your tables adding fields; developers don’t always consider the correct data type and their field size which allocates memory whether used or not. For example, a ‘Text’ field size can be set between 1 and 255 characters, equating to roughly 1 byte per character. If you have a reference number field that only requires 5 characters, then set this field size to 5 saving a maximum of 250 bytes per record. Multiply this by several thousand records, and you start to handle optimisation in your Access database.
2. I’ve seen tables that have over 100 fields in them with various data types and field sizes. The more fields in a table, the slower that table runs. It’s not the number of records that slows your database; it’s the number of areas in a table. Keep the number of fields to a minimum and set the best fit data type. If you have a table that potentially has many fields (say more than 20), then split it into two or more tables and have a join between them, calling the additional tables when required.
3. Queries can be really slow to run, especially with multiple table joins (RDBMS). The more tables joined with various criteria, sorting and using functions to calculate, the slower the query will run and can take well over an hour to run in extreme cases. Learning to index key fields (which is carried out in table designs) will dramatically improve the speed of the query. The setting, therefore, primary and secondary keys in tables is how you handle performance optimising your query.
4. Loading a form sometimes takes a while to display data and run general functionality, which is very noticeable for large volumes of data. To help handle performance, consider loading forms as unbound (with no data source) and using macros or VBA code to load filtered recordsets instead. You can still use a bound data source but filter it first (via a query) to help keep recordsets to a minimum, optimising the form.
5. Running reports will result in many pages before printing, and the preview and layout views can take a while to load and display. Again, like forms, consider reducing the record set with a deeper query combined with key fields indexed. Additionally, having sub-reports can cause performance problems, too, as each report has its own data source. Do not have more than one sub-report; two at the most, as it will degrade performance even more.

There are other reasons why your Access database will slow and other techniques to help optimise that all-important database. But the above five points will give you a good head start indeed!