Performance Tips for Faster Microsoft Access Databases

Hi All,

For those who work with Microsoft Acces, here you can find really great tips to improve it’s performance.

Over 100 Tips for Faster Microsoft Access Databases

This article outlines specific performance tips you can use to improve the speed of your Microsoft Access applications. These tips apply to all versions of Access including Access 2016, 2013, 2010, 2007, 2003, 2002, 2000, and 97.

Some of the tips may contradict each other but they are offered because bottlenecks differ in each situation. Some tips may make things run faster on your system, while others degrade performance. You should evaluate each tip as it applies to your specific application running on your specific hardware.

https://www.fmsinc.com/microsoftaccess/Performance.html

For example, it has a great tip when working with Linked Tables: Keep an Open Handle to Every Linked Database.

This will significantly increase the performance of opening tables, forms, and reports that are based on linked tables by having Microsoft Access keep the linked table’s database open.
To do this, open a Database variable in Visual Basic code using the OpenDatabase method. Keep this variable open as long as your application is running. This forces Access to keep that database open, making access to linked tables much faster. For more information, it has an exclusive tip on Increasing the Performance of Linked Databases

Another great tip (I recommend reading all tips!) is to avoid using Domain Aggregate Functions: If a query is based on one or more linked tables, avoid using functions (built-in or user-defined), or domain aggregate functions in the query criteria. When you use domain aggregate functions (such as DLookup), Access must fetch all records in the function’s data source to execute the query.

image