Optimize Microsoft Access Reports for better Database Programming
Some pro tips as suggested by database programming experts to make sure that your database runs faster.
Normalize tables
The first and most crucial thing is to normalize your data. Normalization is the process of organizing data in a database. It minimizes redundancy and also eliminates unwanted characteristics like Insertion, Update and Deletion Anomalies.
Tables must have a primary key
Each table must have a primary key — a table without a primary key is like a person without an identity. Using the primary key you can quickly locate precise records in your database application. Moreover, without it, you cannot create secondary indexes on a table’s fields.
Split up your database
If you are sharing a database with other people on the network, you must split it. To improve performance and reduce the chance of database file corruption, using Access Database splitter to split database is really helpful. Copy the frontend file to your desktop and leave the data file on the network — this also improves the speed of the application.
Load only what’s needed
Avoid loading the data from the very beginning every time a person opens an application. Every user need not load everything, therefore load forms with the use of a “where” clause which shows only the data that the user needs.
Get Office Accelerators’s stories in your inbox
Join Medium for free to get updates from this writer.
Select the apt data type
Apt data type decreases the disk space required for storing data, and the time taken for the application to function, i.e. to retrieve, manipulate, and write data. The best practice is to pick the smallest data type possible for storing a specific type of data.
Take benefit of the Cache
Whenever possible, make use of the cache memory particular for using external SQL data sources. There is an automatic caching feature in Microsoft Access reports and forms. For maximizing cache effectiveness, use the CacheStart, CacheEnd and FillCache methods while using recordsets in your VBA code.
Reduce the size of graphics
In case you insert graphics on your reports or forms, the recent version of Access (starting from Access 2007) are more competent. To minimize the size of your database, Access converts the graphics into much smaller PNG formats. And it doesn’t impact graphics already used on your forms and reports but it makes more space for new graphics. To activate this feature click on the Access Office button, select Access Options, Current Database. At the bottom of the Application Options section, you will see Picture Property Storage Format. Set it to Preserve source image format (smaller file size).
Base Reports on Queries-Minimize Fields Returned
Let your reports and subreports be based on queries and not on tables. Using a query, you get to control the number of fields returned to the absolute minimum number, and make data retrieval process faster.
Remove Unnecessary Reports
Do not keep unnecessary reports in your database. In case the query of a subreport is same as its parent report, or even if the query is similar, try to remove the subreport and place its data in the main report. This might not be possible always, but it improves performance greatly.
