Building an Access database requires some time, effort and ideally a good level of database knowledge though Microsoft Access provides a wealth of tools to assist and get you up and running in minutes.
But some of the assist tools do not take on board and control some of the pitfalls most developers tend to ignore and brush aside ultimately leading to a degrading performance and dramatically a slowing of the database.
Optimization is a methodology that must be seriously considered and applied more so for the larger database file in order to control and increase performance.
Here are the 5 common mistakes users and developers need to be aware and handle accordingly:
- 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 which equates to roughly 1 byte per character. If you have a reference number field which 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 optimization in your Access database.
- I've seen tables which have over 100 fields in it with various data types and fields 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 fields in a table. Keep the number of fields to a minimum and set the best fit data type ( and where applicable field size ). 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 really required.
- Queries can be really slow to run especially with multiple table joins (RDBMS) are in use. The more tables joined with multiple 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. Setting therefore primary and secondary keys in tables is how you handle performance optimizing your query.
- 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 use 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 optimizing the form.
- Running reports will result in many pages prior to printing and the preview and layout views can take a while to load and display. Again, like forms, consider reducing the recordset 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 optimize that all important database. But the above five points will give you good head start indeed!