Building Access forms is a fun part of the Microsoft Access database build plan but it’s also the most challenging and can be time consuming too especially when you lose track of time getting carried away with all the fun
yes, it’s fun for me anyway!
Here are my 5 useful tips when building Access forms which are designed to help speed up your Access database application as this is one of the biggest areas which can slow that database system down.
- What type of Microsoft Access form are your building? Will it be a data entry form or a read only form? If you are going to create a read only Access form, then you have an opportunity to lock and switch off (set to false) various properties which, includes ‘AllowEdits’, ‘AllowDeletions’ and ‘AllowAdditions’.
- Keep controls (i.e. Textboxes, Labels and Buttons) to a minimum. The more controls and formatting in an Access form, the longer it can take to load and slow down. A busy looking form is not easy to navigate and use so why over complicate.
- Loading a form with the ‘Filter by Form’ option enables will be slower than leaving it as not enabled. You can always filter data via the query, SQL statement or use either a macro or VBA code attached to the load event of the Access form.
- Using sub-forms can be a great way to load various different processes into one view but the downside that each sub-form has its own dedicated data source (table, query or SQL) and this will take longer to load than a single form. If you are going to use a sub-form or two then keep consider loading a filtered recordset (i.e. do not show all records on loading). Each sub-form should be linked to the main form which will also filter records.
- Calculating when building Access forms also will slow down the record navigation and other events to the form as the expression call the (refresh) recalc method and an expression could be complex and use nested functions causing it slow further. Using VBA and populate values is a good compromise.
Microsoft Access Database: 5 Useful Tips When Building Access Forms – Final Thoughts
There is some confusion when checking and dealing with performance related Access database applications and another area sometimes leads to the data source regarding indexing a table and across a network (LAN).
This article doesn’t cover this area but it should be investigated especially if you are still experiencing performance issues when building Access forms.
Where next? Take a look at me eBook offers which covers various subjects from tables indexing through to form designs. All come with a 100% money back guarantee, what do you have to lose?