다음을 통해 공유

MS Access: 5 Tips to Make Your Database Fast

  • 아티클

Summary

The size of Microsoft Access increases with time and as it exceeds its storage limitations, the database management system starts encountering performance issues. Learning a few simple tricks can help in optimizing the performance of Access database and enhancing its productivity.  

The size of the primary database is one of the major factors that affect the performance of any Access database. You can reduce performance issues by keeping a check on the size of the database. Limiting the size of Access database also helps in enhancing its speed. The following post highlights a few simple tips to make MS Access database fast.  

Top tips to Make Access Database Fast: 

1. Normalize Access Tables

Normalizing Access Tables ensures that the data is stored in one place only. This helps in locating specific records in no time. 

Assign a Primary Key to every table: By assigning specific primary keys, users allow Access application to quickly locate specific records. Moreover, it is not possible to allocate a secondary key to a Table unless it has a primary key assigned to it.

Follow a pattern to assign a value to a primary key:

  •  As the primary keys define the uniqueness of a particular record, these can be assigned a numeric value with one field. 
  •  The value of a primary key should not be edited. Changes in primary key may have a ripple effect on other tables through secondary keys or via referential integrity. 

 

2. Keep an Open Handle to all the Linked Databases and Linked Tables

In Access database, you can increase the performance of linked tables, forms and reports by keeping these items open in the Access database. You can keep an open handle to all linked databases with the help of the following:

  • In visual basic code, open a database variable with the help of OpenDatabase method.
  •  Do not close this variable as long as the application is running.

By not closing the variable, Access is forced to keep the database open and ensures that the linked tables are accessed without loss of time.

Follow these processes to enhance the performance and speed of linked databases:

  1.  On linked tables, minimize record navigation by using only Page Up and Page Down movements.
  2. Create a data-entry form to add new records to the linked table. This prevents Access from retrieving the entire information in a linked table when you enter new records.
  3. Post Queries to limit and customize data: Filters and Queries limit the amount of data that needs to be read from the linked table.
  4. If a query is linked with one or more tables, avoid using functions or Domain aggregate functions (DLookup) in the query criteria. It is better for an Access database to fetch the records from function’s data source.  
  5.  If Access database is shared amongst multi-users, then multiple users may also try to edit the database. It is better to lock the records for as long as it is required. 

3. Manage the Size of Access Database:

Large databases may obstruct normal functioning of the application, so it’s better to manage the size of Access Database to ensure uninterrupted access. 

The following methods help in keeping a check on the size of an Access Database:

a. Split Access Database:

Splitting the database into an ‘application’ and ‘data’ database helps in enhancing the performance of Access Database. This can be done by splitting the database into two parts—

back-end and front-end. Back-end consists of the data database and front-end stores database objects such as queries, reports and forms on the local system. 

This will help in faster loading and execution of the Access database in a secure manner.

Note:

  1.  Back up the database before splitting.
  2. Splitting is a time-taking process, hence, inform the users before splitting. Any changes made by a user during splitting are not reflected in the backend. 
  3.  Everyone who accesses or uses the database must use the same version.  

b. Turn-off Animations:

Animations are good for enhancing user experience, but they tend to slow down the performance of the database. Keeping the interface simple and including how- to-use steps instead of animations can effectively enhance the UX and make the database faster.   

To turn off the animation, 

  1. Go to File and click Options.
  2. Access Options screen opens. In the left pane, click Client Settings. 
  3. In the Display tab, uncheck the checkbox for Show animations.
  4. Click OK.

 

c. Turn-off Non-Usable Action Tags:

Turning-off the unnecessary Action-Tags (which remain unused) in the database enhances the speed and increases productivity. 

To turn off the Non-Usable Action Tags, 

  1.  Go to File and click Options.
  2. Access Options screen opens. In the left pane, click Client Settings. 
  3.  In the Display tab, uncheck the following:
    1.  Show Action Tags of Datasheets
    2.  Show Action Tags on Forms and Reports
  4.  Click OK. 

 

d. Limit the Controls 

A simple form with less controls loads faster as compared to a complex form with innumerous queries. A form with multiple controls may take longer time to load and respond to queries. 

Make several specific tasks instead of using one complex form. 

e. Run Compact Utility in Access Database 

Using Compact and Repair utility on Access database, once a month, helps in reducing unwanted and unused objects and reclaim unused space in Access database. This utility may also help in repairing corruption in the database, re-organizes table records and updates statistics. However, it is advised to not use this utility in case of severe corruption.    

  1. 1. Open Access database and click the Database Tools menu. 
  2. 2. Click Compact and Repair Database.

f. Run Performance Analyzer

Performance Analyzer helps in optimizing the performance of Access desktop database. It helps Access users by providing recommendations to improve database performance and preventing data errors. 

It helps in analyzing a single Table or the entire database. 

The following steps will help in running Analyzer and ensuring database optimization:

  1.  Open Access database to be optimized, and go to Database Tools. 

  2.  In Analyze group, click Analyze Performance.

  3. The Performance Analyzer screen is displayed

     

  4.  Click Tables. The Analyzer will list all tables in the selected Access database. 

  5.  If there is a need to optimize all database objects in a single process, select the All Object Types option.

  6.  Select the name of database object for optimization. 

Note: Performance Analyzer doesn’t suggest methods to improve the performance. 

4. Optimize Access Database Objects – Forms and Queries:

  1.  The following tips help in optimizing Forms and makes Access database faster:
    1. Close the unused forms as every open form consumes more memory that could otherwise be used by other objects of Access database.
    2. If you have set the condition for opening the most used forms as soon as the application starts, then it may take more time for the application to load. Avoid such situations by setting the visible properties as False and make those Forms visible as required.
    3. Always base the Forms on Queries instead of Tables. This will help in restricting the number of fields returned, thus making the form to load faster.   
    4. Split the multi-page forms by using the page break character to help them load faster. 
  2. The following tips help in optimizing Queries and making Access database faster:
    1. It is advised to not base the records on Queries, which use ORDER BY clause. 
    2. Avoid those Reports that sort or group on expressions or functions.
    3. Index the fields that are used in sorting and grouping.
    4. Index the fields that are used to link a subreport to a report.
    5. Identify empty reports by using the report’s NoData event.

5. Defragment the Drive carrying Access Database:

Defragmenting the drive carrying database helps in freeing-up the disk space immediately. It can be done with the help of Disk Defragmenter. 

Note: Defragmentation should be performed: 

  1. Before compacting    
  2. During off-hours in the maintenance window when it is possible to take the system offline.

Hope you resolve Access database performance issue after performing above steps, however you may still encounter Access database corruption errors,. 

Conclusion

This post focused on enhancing the performance and speed of Access database by using a few simple tips. 

In fact, sometimes even after taking all the above measures to enhance performance of Access database, it becomes unresponsive, either due to a large or corrupt MDB or ACCDB file or due to an error.