The information in this article applies to:
- Microsoft Access version 2.0
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article contains tips for optimizing the performance of Microsoft
Access version 2.0. Much of this information is taken from the Microsoft
Access 2.0 Help file.
Note that the optimal setting for each item may vary with the type of
computer on which you run Microsoft Access. It is usually best to change
only one setting at a time and then monitor database performance for
improvement.
MORE INFORMATION
To optimize the general performance of Microsoft Access version 2.0:
- Use the Add-in Manager to uninstall library databases that contain
Microsoft Access Wizards, builders, and other add-ins you do not want.
This reduces Microsoft Access memory consumption and load time.
- Make more memory available by closing applications and terminate-
and-stay-resident (TSR) programs that you are not using. Usually,
these applications are loaded from the AUTOEXEC.BAT and
CONFIG.SYS files.
- Make sure your Microsoft Windows virtual memory (swap file)
setting is large enough, and of type "permanent" rather than
"temporary." In general, the virtual memory setting plus available
RAM should be no less than 25 MB. It should be more if you will be
running several memory-intensive applications simultaneously.
To check or change the virtual memory setting, start Microsoft
Windows Control Panel. Double-click the 386 Enhanced icon, then
choose Virtual Memory. To change the setting, choose Change. Make
sure to select Permanent in the Type box. Choose OK to save your
changes. Please see the Microsoft Windows "User's Guide" for more
detailed information on virtual memory settings.
- Periodically run a disk defragmentation utility such as MS-DOS
version 6.0 Defrag to keep files in contiguous clusters on your hard
disk, making file access quicker in general. If you do not
defragment your hard disk, the time it takes for MS-DOS to retrieve
your files may increase since it may have to go to several physical
locations on the disk to retrieve the entire file.
- Use 32-bit disk access, and 32-bit file access in Windows 3.11 and
later. In Windows Control Panel, double-click the 386 Enhanced icon,
choose the Virtual Memory button, then choose the Change button.
Make sure the Use 32-Bit Disk Access check box is selected. Also select
the Use 32-Bit File Access option, if it is available in your version
of Windows.
- Increase the RAM on your computer. Microsoft Access requires a minimum
of 6 MB, but additional RAM improves performance.
- Make the WinCacheSize parameter for SMARTDrive (or similar settings
for other disk caches) in your CONFIG.SYS file no larger than
necessary for effective caching. For computers with limited RAM,
try completely disabling software caching such as SMARTDrive.
- Do not use any of your RAM for a RAM disk.
- Set the Buffers parameter in your CONFIG.SYS file to at least 40.
- When you are opening databases that are not in a multiuser environment,
select the Exclusive check box in the Open Database dialog box.
- When you are using databases that other users do not need to share,
install Microsoft Access and all your databases on your local hard disk
rather than on a network server.
- Create only as many indexes as necessary. Although indexes can speed
access to data, it is possible to "over index" a table so that it is
slow adding, deleting, and updating records.
- Create indexes for joined fields.
- In a multiple-field index, use only as many fields in the index as
necessary.
- Use Rushmore query optimization in your queries whenever possible. For
detailed information on how to do this, search for "Rushmore technology"
then "Optimizing Queries with Rushmore Technology" using the Microsoft
Access Help menu.
- If you have a wallpaper (full-screen background) bitmap on your Windows
desktop, replace it with a solid color or pattern bitmap, or no bitmap
at all. For a standard VGA display, this can free about 256K of RAM. For
a 1024 x 768 pixel display with 256 colors, this can free about 750K of
RAM. (Your actual RAM savings depends on your video display.)
REFERENCES
For more information about optimizing Microsoft Access performance,
search for "optimizing performance" using the Microsoft Access Help menu.
|