This article contains tips for improving the performance of a Microsoft Office Access database. By following these tips, you can help speed up many database operations, such as running reports or opening forms that are based on complex queries.
One of the best ways to improve the performance of a database is to create indexes for commonly used fields. By creating indexes, you can improve performance more than you can by using any of the tips in this article. Access automatically creates some indexes for you, but you should carefully consider whether additional indexes will improve performance.
This article does not discuss ways to optimize the performance of specific database objects, such as by creating an index. For more information, see the article Create and use an index to improve performance.
In this article
Help improve performance of a local database
The following guidelines can help you optimize the performance of a local Access database — a database that is stored on a local hard disk drive, rather than on a network.
Turn off Name AutoCorrect
The Name AutoCorrect feature helps ensure that the functionality of database objects remains intact when other database objects that they depend upon are renamed. For example, if you rename a table and there are queries that use that table, Name AutoCorrect makes sure that those queries do not break because of the change. This feature can be useful, but it does slow performance some.
If your database design is stable and its objects will not be renamed, you can safely turn off Name AutoCorrect to improve performance.
-
Open the database that you want to optimize.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane of the Access Options dialog box, click Current Database.
-
In the right pane, under Name AutoCorrect Options, clear all the check boxes.
Set the database to compact and repair automatically
Over time, the performance of a database file can become slow because of space that remains allocated to deleted or temporary objects. The Compact and Repair command removes this wasted space and can help a database run faster and more efficiently. You can set an option to run the Compact and Repair command automatically when a database closes.
-
Open the database that you want to optimize.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane of the Access Options dialog box, click Current Database.
-
In the right pane, under Application Options, select the Compact on Close check box.
Open the database in exclusive mode
If you are the only person using a database, opening the database in exclusive mode prevents other users from using the database at the same time and can help improve performance.
-
Start Access, but do not open a database. If you already have a database open, close it.
-
Click Open and then click Browse.
-
In the Open dialog box, select the database file that you want to open. You can use the Look in list if you need to browse to find the database file.
-
Click the arrow on the Open button, and then click Open Exclusive.
Turn off AutoCorrect options
By default, Access corrects your spelling as you type. You can turn off the AutoCorrect feature to help improve performance.
-
Open the database that you want to optimize.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane of the Access Options dialog box, click Proofing.
-
In the right pane, under AutoCorrect options, click AutoCorrect Options.
-
In the AutoCorrect Options dialog box, clear the check boxes for the options that you don't want.
Note: You do not need to turn off all the AutoCorrect options to see a benefit, but the more AutoCorrect options that you turn off, the bigger the benefit.
Help improve performance in a multiuser environment
The following guidelines can help you optimize the performance of an Access database that is used in a multiuser environment.
Split the database
When you split a database, you put the data tables in a database file on a network server in what is called a back-end database. You put the other database objects, such as queries, forms, and reports, in another database file that is called the front-end database. Users keep their own copy of the front-end database on their computers. Performance will improve because only the data is sent across the network.
You split a database by using the Database Splitter Wizard.
-
On the Database Tools tab, in the Move Data group, click Access Database.
Change the page-level or record-level locking setting
Access locks a certain amount of data while you edit records. The amount of data that is locked depends on the locking setting that you choose. You can help improve performance by choosing page-level locking. However, page-level locking may decrease data availability, because more data is locked than with record-level locking.
-
Page-level locking Access locks the page that contains the record (the page is the area of memory where the record is located). Editing a record with page-level locking enabled might also cause other records stored nearby in memory to be locked. However, performance is generally faster when you use page-level locking instead of record-level locking.
-
Record-level locking Access locks only the record that is being edited. Other records are not affected.
Change the page-level or record-level locking setting
-
Open the database that you want to adjust.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane, click Client Settings.
-
In the right pane, in the Advanced section, select or clear the Open databases by using record-level locking check box.
Choose an appropriate record-locking setting
Access locks records while you edit them. The number of records that Access locks and the amount of time those records are locked depends on the record-locking setting that you choose.
-
No locks Access doesn't lock a record or page until a user saves changes to it, which results in data being more readily available. However, data conflicts (simultaneous changes being made to the same record) can occur if you use this setting. When a data conflict occurs, the user must decide which version of the data to keep. This is generally the fastest option, but data conflicts may outweigh the performance gain.
-
Edited record Access locks a record as soon as a user begins to edit it. As a result, records are locked for longer periods of time, but data conflicts are less likely.
-
All records Access locks all the records in a table while any form or datasheet that uses that table is open. This can improve performance for the user who is editing data in the table, but it restricts other users' ability to edit data more than the other options do.
Change the record-locking setting
-
Open the database that you want to adjust.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane, click Client Settings.
-
In the right pane, in the Advanced section, under Default record locking, click the option that you want.
Adjust the network refresh and update settings
Try adjusting the Refresh interval (sec), Update retry interval (msec), Number of update retries, and ODBC refresh interval (sec) settings, as applicable.
You use the Update retry interval and Number of update retries settings to specify how often and how many times Access tries to save a record when it is locked by another user.
You use the ODBC refresh interval and Refresh interval settings to control how often Access refreshes your data. Refreshing only updates data that already exists in your datasheet or form. Refreshing does not reorder records, display new records, or remove deleted records and records from query results that no longer meet specified criteria. To view such changes, you requery the underlying records for the datasheet or form.
Change the network refresh and update settings
-
Open the database that you want to help run faster.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane, click Client Settings.
-
In the right pane, in the Advanced section, change the settings that you want.
Tip: To requery, press SHIFT+F9.
Help improve Access performance on your computer
The following guidelines can help improve Access performance, regardless of whether the database with which you are working is stored on your computer or on a network.
Turn off user interface animations
The Access user interface includes animations, such as when menus open. Although these animations help make the interface easier to use, they can slow things down a bit. You can turn them off to help improve performance.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane, click Client Settings.
-
In the right pane, under Display, clear the Show animations check box.
Turn off action tags
If you don't use action tags, turn them off to help improve performance.
-
Click File > Options to open the Access Options dialog box.
-
In the left pane, click Client Settings.
-
In the right pane, under Display, clear the Show Action Tags on Datasheets and the Show Action Tags on Forms and Reports check boxes.
Close other programs not being used
Closing other programs makes more memory available to Access, which helps minimize disk usage and improves performance.
Note: You may also want to exit some programs that continue running in the background after you close them. Look in your notification area for any such programs. Be careful when exiting these programs, because some programs may be necessary for your computer to function as you expect. If you have any doubt, you probably should not exit these programs.
Add more RAM to your computer
Adding RAM to your computer can help large queries run faster and can allow you to have more database objects open at once. Moreover, RAM is much faster than virtual memory on a hard disk drive. When you add RAM, you help minimize disk usage and improve performance.
Clean up your hard disk drives
Periodically perform this set of steps:
-
Delete files from your computer that you no longer need.
-
Delete your temporary Internet files.
-
Empty your Recycle Bin.
-
Run Compact and Repair operations on your databases.
-
Defragment your hard disk drives.
Disable Windows services that you don't require
For example, if you have a good desktop backup solution in place, you should consider disabling the System Restore service. Disabling Microsoft Windows services that you are not using makes more RAM available for Access.
Important: If you decide to disable Windows services, keep track of what you disable, so that you can easily re-enable any Windows services that you decide you do require.
Adjust your virtual memory settings
In most cases, the default virtual memory setting used by Windows should perform optimally. However, in some situations, adjusting virtual memory settings can improve Access performance. Consider adjusting the default virtual memory settings in the following cases:
-
You don't have much disk space available on the drive that is currently being used for virtual memory, and another local drive has available space.
-
Another local drive that is faster than the current drive has available space and is not heavily used.
In these cases, you might get better performance by specifying a different drive for virtual memory.
You also might get better performance by specifying a fixed amount of disk space for virtual memory. Consider specifying 1.5 times as much virtual memory as the amount of RAM that is installed on your computer. For example, if you have 1,024 megabytes (MB) of RAM, specify 1,536 MB for virtual memory.
Note: You may want to specify more virtual memory if you often run several large applications simultaneously.
For help with changing virtual memory settings, search Windows Help for "change virtual memory".
Don't use a screen saver
Screen savers use memory and start automatically. Because of refinements in the design of monitors, screen savers are no longer needed to help protect your monitor from "burn-in." You can boost performance a bit and help your computer run more smoothly by not using a screen saver.
Tip: Don't rely on a screen saver to protect your computer from unauthorized access. To help protect your computer when you step away, press Windows logo key+L.
Don't use a desktop background
You might see some improvement by setting the desktop background to (None).
