Microsoft Access Database Performance Tips
Building complex company systems using Access Forms and Visual Basic for Applications (VBA) is a thing of the past.
Despite this the Microsoft Access database is still as popular as ever.
The Access Database is easy to create, easy to maintain and easy to query. Its reporting is superb. Microsoft Access is going to be with us for a long, long time.
Here is a collection of tips that will help in maintaining peak performance for your Microsoft Access database.
Avoid Domain Aggregate Functions
Domain aggregate functions such as DLookup are inefficient.
Compact Databases often to reclaim space and make operations faster.
Limit Fields returned
This will results in faster performance and reduced resource usage.
Limit records returned
Use filters or queries to limit the amount of data that needs to be read from any table.
Link on Primary Key Indexes
Whenever possible, use the Primary Key index instead of other indexes.
Each additional index slows updating, deleting and adding of records. Fields with identical data should not be indexed.
Fields used as criteria should be indexed. Index fields that join or sort data.
Use Optimal Data Types
Select the smallest data type for a field. This will decrease disk space usage and make data handling faster.
Remove embedded Images
The Images should be kept in folders, with the Table field containing the file path. Embedded OLE objects are a disaster waiting to happen.
Subdatasheets have a performance overhead when a Table is opened. Set this property to None.
Data should never be repeated in different fields. Unless, of course, denormalising makes for greater simplicity and ease of use.
Tables and Primary Keys
Every table should have a unique numeric primary key. This ensures efficient location of records. It is also a necessity for a SQL Server migration.
Tables and Relationships
Relationships allow joins to work faster. Relationships allow referential integrity to be implemented.