ACC2: How to Optimize Microsoft Access Version 2.0 Performance

• Microsoft Access 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.

Additional query words: general speeding improving

Keywords          : kbusage GnlOthr 
Version           : 2.0
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: April 2, 1999