C H A P T E R    7 Part 2  Deploying Microsoft Office Microsoft Office Resource Kit
Customizing and Optimizing Microsoft Office Previous

Contents
Next

Index

In This Chapter
Using Windows System Policies to Customize Office
Customizing Office Connections to the World Wide Web
Optimizing Individual Office Applications

This chapter contains information about customizing user environments in your workgroup and modifying your users' computers for optimum performance of Microsoft Office 97 for Windows or Microsoft Office 98 for the Macintosh.

See Also

Top

Using Windows System Policies to Customize Office

Office applications have many options that users can customize to alter the user interface and behavior of the application. As a network administrator, you can set many of these options remotely, for all the users in a workgroup, using Windows system policies. System policies allow you to provide greater consistency among client computers and to centralize support and maintenance efforts.

   Note   System policies are supported by Windows 95 and Windows NT Workstation version 4.0 only. The Macintosh and Windows NT Workstation version 3.51 operating systems do not support system policies. In this section, Windows refers to Windows 95 and Windows NT Workstation 4.0.

Windows System Policies

The Office applications use keys in the Windows registry to record the values of user­defined options. For example, Microsoft Excel stores the value of the Save Excel files as option in a registry key. When a user changes the Save Excel files as option on the Transition tab in the Options dialog box (Tools menu), Excel records the new value in the registry.

A system policy represents an option corresponding to one or more Windows registry keys. System policies are defined by a system policy template file that associates each policy with the registry keys used by the application for the option that the policy represents. The template also organizes the policies in a hierarchy to make them easier to find.

The Windows System Policy Editor displays system policy values as defined in the policy template file and allows you to change these values. You store the modified policies in a system policy file, which you then place on a network server. When a user logs on to the network, Windows downloads the system policy file from the server and alters the values in the user's Windows registry based on the policies in the file.

For example, you can use the System Policy Editor to set a value for the Save Excel files as option described earlier: Set the value in the Default Save policy for Excel, and then place the system policy file on the network server. When a user logs on, Windows reads the Default Save policy value from the policy file and modifies the value entry in the Save Excel files as subkey in the user's Windows registry. When the user runs Excel, the application uses the new Save Excel files as value. Because users logging on to the network all use the same policy file, this same option is set for all users from a central location.

For more information about Windows system policies, see the Microsoft Windows 95 Resource Kit or the Microsoft Windows NT Server 4.0 Resource Kit andMicrosoft Windows NT Workstation 4.0 Resource Kit.

The System Policy Editor

To create or modify a system policy file, use the System Policy Editor, which is included in the Office Resource Kit Tools and Utilities. To create a system policy file click the New Policy command on the System Policy Editor File menu. For more information about how to install the Office policy template files, see "Installing the System Policy Editor," in Appendix C, "Registry Keys and Values."

After you set the policy values you want, place the system policy file in the appropriate folder on the network server.

To store the system policy file on the network server

  1. On the System Policy Editor File menu, click Save As, and save the system policy file as Config.pol (for Windows 95 clients) or Ntconfig.pol (for Windows NT Workstation clients).
  2. Exit the System Policy Editor.
  3. On Windows NT networks, copy Config.pol or Ntconfig.pol to the Netlogon folder of the primary domain controller, as defined for your client computers.

    – or –

    On NetWare networks, copy Config.pol or Ntconfig.pol to the Public folder of the preferred server, as defined for your client computers.

   Tools and Utilities   The Office Resource Kit Tools and Utilities include the latest version of the System Policy Editor. This is the same version that is included with Windows NT Workstation 4.0, and you can use it with both Windows 95 and Windows NT Workstation 4.0. For more information, see "System Policy Editor and Office 97 Policy Templates" in Appendix A, "Microsoft Office Resource Kit Tools and Utilities."

Using Office Policy Template Files

To help you create a policy file for Office, the Office Resource Kit Tools and Utilities include three Office template files that define all the system policies that can be set for Office 97 applications. These template files are described in the following table.

Note   Windows system policy template files have a .adm file extension and are not similar to other Office template files.

The system policies in the Office policy template files are organized by application. Most policies correspond to options that users can set in the Options dialog box (Tools menu) of each Office application. For example, the Macro Virus Protection policy for Word is found under User\Word 97\Tools_Options\General\Macro Virus Protection. Some policies do not correspond to settings that users can select; these are organized by application or under the general Office heading.

For a complete index of system policies available in the Office policy templates, see Appendix C, "Registry Keys and Values."

Setting System Policies

Using the System Policy Editor, you can set user policies for all users, for a particular network group, or for a single user. User policies are system policies that represent application options relevant to the user currently logged on to Windows, and they are stored in the HKEY_USERS portion of the user's Windows registry.

You can also set computer policies for all client computers or for a single computer. Computer policies are system policies that represent options relevant to a particular computer, and they are stored in the HKEY_LOCAL_MACHINE portion of the Windows registry.

To set system policies for all users, double­click the Default User icon in the main window of the System Policy Editor to display the Default User Properties dialog box. This dialog box contains a list of all the system policies you can set for the user. To set system policies for all client computers, double­click Default Computer instead to display the Default Computer Properties dialog box.

Note   The name of this dialog box changes depending on the name of the icon you double­click in the main window of the System Policy Editor, and is referred to generically in this section as the Properties dialog box.

You can also set system policies for a single user or group of users.

To set system policies for a specific user or group of users

  1. Start the System Policy Editor.
  2. On the Edit menu, click Add User or Add Group.
  3. In the Add User or Add Group box, enter the name of the user or the group.

    The System Policy Editor creates an icon for that user or group in the main window.

  4. Double­click the new icon to display the Properties dialog box.

Note   The group names you specify in the System Policy Editor must reference user groups that already exist in the network system. You cannot create new groups from within the System Policy Editor.

If you add more than one group, you can set the relative priority for the groups. When a user who is a member of several groups logs on, the policy settings from the highest priority group are processed last so that those settings override the settings from lower priority groups. To set group priorities, click Group Priority on the Options menu in the System Policy Editor.

In addition to users and user groups, you can set policies for a particular client computer.

To set system policies for a client computer

  1. Start the System Policy Editor.
  2. On the Edit menu, click Add Computer.
  3. In the Add Computer box, enter the name of the client computer.

    The System Policy Editor creates an icon for that computer in the main window.

  4. Double­click the new icon to open the Properties dialog box.

You can expand and collapse groups of policies in the Properties dialog box using the plus (+) and minus ( – ) signs to the left of group headings. Each policy has a check box to the left that indicates its current setting. If a policy requires additional information, an edit control appears at the bottom of the dialog box.

A system policy can have one of three settings in the Properties dialog box:

This template file Defines policies for these applications
Off97w95.adm (Windows 95) or Off97nt4.adm (Windows NT Workstation 4.0) Excel, Microsoft PowerPoint, and Microsoft Word, and general settings for all Office applications
Access97.adm Microsoft Access
Outlk97.adm Microsoft Outlook
Query97.adm Microsoft Query and ODBC
  




  





  

  • Checked

    The policy has been implemented. When a user logs on, the Windows registry changes to conform to the policy.

  • Cleared

    The policy has not been implemented. If it was implemented previously (either through a system policy or the user's configuration settings), the previously specified settings are removed from the registry.

  • Grayed

    The setting is unchanged from the last time the user logged on. Windows makes no modifications to the user's configuration settings.

For example, you might select the check box for the policy Default Save for Excel and assign the value Microsoft Excel 5.0/95 Workbook. When a user logs on to the network, the user's registry is modified to set the default file format in which to save Excel workbooks to this value. If the user has set this value to another file format, that setting is overwritten. If this check box is cleared in the policy file, then the option in Excel is unavailable when the user logs on. If the policy is grayed, then Windows does not modify the user's registry and it retains its previous setting.

Client Computer Requirements for System Policies

Client computers must meet the following requirements to use system policies:

  • Windows 95 or Windows NT Workstation 4.0 must be installed.
  • If you want to set user policies, client computers must have user profiles enabled.
  • If you want to set group policies, the group policy capability must be installed on each client computer.
  • For automatic downloading of policies over Windows NT networks, Client for Microsoft Networks must be specified as the primary network logon client, and the domain must be defined on the client computers.

    For automatic downloading of policies over NetWare networks, Microsoft Client for NetWare Networks must be specified as the primary network logon client, and a preferred server must be defined.

Note   By default, Windows 95 and Windows NT Workstation 4.0 automatically download the system policies you created and placed on the appropriate network server. If you want to change to manual downloading, see the Microsoft Windows 95 Resource Kit or the Microsoft Windows NT Server 4.0 Resource Kit and Microsoft Windows NT Workstation 4.0 Resource Kit. If the client computers are using NETX or VLM, then you must download system policies manually.

How Does Windows Use System Policies?

When the user logs on, Windows checks the user's configuration information for the location of the policy file. Windows then downloads the policies and copies the information in the registry using the following process.

First, if user profiles are enabled, Windows checks for a user policy section that matches the user name and applies the user­specific policy. If Windows does not find a user policy section, it applies the Default User policies. If support for group policies is installed, Windows downloads group policies, starting with the lowest priority group and ending with the highest priority group. Group policies are processed for all groups to which the user belongs. Group policies are not applied if there are user policies defined for the user. These settings are copied into the USER.DAT portion of the registry.

Second, Windows applies computer­specific policies to the desktop environment. If a policy section for that computer name does not exist, Windows applies the Default Computer policies. These settings are copied into the SYSTEM.DAT portion of the registry.

By default, Windows automatically attempts to download computer and user policies from the file Config.pol or Ntconfig.pol in the Netlogon folder on a Windows NT server or the Public folder on a NetWare server. This default location can be overridden in a policy file setting. If no server is present, Windows uses the settings currently on the client computer. For more information about how Windows processes system policies, see the Microsoft Windows 95 Resource Kit or theMicrosoft Windows NT Server 4.0 Resource Kit andMicrosoft Windows NT Workstation 4.0 Resource Kit.

Top

Customizing Office Connections to the World Wide Web

Through the Microsoft on the Web command (Help menu), you can connect to Microsoft pages on the World Wide Web that relate to Office and Office applications (requires Internet access). This section describes how you can customize the Web connections available on the Help menu in the Office applications.

Customizing Web Connections in Office 97 for Windows

   Office 97 allows you to connect your Office applications directly to Microsoft pages on the World Wide Web through commands on the Microsoft on the Web submenu (Help menu). You can also modify these commands to include additional Web or intranet sites, or you can disable the commands.

Using the System Policy Editor

This section describes in detail how to use the System Policy Editor and Office policy templates to set some system policies for your workgroup. Use this information to learn how to set any Office system policy with the System Policy Editor. Make sure that you have already installed the System Policy Editor and Office policy templates from the Office Resource Kit Tools and Utilities.

For more information about the System Policy Editor, see "Using Windows System Policies to Customize Office" earlier in this chapter. For detailed descriptions of the system policies in the Office policy template files, see "Using Office 97 System Policies" in Appendix C, "Registry Keys and Values."

   Note    System policies are supported by Windows 95 and Windows NT Workstation version 4.0 only. The Macintosh and Windows NT Workstation version 3.51 operating systems do not support system policies. In this section, Windows refers to Windows 95 and Windows NT Workstation 4.0.

When you start Office, it searches the registry of the computer on which it is installed for a registered Web browser. If it finds one, Office enables the Microsoft on the Web submenu commands. These commands are grouped into two sets:

  • Commands related to the application
  • Commands related to Office, including the Microsoft Home Page command

For example, the following illustration shows the default Microsoft on the Web submenu in Excel.

For users who have Web access, these commands provide hyperlinks to a wealth of information about the application and other useful Web resources on the Microsoft Web site. If no registered browser is found, these commands are unavailable.

The following table describes the commands on the Microsoft on the Web submenu.

Note   In the Visual Basic environment of the Office applications, some of the Microsoft on the Web commands function differently. The Microsoft Office Home Page command is replaced by the For Developers Only command, which connects you to:

http://www.microsoft.com/msdn/

The Send Feedback command connects you to:

http://www.microsoft.com/officedev/misc/feedback.htm

Tip   You can use the System Policy Editor and the Office policy template files to edit your own registry. In this case, the System Policy Editor displays only the Office-related registry keys and values that you are most likely to want to change. To edit your own registry settings, click Open Registry on the File menu in the System Policy Editor.

Disabling Commands on the Microsoft on the Web Submenu

The Microsoft on the Web submenu commands are enabled only if an Office application detects a registered Web browser. If these commands are available, but you want to disable them for some or all users in your workgroup, you can do so by using the System Policy Editor to modify Office system policies.

You can disable the application­specific commands (commands one through four on the submenu), or the Office­wide commands (commands five through nine on the submenu). If you disable both groups of commands, the Microsoft Home Page command is also disabled.

To disable the application­specific commands on the Microsoft on the Web submenu

  1. Close the Office application you want to customize.
  2. On the Start menu, point to Programs, point to Accessories, and then point to System Tools.
  3. Click System Policy Editor.
  4. On the File menu, click New Policy.

    – or –

    If you have previously created a system policy file, click Open on the File menu, and select the file Config.pol (for Windows 95 clients) or Ntconfig.pol (for Windows NT Workstation clients).

  5. To set system policies for all users, double-click the Default User icon.

    – or –

    To set system policies for a specific user or group of users, on the Edit menu, click Add User or Add Group, and enter the name of the user or group. Then double-click the new icon.

  6. In the Properties dialog box, expand the Policies tree to:

    User\application\Internet\Help_Microsoft on the Web

  7. Select the Disable submenu check box.
  8. Click OK to close the Properties dialog box, and then exit the System Policy Editor.

To distribute the new policy file, you must store it on a network server. For more information about using the System Policy Editor, see "The System Policy Editor" earlier in this chapter.

To disable the Office­wide commands on the Microsoft on the Web submenu

  1. Close all Office applications.
  2. On the Start menu, point to Programs, point to Accessories, and then point to System Tools.
  3. Click System Policy Editor.
  4. On the File menu, click New Policy.

    – or –

    If you have previously created a system policy file, click Open on the File menu, and select the file Config.pol (for Windows 95 clients) or Ntconfig.pol (for Windows NT Workstation clients).

  5. To set system policies for all users, double-click the Default User icon.

    – or –

    To set system policies for a specific user or group of users, on the Edit menu, click Add User or Add Group, and enter the name of the user or group. Then double-click the new icon.

  6. In the Properties dialog box, expand the Policies tree to:

    User\Office 97\Internet\Help_Microsoft on the Web

  7. Select the Disable submenu check box.
  8. Click OK to close the Properties dialog box, and then exit the System Policy Editor.

To distribute the new policy file, you must store it on a network server. For more information about using the System Policy Editor, see "The System Policy Editor" earlier in this chapter.

Adding Your Own Commands to the Microsoft on the Web Submenu

You can replace or add to the default commands on the Microsoft on the Web submenu with your own commands. For example, you might have an internal support page on your intranet that you would like users to visit instead of the support pages on the Microsoft Web site.

To add your own commands to the Microsoft on the Web submenu

  1. Close the Office applications you want to customize.
  2. On the Start menu, point to Programs, point to Accessories, and then point to System Tools.
  3. Click System Policy Editor.
  4. On the File menu, click New Policy.

    – or –

    If you have previously created a system policy file, click Open on the File menu, and select the file Config.pol or Ntconfig.pol.

  5. To set system policies for all users, double­click the Default User icon.

    – or –

    To set system policies for a specific user or group of users, on the Edit menu, click Add User or Add Group, and enter the name of the user or group. Then double-click the new icon.

  6. In the Properties dialog box, expand the Policies tree to:

    User\Office 97\Internet\Help_Microsoft on the Web

  7. Select the Customize submenu check box.
  8. In the Settings for Customize Submenu box, scroll down to the first empty command box.
  9. Click inside the empty command box, and then enter your hyperlink information in the following format:
    &command name,URL
    

    where the character after the ampersand (&) will appear underlined in the menu.

    For example, to add the command Office 97 Resource Kit that connects to the Office Resource Kit home page on the Web, you would enter:

    Office 97 &Resource Kit,http://www.microsoft.com/office/ork/
    
   10. Click OKto close the Propertiesdialog box, and then exit the System Policy Editor.

To distribute the new policy file, you must store it on a network server. For more information about using the System Policy Editor, see "The System Policy Editor" earlier in this chapter.

Customizing Web Connections in Office 98 for the Macintosh

This command on the Microsoft on the Web submenu Connects to this page on the Microsoft Web site
Free Stuff The Free Stuff page for the application, from which you can download file converters, add­ons, and other free utilities.
Product News The home page for the application.
Frequently Asked Questions The Frequently Asked Questions (FAQ) page for the application. This page is maintained by Microsoft Support Services.
Online Support The online support page for the application. This page is maintained by Microsoft Support Services.
Microsoft Office Home Page The Office home page, where you can find Office­wide and application­specific information.
Send Feedback An Office­wide feedback page, where you can submit feature requests, bug reports, or product purchasing and licensing information.
Resources on the Web A page that contains hyperlinks to other useful reference sites, such as home, reference, and financial information.
Search the Web A page that contains hyperlinks to Web search pages.
Web Tutorial The Microsoft Web tutorial.
Microsoft Home Page The Microsoft home page at http://www.microsoft.com/.
   In Office 98 for the Macintosh applications, the Help on the Web command on the Help menu opens an HTML document in your Web browser. This HTML document, named Webhelp.htm, is installed in the Microsoft Office 98:Office:Help folder. For users who have Web access, the Help on the Web command provides hyperlinks to a wealth of information about the application and other useful Web resources on the Microsoft Web site.

You can edit Webhelp.htm in an HTML editor, such as Word with the Web Page Authoring component installed. You can replace or add to the default hyperlinks in Webhelp.htm with your own hyperlinks. For example, you might have an internal support page on your intranet that you would like users to visit instead of the support pages on the Microsoft Web site.

To edit the Help on the Web HTML document in Word

  1. On the File menu, click Open.
  2. In the List files of type box, click HTML Document.
  3. In the Select a Document box, click Webhelp.htm in the folder Microsoft Office 98:Office:Help.
  4. Edit Webhelp.htm as needed, and then close the document.

    The next time you click the Help on the Web command on the Help menu, the revised Webhelp.htm document opens in your browser.

What if I cannot open an HTML document in Word?

If HTML Document does not appear in the List files of type box in the Open dialog box, or if Word opens the HTML document but displays only the HTML code, you need to install the Web Page Authoring component. Run Microsoft Office Installer and click Custom Install. Select the Web Page Authoring option, and then click Install.

Top

Optimizing Individual Office Applications

There are a number of ways that you can customize Office applications to improve the overall performance of Office on client computers. By modifying specific options based on your particular use of Office, you can increase speed or reduce memory or disk usage. The remainder of this chapter discusses how to optimize each of the Office applications.

Optimizing Microsoft Access

   Depending on your computer's configuration and your working environment, there may be several things you can do to improve the performance of Microsoft Access or your database. The optimal settings described in the following sections may vary with the type of computer on which you run Access.

Using the Performance Analyzer

When you want to optimize the performance of a Microsoft Access database, start with the Performance Analyzer. You can use the Performance Analyzer to analyze a database or selected objects in a database. The Performance Analyzer can make some changes for you automatically.

To run the Performance Analyzer

  1. Open the Access database you want to optimize.
  2. On the Tools menu, point to Analyze, and then click Performance.
  3. In the Object Type box, click the type of database object you want to optimize.

    To view a list of all database objects at once, click All in the Object Type box.

  4. In the Object Name box, select the database objects you want to optimize.
  5. Repeat Steps 3 and 4 until you have selected all the objects you want to optimize, and then click OK.

In the dialog box that appears, the Analysis Results box displays three types of optimizations: Recommendations, Suggestions, and Ideas. When you click an item in the Analysis Results box, the Analysis Notes box displays information about the proposed optimization. Suggestion optimizations have potential tradeoffs to consider. To view a description of the tradeoffs, click a Suggestion optimization in the Analysis Results box, and then read the information in the Analysis Notes box.

Access can perform Recommendation and Suggestion optimizations for you. In the Analysis Results box, click each of the Recommendation or Suggestion optimizations you want performed, and then click Optimize.

You must perform Idea optimizations yourself. To perform an Idea optimization, click that optimization, and then follow the instructions in the Analysis Notes box.

Optimizing a System to Run Microsoft Access

In addition to running the Performance Analyzer, the following suggestions can help you optimize the performance of Access and the computer it is running on.

Increase RAM

Because database operations are faster when Access can perform them in random­access memory (RAM), the performance of Access depends largely on the amount of RAM available. To run as a standalone application, Access requires a minimum of 12 MB of RAM for Windows, or 16 MB of RAM for Windows NT Workstation.

The amount of memory Access needs to run at top speed depends on a variety of factors, such as how many other applications are running at the same time and what types of operations an Access database application performs. When other applications are competing for memory, you can usually improve performance significantly by running with more than the required amount of RAM.

Similarly, if an Access database application uses Automation (formerly OLE Automation) to work with another application's objects, you can improve the performance of an Access database application by running with more RAM. When running with 32 MB or more of RAM, you may be able to increase performance by adjusting the MaxBufferSize setting in the Windows registry. Make sure that you are not using any of your RAM for a RAM drive.

For more information about the MaxBufferSize setting, see "MaxBufferSize" later in this chapter.

Optimize Virtual Memory Use

With virtual memory, an application sees a large, continuous block of primary memory (RAM) that, in reality, is a much smaller block of primary memory supplemented by secondary memory (such as a hard disk). To temporarily free up space in RAM, blocks of data (called pages) are moved between RAM and a swap file located on the hard disk.

By default, the Windows 95 swap file is dynamic, so it can shrink or grow based on available disk space and the operations performed on the system. Also, the swap file can occupy a fragmented region of the hard disk with no substantial performance penalty. A dynamic swap file is usually the most efficient use of resources. The simplest way to ensure high virtual memory performance is to make sure that the disk containing the swap file has ample free space so that the swap file size can shrink and grow as needed.

In Windows 3.x, enhancing performance by changing virtual memory settings is quite common. Because the Windows 95 swap file is dynamic, the need to change virtual memory settings is less common. However, in some situations adjusting virtual memory settings can improve performance. If you've already tried deleting unnecessary files, and you still have a performance problem, try changing the Windows 95 default virtual memory settings.

If you have more than one drive available, you may get better performance if you specify that Windows locate the swap file on a drive other than the default in the following cases:

  • If the default drive does not have much free disk space, and another local drive has space available.
  • If another local drive is available that is faster than the current drive (unless that disk is already heavily used).

You also may get better performance if you specify the minimum disk space available for virtual memory to be at least 25 MB minus available RAM. For example, if a computer has 12 MB of RAM, you should specify at least 13 MB of virtual memory. You may want to specify more if you run several large applications at the same time.

For more information about changing Windows 95 virtual memory settings, see Windows 95 online Help.

Compact Databases and Defragment the Hard Disk

Each time users add or update data, a database grows. But when a user deletes data, the database does not automatically get smaller. Access makes empty data pages available for reuse after a database is closed, so the next time it is opened, new data pages are available to be filled with new records. If left unused, empty data pages remain in the database until it is compacted.

Compacting the database frees unused disk space, makes all data pages contiguous, and, if a primary key is defined for a table, saves its records in primary key order. For databases in which records are frequently added, deleted, and updated, you should compact frequently.

Note   If the database is a replica, you must compact it twice to reclaim all available space. Additionally, if the database is the Design Master in a replicated set, you must take certain precautions when compacting it to prevent it from losing its Design Master status. For more information about replication, see Chapter 29, "Workgroup Features in Microsoft Access."

In addition, you should occasionally use a utility such as the Windows Disk Defragmenter to defragment the hard disk after compacting databases. This increases efficiency by making database files contiguous on the hard disk.

Do Not Use Wallpaper on a Low­Memory Computer

If the Windows desktop has a wallpaper (full­screen background) bitmap and the computer does not have memory to spare, replace the wallpaper with a solid color or pattern bitmap, or no bitmap at all.

Using the System Policy Editor to Change Settings in the Options Dialog Box

You can customize the user environment and achieve some performance optimizations in Access by setting options in the Options dialog box (Tools menu) on individual user's computers. Using the System Policy Editor, however, a network administrator can set these options remotely — for all users, for a particular network group, or for a single user. You can also set options for all client computers or for a single computer.

Instead of making these changes in the Options dialog box on individual computers, run the System Policy Editor using the Access97.adm template. For more information about using the System Policy Editor, see "Using Windows System Policies to Customize Office" earlier in this chapter.

Adjusting Microsoft Jet Registry Settings to Improve Performance

In addition to optimizing system memory use, you may want to adjust Microsoft Jet settings in the Windows registry. These settings control how the Microsoft Jet database engine uses memory and performs other aspects of its operations. Microsoft Jet automatically provides default settings that usually provide an excellent performance for most common database operations. However, depending on what kind of operations a database application performs and how much memory is available at any given time, you may be able to improve performance by adjusting Windows registry settings.

Note   Keep in mind that the optimum value for a setting in the registry can change from computer to computer, and can change depending on how much memory is available, the kind of operation the user is performing, and the level of activity in the database at any given time. Experiment with these settings to find out what works best.

If you decide to change any of the Microsoft Jet registry settings in the Windows registry, you have three options:

  • Change the default values for Microsoft Jet settings in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5 key.

    These defaults are written when Access 97 is installed. Changes made to settings in this key may affect other applications that use the Microsoft Jet version 3.5 database engine, such as Visual Basic version 5.0 or Excel 97. Make changes to values in this key only if you want to affect all the applications that use Microsoft Jet 3.5 on a user's computer, or if only Access 97 is used to perform operations on Microsoft Jet 3.5 databases on a user's computer.

  • Create a Jet 3.5 subkey below the KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5\Engines key, and then add values for any default setting you want to override.

    These changes affect only Access 97.

  • Create a custom set of registry keys, called a user profile, that contains settings to override default registry settings.

    These settings affect only a particular database application or session of Access. You use the /profile command­line option when starting Access to specify the user profile you want to use.

For more information about user profiles or changing Access registry settings, see Access online Help.

Using the SetOption Method to Change Settings in the Windows Registry

You can also change most of the Microsoft Jet registry settings by using the SetOption method of the DBEngine object in Data Access Objects (DAO) code. Using the SetOption method, you can change these settings while a database application is running.

For example, you may want to change the SharedAsyncDelay and PageTimeout settings to low values to increase performance when an application performs operations on one record at a time or is using controls bound to data; but you may want to increase these values when performing bulk operations, such as update and delete queries or transactions on large numbers of records.

Changing Microsoft Jet settings by using the SetOption method does not affect the values stored in the Windows registry. Changes made with the SetOption method are in effect only for the current instance of the DBEngine object. For more information about the SetOption method, see Access online Help.

The following sections discuss the Windows registry settings you can adjust to improve performance of Access.

Threads

A thread is a software process that runs independently on a multitasking operating system such as Windows 95 or Windows NT Workstation. For example, using multiple threads allows you to run a communications software program to download a file at the same time you use your word processor to edit a document.

Microsoft Jet uses threads internally to enhance performance and provide background services such as read­ahead caching, write­behind caching (transaction commit), cache maintenance, and the detection of changes made to shared databases. By default, Microsoft Jet uses up to a maximum of three threads. You may want to try increasing the Threads setting if a large number of actions are performed in a database application, or if it contains a large number of linked tables.

MaxBufferSize

Microsoft Jet reads and writes data in 2 KB pages, placing the data in a temporary holding area called the buffer as required by its operations. By default, when performing operations that add, delete, or update records and that are not part of an explicit transaction, Microsoft Jet automatically performs internal transactions that group changes to records and temporarily saves them in the buffer.

After a specified time, or when the size specified by the MaxBufferSize setting is exceeded, Microsoft Jet writes the data to the database. This minimizes the time spent reading and writing data to the database. Additionally, Microsoft Jet can minimize the time spent reading data for tables, queries, forms, or reports by reading available data from its buffer.

The MaxBufferSize setting specifies a high­water mark for the size of the buffer that Microsoft Jet uses to work with records in memory, measured in kilobytes. Microsoft Jet can temporarily exceed the MaxBufferSize. As soon as it does, however, it starts a background thread to write data to the database to bring the buffer's size down to the specified high­water mark.

By default, Microsoft Jet allocates memory for its internal buffer on an as­needed basis up to the MaxBufferSize. The default value written in the Windows registry for the MaxBufferSize when Access is installed is 0, which indicates that Microsoft Jet calculates the setting based on the following formula:

((Total RAM in KB – 12,288)/4 + 512 KB)

For example, on a system with 16 MB of RAM (16,384 KB), Microsoft Jet uses a setting of 1,536 in MaxBufferSize.

You can override the default calculated setting by specifying a different value in MaxBufferSize. This sets a new high­water mark. The minimum value that Microsoft Jet uses by default is 512 KB, but you can specify a value as low as 128 KB. However, setting the value in MaxBufferSize to less than 512 KB is not recommended for Access applications because it can seriously degrade performance.

For computers with 16 MB or less of installed RAM, there is generally no need to override the calculated setting. For computers with 32 MB of RAM, however, you may see some performance improvement when specifying a larger MaxBufferSize, as long as no other applications are running at the same time as Access. For example, tests have shown performance improvements in applications that perform large transactions when a MaxBufferSize as large as 8 MB is specified.

Specifying a MaxBufferSize larger than 8 MB has not been found to increase performance. Setting a value too high can degrade performance due to the added CPU overhead needed to manage the cache, and due to the fact that the operating system may start swapping the Microsoft Jet cache to disk as virtual memory.

Important   When you specify a MaxBufferSize setting larger than the default and you are not using the FlushTransactionTimeout setting, increase the SharedAsyncDelay setting to increase the time that data is held in the buffer. If you do not increase the value of SharedAsyncDelay, Microsoft Jet writes the contents of its buffer to the database before it has had time to utilize the additional memory you specified. For more information, see "SharedAsyncDelay" later in this section.

UserCommitSync

The UserCommitSync setting determines whether changes made as part of an explicit transaction (a change made to data using the BeginTrans, CommitTrans, and Rollback methods) are written to the database in synchronous mode or asynchronous mode. In synchronous mode, Microsoft Jet does not return control to the application code until the changes made by the CommitTrans method are written to the database. In asynchronous mode, Microsoft Jet stores the changes in its memory buffer, returns control to the application code immediately, and then writes the changes to the database in a background thread.

Microsoft Jet begins writing the changes either after a specified period of time (determined by the FlushTransactionTimeout setting, or by the SharedAsyncDelay or ExclusiveAsyncDelay settings described later in this section), or when the MaxBufferSize is exceeded. The default UserCommitSync setting is Yes, which specifies synchronous mode. It is not advisable to change this setting, because in asynchronous mode there is no guarantee that information has been written to disk before a database application's code proceeds to the next command.

Note   There is no longer a need to use explicit transactions to improve performance of Access. In Access 95 and Access 97, a database application should use explicit transactions only in situations where there may be a need to roll back changes. Microsoft Jet can now automatically perform internal transactions to improve performance whenever it adds, deletes, or changes records.

For more information about transactions, see Chapter 9, "Working with Records and Fields," in Building Applications with Microsoft Access 97.

ImplicitCommitSync

By default, when performing operations that add, delete, or update records outside of explicit transactions, Microsoft Jet automatically performs internal transactions that temporarily save data in its memory buffer, and then later write the data to the hard disk. The ImplicitCommitSync setting determines whether changes made using these automatic internal transactions are written to the database in synchronous mode or asynchronous mode.

The default ImplicitCommitSync setting is No, which specifies that changes are written to the database in asynchronous mode; this helps provide an excellent performance. If you want internal transactions to be written to the database in synchronous mode, change the ImplicitCommitSync setting to Yes. This generates behavior similar to Microsoft Jet versions 2.x and earlier, when not using explicit transactions in Access Basic code; however, it can also impair performance considerably.

FlushTransactionTimeout

The FlushTransactionTimeout setting determines the method Microsoft Jet uses to perform asynchronous writes to a database file. This setting is the number of milliseconds after which Microsoft Jet starts writing database changes to disk from its cache. Changes are written to disk after the specified amount of time has expired and if no new pages have been added to the cache during that interval.

The only exception is if the size of the cache exceeds the MaxBufferSize setting, at which point the cache starts asynchronous writes regardless of whether the time has expired. The default setting is 500 milliseconds. The only reason to increase the value of the FlushTransactionTimeout setting is if the database is being updated over a slow wide area network (WAN) or local area network (LAN) connection. Increasing this value for databases being updated over fast WAN and typical LAN connections does not improve performance.

The FlushTransactionTimeout setting overrides both the ExclusiveAsyncDelay and
SharedAsyncDelay settings, and is the preferred method of determining how Microsoft Jet performs asynchronous writes to a database file. To enable the ExclusiveAsyncDelay and SharedAsyncDelay settings, you must set the FlushTransactionTimeout entry to a value of 0.

ExclusiveAsyncDelay

The ExclusiveAsyncDelay setting is the maximum time that can pass before asynchronous mode changes start to be written to a database that is opened exclusively. The default setting is 2,000 milliseconds. Decrease this setting if you want to be sure that changes are written to a database more frequently, but note that this decreases overall performance.

Because the default setting is already quite long, increasing this setting does not improve performance in most cases. However, if a system has 32 MB of RAM or more and you have specified a large MaxBufferSize, you may see some performance improvement if you increase this setting. By default, this setting is disabled by the FlushTransactionTimeout setting.

SharedAsyncDelay

The SharedAsyncDelay setting is the maximum time that can pass before asynchronous mode changes start to be written to a database that is opened in shared mode. The default setting is 50 milliseconds. Note that this produces a small delay before changes are made available to other users. Increasing this value enhances performance in shared databases because there are fewer disk writes. However, it may reduce the overall concurrency because pages are locked while they are in the buffer waiting to be written to the database.

If a database application performs operations that affect many records, increase this setting to give Microsoft Jet additional time to temporarily save additions, deletions, and updates in its buffer before it writes them to the database. This applies whether a database application uses action queries, DAO code, or SQL statements to add, delete, or update records. By default, this setting is disabled by the FlushTransactionTimeout setting.

PageTimeout

The PageTimeout setting determines how long Microsoft Jet waits before checking to see whether other users have made changes to the database. If changes have been made, Microsoft Jet refreshes the data in its memory buffer.

Note   This action is equivalent to pressing F9 while viewing a table or query in Access, or using the Refresh method in DAO code.

The default PageTimeout setting is 5,000 milliseconds. Decreasing this setting increases the amount of time spent reading from the disk, thus impairing performance; but it can also ensure the data available to the user is more current. The Refresh Interval setting on the Advanced tab in the Options dialog box (Tools menu) in Access overrides the setting in PageTimeout.

Tip   You can override the PageTimeout setting and refresh the cache by using the dbRefreshCacheargument of the Idle method in DAO code. This allows users to see other users' changes immediately. For more information about the Idle method, see Access online Help.

LockDelay

If Access tries to place a lock on a page in a shared database, and a message that locking has failed is returned, the LockDelay setting determines how long it waits before it retries. If the time it takes to return the message exceeds the LockDelay setting, there is no delay. The default setting is 100 milliseconds.

On systems that do not manage lock retries themselves, such as Windows 95 peer­to­peer networking, the LockDelay setting prevents Microsoft Jet from performing repeated retries over a short period of time. If you are using such a system, the default setting is usually sufficient to reduce the number of lock requests sent across the network, which frees up network bandwidth for other purposes.

If a database application or its users frequently lock a large number of records, you may want to try increasing this setting to further reduce the number of lock requests being sent across the network. If you are using a server­based networking system that manages lock retries itself, such as Windows NT Server or Novell NetWare, there is no need to change this setting.

MaxLocksPerFile

The MaxLocksPerFile setting determines the maximum number of locks that Microsoft Jet places against a file. The default setting is 9,500 locks. If the number of locks required to perform a transaction exceeds the MaxLocksPerFile setting, the transaction commits (writes) the data that has locks associated with it, frees the locks, and then continues processing the transaction.

If the maximum number of locks a server can handle is less than the setting in MaxLocksPerFile, the server returns an error message or appears to hang when performing a large transaction. If this occurs, decrease the value of MaxLocksPerFile. A NetWare server can be configured to perform a maximum of 10,000 locks per connection. A NetWare server connection can include more than one database, so it is possible to exceed the maximum number of available locks if you are using more than one database at a time.

RecycleLVs

Long value (LV) pages store data in fields with Memo, OLE Object, and Hyperlink data types, as well as the data that defines forms, reports, and modules. When a database is open in shared mode, the size of the database increases when data, forms, reports, and modules are deleted or changed in such a way that the current LV page must be discarded and replaced with a new LV page. Sometimes a new LV page is needed to prevent errors for other users who may still be using the object or data in its original form.

The RecycleLVs setting determines when discarded LV pages become available for reuse. The default setting is 0, which specifies that discarded LV pages continue to occupy space in the database and only become available for reuse after the last user closes the database. You can change the default setting to 1 so that discarded LV pages become available for reuse after Microsoft Jet determines that there is only one user in the database in shared mode and that new LV data has been added to the database.

Note that setting RecycleLVs to 1 slows performance slightly. Therefore, this is primarily useful during development of a database application, when forms, reports, and modules are being created, because it minimizes the need to compact the database.

When a database is open in exclusive mode, the RecycleLVs setting has no effect: discarded LV pages become available for reuse immediately. In both exclusive mode and shared mode, discarded LV pages are reused only when new LV data needs to be written to the database. To remove discarded LV pages before that time, you must compact the database.

Optimizing Table Performance

You can achieve excellent performance results by following these guidelines for table design.

Design Tables Without Redundant Data

A well­designed database is a prerequisite for fast data retrieval and updates. If existing tables in your database contain redundant data, you can split the data into related tables so that you can store data more efficiently. This process is called normalization.

You can use the Table Analyzer Wizard to normalize your tables manually, or you can have the wizard do this for you automatically. After identifying the data that can be moved to smaller tables, the Table Analyzer Wizard identifies a unique value within each new table to use as a primary key, or if no such value exists, creates an incrementing AutoNumber field to use as the primary key. It creates foreign key fields and uses them with the primary keys to create a relationship between the new tables. Finally, the wizard searches through your data, identifies any values that appear to be inconsistent, and prompts you to choose the correct value.

To use the Table Analyzer Wizard

  1. On the Tools menu, point to Analyze, and then click Table.

    The first two panels of the wizard contain introductory information about normalization.

  2. On the third panel of the wizard, double­click the table that you want to normalize.
  3. Follow the instructions in the remaining panels of the wizard.

    In the last panel, you can create a query to view all the information from the split tables in a single datasheet.

For more information about determining which tables, fields, and relationships belong in your database, see Access online Help.

Choose Appropriate Data Types for Fields

You can save space in a database and improve join operations by choosing appropriate data types for fields. When defining a field, choose the smallest data type that is appropriate for the data in the field. Also, give fields you use in joins the same or compatible data types.

For more information about data types or the size of data types for fields, see Access online Help.

Create Indexes for Fields Used in Joins and When Setting Criteria

An index helps Access find and sort records faster. You can make dramatic improvements in the speed of queries by indexing fields on both sides of joins, or by creating a relationship between those fields and then indexing any field used to set criteria for the query. Finding records through the Find dialog box (Edit menu) is also much faster when searching an indexed field. Intelligent indexing of large tables can significantly improve performance when your query uses Rushmore query optimization.

For more information about Rushmore query optimization, see Access online Help.

Note   Microsoft Jet can use a descending index to optimize a query, but the criteria on the indexed field must use an equal sign (=) as the comparison operator. If the index is descending and the comparison operator is something other than an equal sign, the index will not be used. Ascending indexes (the default) can always be used to optimize a query.

Indexes take up disk space and slow down the adding, deleting, and updating of records. In most situations, however, the faster data retrieval outweighs these disadvantages. If a database application updates data frequently or if you have disk space constraints, you may want to limit indexes; otherwise, use them generously. Make sure to create a primary key or unique index if the data in a field or combination of fields uniquely identifies the records in a table.

Use Multiple­Field Indexes When Joining Multiple Fields

If you use multiple­field indexes, Access can optimize queries that join multiple fields from one table to multiple fields in another table (such as LastName, FirstName in one table to LastName, FirstName in another table) or that search for values in multiple fields.

If you use criteria on a field in a multiple­field index, the criteria must apply to the first field or fields in the index in order for Microsoft Jet to use the index to optimize the query. For example, if you have a multiple­field index on the two fields LastName and FirstName used in a join to the LastName and FirstName fields in another table, Microsoft Jet can use the multiple­field index to optimize the query when you have criteria on LastName only, or on LastName and FirstName, but not if the criteria is on FirstName only. If the criteria is on FirstName only, you must add a single­field index on FirstName to optimize the query.

The simplest approach is to add multiple­field indexes to fields used in multiple­field joins and also add a single­field index to any field on which you use criteria to restrict the values in the field. You can add both a multiple­field index and a single­field index to the same field.

Optimizing the Performance of Linked Tables

Although you can use linked tables as though they were regular Access tables, keep in mind that they are not actually in your Access database. Each time you view data in a linked table, Access has to retrieve records from another file. This can take time, especially if the linked table is on a network or in an SQL database.

If you are using a linked table on a network or in an SQL database, follow these guidelines for the best results:

  • Force the linked database to remain open.

    This greatly enhances performance when opening the main database and opening tables and forms. Create an empty table in the linked database and create a link to the table in the main database. Then use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and creating and deleting the associated LDB file.

  • View only the data you need.

    Do not page up and down unnecessarily in the datasheet. Avoid going to the last record in a large table. If you want to add new records to a large table, use the Data Entry command on
    the Records menu to avoid loading existing records into memory. If you often add records to a linked table, create a form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Access does not display any existing records and does not have to retrieve all the records in the linked table.

  • Use filters or queries to limit the number of records that you view in a form or datasheet.

    This allows Access to transfer less data over the network.

  • In queries that involve linked tables, avoid using functions in query criteria.

    In particular, avoid using domain aggregate functions, such as DSum, anywhere in your queries. When you use a domain aggregate function, Access retrieves all of the data in the linked table to execute the query.

  • When an Access database is shared on a network, avoid locking records longer than necessary.

Optimizing SQL Database Performance

When you connect to an external SQL database table, you can maintain excellent performance by using linked tables whenever possible. Linked tables are considerably faster, more powerful, and more efficient than directly opened tables. You can only open tables directly using Visual Basic code, so this is not pertinent if you are accessing SQL Server data using the Access user interface.

Following are additional guidelines for improving performance:

  • Retrieve and view only the data you need.

    Use restricted queries to limit the number of records that you retrieve, and select only the columns you need, so Access can transfer less data over the network. If you are not updating the data, do not use updatable result sets. You can prevent data from being updated when using a form by setting the form's RecordSet property to Snapshot.

  • If you need to retrieve a large number of records, use a dynaset.

    Dynasets are faster and more efficient than snapshots. For example, when you move to the end of a snapshot, the entire result set must be downloaded to the local computer. With a dynaset, however, only the last screen is downloaded. Also do not page up and down unnecessarily in the data, and avoid going to the last record in a large table — the Data Entry command on the Records menu is the fastest way to add new records to a table.

  • Use cache memory.

    If the data most recently retrieved from the server is requested again while the application is running, it is faster to retrieve many rows from a cache than to fetch many individual rows. Access forms and datasheets automatically use a cache. If you are retrieving data using a Recordset object created in Visual Basic for Applications, you can use the CacheStart and CacheSize properties to specify the range you want within the Recordset object. Use the FillCache method to quickly fill all or part of this range with data from the SQL database server.

  • Avoid using queries that cause processing to be done locally.

    When accessing external data, the Jet database engine processes data locally only when the operation cannot be performed by the external database. Query operations (as defined by the SQL commands used to implement them) performed locally include:

    • JOIN operations between tables from different remote data sources. (Note that if the join involves a local table or query with few records and a remote table with many more records, and the remote table's join field is indexed, Access returns only the records that match the local table or query, thus greatly improving query performance.)
    • JOIN operations based on a query with the DISTINCT predicate or a GROUP BY clause.
    • Outer joins containing syntax not supported by the Open Database Connectivity (ODBC) driver or server.
    • DISTINCT predicates containing operations that cannot be processed remotely.
    • The LIKE operator used with Text or Memo fields (this may not be supported by some servers).
    • Multiple­level GROUP BY arguments and totals, such as those used in reports with multiple grouping levels.
    • GROUP BY arguments based on a query with a DISTINCT predicate or a GROUP BY clause.
    • Cross­tab queries that have more than one aggregate; that have field, row, or column headings that contain aggregates; or that have a user­defined ORDER BY clause.
    • TOPn or TOPn PERCENT predicates.
    • User­defined functions, or operators or functions that are not supported by the ODBC driver or server.
    • Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.
  • For update and delete queries that affect data stored on a server, set the FailOnError property to Yes.

    This setting tells Access to send the query to the server, where all the appropriate records are processed at once instead of one record at a time.

Optimizing Multiuser Performance

This section describes suggestions that can help you optimize the performance of databases that are used in a multiuser environment.

Database performance is faster when only data is sent across the network. Put only the tables on a network server, and keep other database objects on users' computers. You can separate the tables from the other database objects by using the Database Splitter. To split a database, make a backup copy of the database, and then use the Database Splitter command (Tools menu, Add­ins submenu).

Choose the appropriate record locking method. To set the record locking method, use the options on the Advanced tab in the Options dialog box (Tools menu). The options under Default Record Locking include the following:

  • No Locks

    Access does not lock the records being edited. When a user tries to save changes to a record that another user has also changed, Access gives the option of overwriting the other user's changes, copying the user's version of the record to the Clipboard, or discarding the changes. This strategy ensures that records can always be edited, but it can create editing conflicts between users.

  • Edited Records

    Access locks the record being edited, so no other user can change it. It might also lock other records that are stored nearby on the hard disk. If another user tries to edit a record that is locked, Access displays an indicator in the other user's datasheet. This strategy ensures that users can always finish making changes that they have started. It is a good choice if you do not often have editing conflicts.

  • All Records

    Access locks all records in the form or datasheet (and underlying tables) for the entire time it is being edited, so no one else can edit or lock the records. This strategy is very restrictive, so choose it only when you know only one person needs to edit records at any one time.

Note   When you edit data in a linked SQL database table using ODBC, Access does not lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record­locking setting you choose for your database, Access always acts as though the No Locks setting has been selected.

You can avoid locking conflicts by adjusting the following settings on the Advanced tab in the Options dialog box (Tools menu):

  • Update Retry Interval

    This sets the number of milliseconds after which Access automatically tries to save a changed record that is locked by another user. Valid values are 0 through 1,000 milliseconds. The default setting is 250.

  • Number of Update Retries

    This sets the number of times Access tries to save a changed record that is locked by another user. Valid values are 0 through 10. The default setting is 2.

  • ODBC Refresh Interval

    This sets the interval after which Access automatically refreshes records that are opened using ODBC. Valid values are 1 through 32,766 seconds. The default setting is 1500.

  • Refresh Interval

    This sets the number of seconds after which Access automatically updates records in Datasheet or Form view. Valid values are 1 through 32,766 seconds. The default setting is 60.

Note   When Access refreshes the current datasheet or form, it does not reorder records, add new records, or remove deleted records. To view these changes, you must requery the underlying records for the datasheet or form.

If a multiuser database uses linked tables, see "Optimizing the Performance of Linked Tables" earlier in this chapter for additional suggestions. If a multiuser database connects to tables in an external SQL database, see "Optimizing SQL Database Performance" earlier in this chapter for additional suggestions.

Optimizing Query Performance

There are several things you can do to make queries run faster. In addition to using the Performance Analyzer to analyze specific queries, consider the following ideas.

  • Index fields on both sides of joins, or create a relationship between those fields and index any field used to set criteria for the query.

    Note   The Microsoft Jet database engine automatically optimizes queries that join an Access table on your hard disk and an ODBC server table (typically an SQL server table) if the Access table is small and the joined fields are indexed. In these cases, Access improves performance by requesting only the necessary records from the server. Make sure that tables you join from different sources are indexed on the join fields.

  • When defining a field in a table, choose the smallest data type or FieldSize that is appropriate for the data in the field. Also, give fields you use in joins the same or compatible data types.
  • When creating a query, do not add fields that you do not need, and clear the Show check box for selection criteria fields whose data you do not want Access to display.
  • Avoid calculated fields in nested queries. If you add a query that contains a calculated field to another query, the expression in the calculated field slows performance in the top­level query.

    For example, suppose this is Query1:

    SELECT Format$(Field1)As TempField FROM Table1
    
    
    

    Query1 is nested in the following query, Query2:

    SELECT * FROM Query1&nbspWhere TempField = 100
    
    
    

    Nesting Query1 in Query2 causes optimization problems due to the expression Format$(Field1) in Query1. For most effective performance, use calculated fields only in the top-level query of nested queries. If that is not practical, use a calculated control on the form or report based on the query to show the result of the expression, instead of nesting the query.

  • When grouping records by the values in a joined field, specify Group By for the field that is in the same table as the field you are totaling (calculating an aggregate on).

    For example, if your query totals the Quantity field in an Order Details table and groups by OrderID, specify Group By for the OrderID field in the Order Details table, not the OrderID field in the Orders table. For greater speed, use Group By on as few fields as possible. As an alternative, use the First function where appropriate.

    If a totals query includes a join, consider grouping the records in one query and then adding that query to a separate query that performs the join. This improves performance in some queries.

  • Avoid restrictive query criteria on calculated and nonindexed columns whenever possible. For more information, see "Optimizing Criteria Expressions in Queries Using Rushmore Technology" later in this chapter.
  • If you use criteria to restrict the values in a field used in a join, test whether the query runs faster with the criteria placed on the one side or the many side of the join. In some queries, you get faster performance by adding the criteria to the former.
  • Use field sorting judiciously, especially with nonindexed fields.
  • If your data does not change often, use make­table queries to create tables from your query results. Use the resulting tables rather than queries as the basis for your forms, reports, or other queries.
  • Avoid using domain aggregate functions, such as the DLookup function, in a query that reads table data. Instead, add the table to the query or create a subquery.
  • If you are creating a cross­tab query, use fixed column headings whenever possible. For more information about sorting or limiting column headings in cross­tab queries, see Access online Help.
  • Use the Between...And, In, and equal (=) operators on indexed columns to optimize queries. For more information, see the following section, "Optimizing Criteria Expressions in Queries Using Rushmore Technology."

If a query includes tables in an external SQL database, see "Optimizing SQL Database Performance" earlier in this chapter for additional suggestions.

Optimizing Criteria Expressions in Queries Using Rushmore Technology

Rushmore is a data­access technology that permits sets of records to be queried efficiently. With Rushmore, when you use certain types of expressions in query criteria, your query runs much faster.

Simple Expressions

Access can optimize simple expressions in the Criteria row of the query design grid or in a WHERE clause in an SQL SELECT statement. A simple optimizable expression can form an entire expression or can appear as part of an expression.

A simple optimizable expression takes one of the following forms:

Indexed field Comparison operator Expression
Expression Comparison operator Indexed field

In these expressions, Indexed field is a field on which an index is constructed.

Comparison operator is one of the following operators: <, >, =, <=, >=, <>, Between, Like, or In. Expression can be any valid expression, including constants, functions, and fields from other tables.

Note   For best results, the comparison value in an expression using the Like operator must begin with a character, not a wildcard character. For example, you can optimize Like "m*" but not Like "*m*".

Examples of Simple Optimizable Expressions

If you have created indexes for the LastName, Age, and HireDate fields in an Employees table, the following are simple optimizable expressions:

[LastName]="Smith"
[Age]>=21
#12/30/90#<[HireDate]
Employees.[LastName]=Customers.[LastName]
[LastName] IN ("Smith", "Johnson", "Jones")
[Age] BETWEEN 18 AND 65


If you have a multiple­field index on the LastName, FirstName fields, the following expression is optimizable:

(([LastName])="Smith") AND (([FirstName])="Pat")


The SQL SELECT statement for the following COUNT(*) query is Rushmore optimizable if there are indexes on the LastName and FirstName fields. This query counts the number of employees who are not named Pat Smith:

SELECT Count(*)FROM Employees
WHERE (Employees.[LastName] <> "Smith")
AND (Employees.[FirstName] <> "Pat")


Complex Expressions

Access can also optimize complex expressions made by combining simple expressions with the AND or OR operators.

A complex optimizable expression takes one of the following forms:

Simple Expression  AND  Simple Expression

– or –

Simple Expression  OR  Simple Expression

The following rules determine query optimization when combining simple expressions in query criteria.

You can use parentheses to group combinations of simple expressions. The preceding rules also apply to combinations of expressions grouped with parentheses.

After you have combined simple optimizable expressions into complex expressions, these complex expressions can, in turn, be combined to form even more complex expressions that are optimizable according to the preceding rules.

Examples of Complex Optimizable Expressions

The following table lists examples of combined simple expressions and the extent to which the result is optimizable.

Note   These examples assume that you have created indexes for the LastName and HireDate fields, but not for the MiddleInitial or FirstName fields.

Additional Notes on Rushmore Optimization

  • The COUNT(*) function is highly optimized for queries that use Rushmore.
  • If the index is descending and the comparison operator is other than equal (=), the query cannot be optimized.
  • Rushmore queries work with Access tables, as well as with Microsoft FoxPro and dBASE tables (DBF files). You cannot use Rushmore with ODBC data sources, however, because Access sends these queries to the ODBC data source instead of processing them locally.
  • You can optimize multiple­field indexes if you query the indexed fields in the order they appear in the Indexes window, beginning with the first indexed field and continuing with adjacent fields (up to and including 10 fields).

    For example, if you have a multiple­field index on LastName, FirstName, you can optimize a query on LastName or on LastName and FirstName, but you cannot optimize a query on FirstName.

Optimizing Filter Performance

If the lists in fields in the Filter By Form window take too long to display or they do not display values from the underlying table, you can set filtering options to change these behaviors. By setting filter options, you can prevent the lists from displaying the underlying table's field values, display field values on the list for certain types of indexed or nonindexed fields only, or change the record limit that determines whether the list displays a field's values.

You can perform this optimization for all tables, queries, and forms in the database, or for specific bound text box controls in forms.

Tip   If you use the same nonindexed field repeatedly to filter records, consider indexing it before changing the following settings. This improves filtering and other search operations on the field.

To optimize Filter By Form performance for all tables, queries, and forms, set the options under Filter By Form Defaults on the Edit/Find tab in the Options dialog box (Tools menu) according to the performance you want to achieve:

  • If the list of values takes too long to display in nonindexed fields only, limit the lists to indexed fields. You can do this by clearing the check boxes for Local Non­indexed Fields and ODBC Fields.
  • If the lists take too long to display in indexed fields because there are too many records in the indexes, also clear the Local Indexed Fields check box.
  • If lists are not displaying the values from indexed or nonindexed fields, make sure that the appropriate check boxes are selected under Show list of values in; or increase the number in the box Don't display lists where more than this number of records read so that it is greater than or equal to the maximum number of records in any nonindexed field in the underlying table.

    If the field is indexed, Access reads only the unique values, not all the values in a field. If the field is not indexed, Access reads all the values in the field. If the number of records it reads is more than the maximum it is allowed to display, Access does not display the values for that field on the list.

To optimize a specific bound text box control on a form, display the form in Design view, display the property sheet for the control, and then set the FilterLookup property according to the performance you want to achieve:

  • If the lists take too long to display in nonindexed fields only, limit the lists to indexed fields. Set the FilterLookup property to Database Defaults; then, on the Edit/Find tab in the Options dialog box (Tools menu), select the Local Indexed Fields check box and clear the Local Non­Indexed Fields and ODBC Fields check boxes.
  • To prevent a list from displaying, regardless of the database defaults, set the FilterLookup property to Never.
  • To force a list to display, regardless of the database defaults, set the FilterLookup property to Always.
  • If lists are not displaying the values from indexed or nonindexed fields, make sure that the FilterLookup property for each field is not set to Never. If it is not, verify that the options under Show list of values in are selected. If they are, increase the number in the Don't display lists where more than this number of records read box so that it is greater than or equal to the maximum number of records in any nonindexed field in the underlying table.

Optimizing Find and Replace Performance

For the fastest searches when using the Find or Replace commands (Edit menu), search for whole field values or the first character within a single indexed field. If you search the same nonindexed field repeatedly, index the field.

Optimizing Form and Subform Performance

There are several things you can do to make your forms run faster:

  • Avoid overlapping controls.
  • Use bitmaps and other graphic objects sparingly.
  • Convert unbound object frames that display graphics to image controls.
  • Use black­and­white rather than color bitmaps.
  • Close forms that are not being used.
  • If the underlying record source includes many records and you want to use the form primarily to enter new records, set the DataEntry property of the form to Yes so that the form opens to a blank record.

    If you open a form with all records showing, Access has to read in each record before it can display the blank record at the end of the recordset. If the form is already open, you can click the Data Entry command on the Records menu to switch to Data Entry mode.

  • Do not sort records in an underlying query unless record order is important, especially with multiple­table queries.
  • Base subforms on queries rather than tables, and include only fields from the record source that are absolutely necessary. Extra fields can decrease subform performance.
  • Index all the fields in the subform that are linked to the main form.
  • Index any subform fields used for criteria.
  • Set the subform AllowEdits, AllowAdditions, and AllowDeletions properties to No if the records in the subform will not be edited. Or set the RecordsetType property to Snapshot.
  • Eliminate code from forms that do not need it by setting the HasModule form property to No.

    The HasModule property specifies whether the form has a form module. A form without a form module loads more quickly and occupies less disk space. If a form or controls on the form do not use event procedures, the form does not require a form module. For example, if your application uses a switchboard form exclusively to navigate to other forms, instead of using command buttons with event procedures, you can use command buttons with macros, or hyperlinks.

    You can still use code with a form that has no form module by calling Function procedures from a standard module using an expression. (You cannot use Sub procedures, because they cannot be called using an expression.) To do this, define a Function procedure in a standard module and then call that function from an event property of the form or a control. For example, to use a command button to call a function to open a form, add an expression like this to the OnClick property of the command button: =OpenDataEntry().

    Note   If a form currently contains event procedures, and you decide to eliminate all event procedure code from that form, you must set the HasModule property to No to completely remove the form module.

In addition to these tips, you can use the Performance Analyzer to analyze specific forms in your database. For information about using the Performance Analyzer, see "Using the Performance Analyzer" earlier in this chapter.

Optimizing List Box and Combo Box Performance

There are several things you can do to make list boxes and combo boxes run faster:

  • Base the list box or combo box on a saved query instead of an SQL statement.

    If you use a wizard to create the list box or combo box, Access automatically sets the RowSource property of the control to an SQL statement. To change the RowSource property to a saved query, click the Build button next to the RowSource property. In the SQL Builder window, click Save on the File menu and enter a name for the query. When you close the SQL Builder window, click Yes when Access asks if you want to update the property.

  • In the query specified in the RowSource property, include only fields that are absolutely necessary. Extra fields can decrease performance.
  • Index both the first field displayed in the list box or combo box and the bound field (if the fields are different).
  • Index any fields used for criteria.
  • In combo boxes, set the AutoExpand property to No if you do not need Access to automatically fill in the text box portion of the combo box with a value that matches the characters that the user types.
  • If the AutoExpand property for a combo box is set to Yes, the first displayed field should have a Text data type instead of a Number data type. In order to find a match in the list, Access converts a numeric value to text. If the data type is Text, Access does not have to do this conversion.
  • If the bound field in a lookup combo box is not the displayed field:
    • Do not use expressions for the bound field or the displayed field.
    • Do not use restrictions in the row source.
    • Use single­table (or query) row sources rather than multiple­table row sources, if possible.
  • Do not create list boxes or combo boxes based on data from linked tables if the data will not change. It is better to import the data into your database, in this case.

For information about optimizing the Filter By Form performance of a list box or combo box, see "Optimizing Filter Performance" earlier in this chapter.

Optimizing Report and Printing Performance

Here are some suggestions for speeding up report and printing performance:

  • Avoid overlapping controls.
  • Use bitmaps and other graphic objects sparingly.
  • Convert unbound object frames that display graphics to image controls.
  • Use black­and­white rather than color bitmaps.
  • Avoid sorting and grouping on expressions.
  • Index fields you sort or group on.
  • Base subreports on queries rather than tables, and include only fields from the record source that are absolutely necessary. Extra fields can decrease subreport performance.
  • Index all the fields in the subreport that are linked to the main report.
  • Index any subreport fields used for criteria.
  • Avoid using domain aggregate functions. Include the field in the report's underlying query or use a subreport.
  • Make sure the report's underlying query is optimized.
  • Use the HasData property or NoData event to determine whether a report is bound to an empty recordset.

Optimizing Visual Basic Performance

In general, you can do more to improve the speed of your code by choosing more efficient algorithms than by implementing particular coding tricks. However, certain techniques can help make your code run faster.

Compile the code in a database application before distributing it to users

In the Module window, click Debug, and then click Compile And Save All Modules.

Always explicitly declare variables

You can require that variables be explicitly declared before they are used in a procedure by selecting the Require Variable Declaration check box under Coding Options on the Module tab in the Options dialog box (Tools menu).

Use the most specific type possible when you declare variables

For example, declare a variable that is used to represent a form as type Form rather than as type Object or Variant. This is especially important if you are working with Automation objects or separate instances of Visual Basic. When working with Automation objects, instead of using a Variant data type or the generic Object data type, declare objects as they are listed in the Modules/Classes box in the Object Browser. This ensures that Visual Basic recognizes the specific type of object you are referencing, allowing the reference to be resolved when you compile.

Use variables to refer to properties, controls, and data access objects

If you refer more than once to the value of a property or control on a form, or to a data access object or its property, create object variables and refer to the variables rather than using full identifiers. This approach is especially effective for speeding up a looping operation on a series of properties, controls, or objects.

Use the Me keyword for form references within an event procedure

When you make form references within an event procedure, use the Me object variable to refer to the form. This restricts the search for the form to the local name space.

Use the IIf function judiciously

Avoid using the IIf function if either of the return expressions takes a long time to evaluate. Access always evaluates both expressions. It is often more efficient to replace the IIf function with an If...Then...Else statement block.

Use string functions when appropriate

Some functions have two versions, one that returns a Variant data type (for example, the Str function) and one that returns a String data type (for example, the Str$ function.) When working with variables declared with the String data type or when writing data directly to random-access files, use functions with names that end with $ if they are available. This makes your operations run faster, because Access does not need to perform type conversions. For more information about string functions, see Access online Help.

Use the Integer or Long data type for math when the size and type of numbers permit

The Variant data type, although more flexible, uses more memory and processor time as it translates between data types. The following table ranks the numeric data types by calculation speed.

Use dynamic arrays and the Erase or ReDim statement to reclaim memory

Consider using dynamic arrays instead of fixed arrays. When you no longer need the data in a dynamic array, use either the Erase statement or the ReDim statement with the Preserve keyword to discard unneeded data and reclaim the memory used by the array. For example, you can reclaim the space used by a dynamic array with the following code:

Erase intArray
While the Erasestatement completely eliminates the array, the ReDimstatement used with the Preservekeyword makes the array smaller without losing its contents.
ReDim Preserve intArray(10, conNewUpperBound)

Erasing a fixed­size array does not reclaim the memory for the array; it simply clears out the values of each element of the array. If each element was a string, or a Variant data type containing a string or an array, then erasing the array would reclaim the memory from those strings or Variant data types, but not the memory for the array itself.

Replace procedure calls with inline code

Although using procedures makes your code more modular, performing each procedure call always involves some additional work and time. If you have a loop that calls a procedure many times, you can eliminate this overhead by removing the procedure call and placing the body of the procedure directly inline within the loop. If you place the same code inline in several loops, however, the duplicate code increases the size of your application. It also increases the chance that you will not remember to update each section of duplicate code when you make changes.

Use constants whenever possible to make your application run faster

Constants also make your code more readable and easier to maintain. If your code has strings or numbers that do not change, declare them as constants. Constants are resolved once when your program is compiled, with the appropriate value written into the code. With variables, however, each time the application runs and finds a variable, it needs to get the current value of the variable. Whenever possible, use the intrinsic constants listed in the Object Browser rather than creating your own.

Use bookmarks instead of the FindNext method to return to a particular record

Using the Bookmark property, you can write a procedure to find a target record, store its bookmark value in a variable, move to other records, and return to the original record by referring to the bookmark. For more information about the Bookmark property, see Access online Help.

Use the FindRecord and FindNext methods on indexed fields

When locating records that satisfy a specified criteria, the FindRecord and FindNext methods are much more efficient than the Seek method when used on a field that is indexed.

Consider reducing the number of procedures and modules

While your application runs, each called procedure is placed in its own public block of memory. Access incurs some overhead in creating and managing these blocks. You can save some of this overhead by combining short procedures into larger procedures.

Organize the modules in an application

Visual Basic loads modules on demand — that is, it loads a module into memory only when your code calls one of the procedures in that module. If you never call a procedure in a particular module, Visual Basic never loads that module. Placing related procedures in the same module causes Visual Basic to load modules only as needed.

Eliminate dead code and unused variables

As you develop and modify your applications, you may leave behind dead code — entire procedures that are not called from anywhere in your code. You may also have declared variables that are no longer used. Review your code to find and remove unused procedures and variables; for example, Debug.Print statements.

To search for references to a particular variable, use the Find command on the Edit menu. Or, if you have Option Explicit statements in each of your modules, you can quickly discover whether a variable is used in your application by removing its declaration and running the application. If the variable is used, Visual Basic generates an error. If you do not see an error, the variable was not used.

If your application has places in which the contents of a string variable or a Variant data type containing a string is not needed, assign a zero­length string ("") to that variable. If you no longer need an object variable, set that variable to Nothing to reclaim the memory used by the object reference.

You can also use compiler directives and conditional compilation to ignore portions of code based on constant values that you specify. For more information about debugging Visual Basic code, see Chapter 7, "Debugging Visual Basic Code," in Building Applications with Microsoft Access 97.

Saving a Database as an MDE File

If the design of forms, reports, and modules is stable, consider saving a database as an MDE file. Saving a database containing code as an MDE file removes the source code from the file and provides some additional optimization of memory use. However, the design of forms, reports, and all code modules cannot be changed in an MDE file. To modify the design of a database saved as an MDE file, you must open the original copy of the database, make the modification to that database, and then re­save it as an MDE database.

For this reason, saving a database as an MDE file is most appropriate for a database where the design of forms, reports, and modules will not be changed, or for the front­end database in a front­end/back­end database application. A front­end/back­end database application consists of two database files. The back­end database contains only the application's tables. The front­end database contains all other database objects (queries, forms, reports, macros, and modules) and links to the tables in the back­end database. Typically, the back­end database is located on a network server, and copies of the front­end database are installed on individual users' computers.

For more information about MDE files, see "Security Features in Microsoft Access" in Chapter 29, "Workgroup Features in Microsoft Access."

Optimizing Microsoft Excel

There are several techniques you can use to optimize Excel for size and speed. In general, size optimizations decrease both the amount of memory required and the amount of disk space required. Speed optimizations usually occur when you modify worksheets so that the recalculation engine in Excel works more efficiently.

Optimizing for Size

If a worksheet contains links to large ranges on external documents, it may require a large amount of disk space and take a long time to open. To prevent this, clear the Save external link values check box on the Calculation tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu). You do not lose the links to the external data, but clearing this option prevents Excel from saving the value with the linked worksheet.

Optimizing for Speed

To optimize Excel for speed, use the following guidelines:

  • Do not select the Precision as displayed check box on the Calculation tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu) unless you really need it.

    When Precision as displayed is off, Excel stores the full precision of a number in memory, and displays only the number of digits specified by the formatting. When Precision as displayed is on, however, Excel performs a math operation on every cell, which rounds the number. The operation forces the precision of the number stored in memory to be equal to the number of decimal places in the cell number format, slowing calculation speed.

  • If you use complicated IF functions on a worksheet, replace them with VLOOKUP or HLOOKUP functions.
  • Avoid using the functions AREAS, CELL, COLUMNS, INDEX, INDIRECT, INFO, NOW, OFFSET, RAND, ROWS, and TODAY.

    Formulas that contain these functions, or dependents of those formulas, must be recalculated every time there is a calculation, because their results may change even if their precedent cells have not changed. If you must use these functions, try to avoid having other calculations depend on their results.

  • Avoid using user­defined functions and names that are complex expressions, which are recalculated more slowly than the equivalent formula in a cell.
  • If your worksheets contain a large number of pictures, you can speed up scrolling by selecting the Show placeholders option for objects on the View tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu).

    If users want to switch quickly between viewing graphics and placeholders, you can assign the following two Visual Basic procedures to custom buttons.

    Sub ViewObjectPlaceholders()
    	ActiveWorkbook.DisplayDrawingObjects =&nbspxlPlaceholders
    End Sub
    
    Sub&nbspViewObjects() ActiveWorkbook.DisplayDrawingObjects =&nbspxlAll End Sub
  • If you have large worksheets that take a long time to recalculate, consider selecting the Manual option on the Calculation tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu).

    Whenever you make a change to the worksheet that necessitates recalculation, Excel displays the word Calculate in the status bar. You can continue to change the worksheet, and when you are finished, press F9 to recalculate manually.

  • Set your users' monitors to use only 16 or 256 colors.

    For writing reports and working with spreadsheets, users may need only 16 or 256 colors, so they can switch to a video driver that supports a lower resolution and fewer colors. They can always switch back if there is no improvement in performance or if their work requires additional video capabilities.

Optimizing Microsoft PowerPoint

There are several adjustments you can make to help PowerPoint run faster and use less disk space. These adjustments have to do primarily with using less memory or using memory more efficiently. This section explains how to make these adjustments.

If you use this operator To combine this expression With this expression The resulting query has these characteristics
AND Optimizable Optimizable Fully optimizable
OR Optimizable Optimizable Fully optimizable
AND Optimizable Not optimizable Partially optimizable
OR Optimizable Not optimizable Not optimizable
AND Not optimizable Not optimizable Not optimizable
OR Not optimizable Not optimizable Not optimizable
NOT N/A Optimizable Not optimizable
NOT N/A Not optimizable Not optimizable
When you combine these expressions With these expression types and operator The resulting query has these characteristics
[LastName]="Smith" AND [Hire Date]<#12/30/90# Optimizable AND Optimizable Fully Optimizable
[LastName]="Smith" OR
[Hire Date]<#12/30/90#
Optimizable OR Optimizable Fully Optimizable
[LastName]="Smith" AND [MiddleInitial]="C" Optimizable AND Not Optimizable Partially Optimizable
[LastName]="Smith" OR [MiddleInitial]="C" Optimizable OR Not Optimizable Not Optimizable
[FirstName]="Terry" AND [MiddleInitial]="C" Not Optimizable AND Not Optimizable Not Optimizable
[FirstName]="Terry" OR [MiddleInitial]="C" Not Optimizable OR Not Optimizable Not Optimizable
Numeric data type Speed
Integer, Long Fastest
Single, Double Next­to­fastest
Currency Next­to­slowest
Variant Slowest
   Virtual Memory

When you run PowerPoint with virtual memory turned off in Windows 95, you may experience slow performance or receive memory­related messages, such as "You do not have enough memory to perform this function." For optimum performance, run PowerPoint with virtual memory turned on. For more information about using virtual memory consult your Windows documentation.

Visual Basic Add­ins

Visual Basic add­ins slow down the launching of PowerPoint if they are loaded automatically. Although PowerPoint add­ins can be created in Visual Basic, consider using DLL add­ins whenever possible for better performance. DLL add­ins load faster than Visual Basic add­ins, and can call Visual Basic add­ins as needed, depending on user input.

A good strategy is to use DLL add­ins for controlling user interface items, and Visual Basic add­ins to carry out the commands added by the DLL. If the DLL add­in is loaded automatically, PowerPoint starts with custom menus, toolbar enhancements, and so forth. When you select one of these customized user interface items, the DLL calls the code in the Visual Basic add­in. This way, PowerPoint loads Visual Basic code and the associated type library on an as­needed basis, which speeds startup considerably.

Image Format and Performance

The amount of time it takes to display an image and the amount of disk space needed to store a graphics file depend on the format of the graphic. There are several adjustments you can make to improve performance where graphics are concerned.

Options for Exporting Pictures

On the Advanced tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu), you can select the best option for exporting pictures: Best for printing or Best for on­screen viewing. This selection governs the size of the resulting file when you export slides and presentations to a graphics format. For information about exporting slides and presentations to a graphics format, see Chapter 20, "Switching to Microsoft PowerPoint."

If you are exporting slides as graphics primarily to print them, you should select the Best for printing check box. Otherwise, always select Best for on­screen viewing for optimum performance. The following table shows the difference in file size depending on which option you select.

Selecting this option Creates a 24­bit bitmap image of this size
Best for printing 24­bit (16 million colors)
Best for on­screen viewing 8­bit (256 colors)
   Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to define the default value for the Export pictures option on the Advanced tab in the Options dialog box (Tools menu) for all PowerPoint users in your workgroup. In the System Policy Editor, set the following policy:

User\PowerPoint 97\Tools_Options\Advanced\Picture

For more information, see "Using Windows System Policies to Customize Office" earlier in this chapter.

File Formats for Compressing Graphics

PowerPoint supports two versatile graphics formats: JPEG File Interchange Format (JFIF) and Portable Network Graphics (PNG). Both JFIF and PNG store bitmaps in a compressed format that can greatly improve PowerPoint performance and reduce overhead of system resources.

PowerPoint can read images in these formats directly, without converting them and without using a filter. Images in any other format must be converted to be displayed. Since PowerPoint can read JFIF and PNG formats directly, images stored in these formats load much faster than images stored in any other format.

Note   Earlier versions of PowerPoint also included support for JFIF files. However, PowerPoint 97 and 98 include an updated JFIF filter that handles more JPEG formats than did the previous JFIF filters included with PowerPoint.

PowerPoint decompresses the images to display them, but stores them in the compressed format. Storing bitmaps as PNG files typically compresses the size of the file by at least 50 percent; and storing full­color, photographic bitmaps as JFIF files typically compresses the file size by 90 percent or more. Not only do these smaller files require less disk space, they travel faster across the network, reduce network traffic, and reduce the overhead of moving presentations to portable computers.

Did You Know?

The commonly used bitmap format (BMP) actually includes many varieties for storing different types of bitmaps. The PNG format, however, is a superset of all BMP formats, so it is possible to convert any BMP format image to a PNG file with no loss of data. Using the PNG format normally compresses bitmaps as well as the GIF format does, but unlike GIF, PNG supports all BMP formats.

GIF, a common format for images on the Internet, can be safely converted to PNG provided the GIF contains only one image. JPEG, the other common format for images on the Internet, cannot reliably be converted to PNG, so PowerPoint supports the standard JFIF filter for compressing these files.

Sharing Graphics Between Versions of PowerPoint

If you are upgrading gradually from earlier versions of PowerPoint to PowerPoint 97 or 98 and a large percentage of users are still running earlier versions, you may want to delay the installation of the PNG and JFIF filters.

At first, when some users are still running an earlier version of PowerPoint, it may be more efficient to store graphics in BMP and JPEG formats. Later, when most users are running PowerPoint 97 or 98, consider storing BMPs as PNG files and JPEGs as JFIF files, and installing PNG and JFIF filters on any computers running earlier versions of PowerPoint. Using these filters, earlier versions of PowerPoint can quickly decompress these images, and you can conserve system resources by storing highly compressed graphics files.

Linking Graphics

To centralize storage of images and keep the size of presentations to a minimum, you can store all images separately from presentation files and link, rather than embed, them in presentations. PowerPoint loads the image in order to display it, but stores only a link rather than a copy of the compressed graphics file. Depending on the size of the original image file, the combination of compressing BMP and JPEG files in PNG or JFIF format and then linking these graphics to presentations can result in significant savings of disk space and reduction of network traffic.

Multimedia and Performance

If you use sound and video files in presentations, you can enhance the performance of PowerPoint depending on where you store the files and how you incorporate them into presentations.

To reduce file size, you can link rather than embed sounds and videos. By default, sound files smaller than 100 KB are embedded in a presentation, and files 100 KB or larger are linked. You can customize this default on the Advanced tab in the Options (Windows) or Preferences (Macintosh) dialog box (Tools menu). Sounds attached to animation effects are embedded regardless of file size, so it is best to use small sound files for these.

   Tip   In Windows 95 and Windows NT Workstation 4.0, you can use a system policy to define the default value for the Link sounds with file size greater than option on the General tab in the Options dialog box (Tools menu) for all PowerPoint users in your workgroup. In the System Policy Editor, set the following policy:

User\PowerPoint 97\Tools_Options\General\Link Sounds File Size

For more information, see "Using Windows System Policies to Customize Office" earlier in this chapter.

Sound files and video clips linked to a presentation play sooner if they are stored in the same folder as PowerPoint than if they are stored elsewhere. To make it easier for PowerPoint to resolve palette colors, slides containing video clips should have a minimum of colors, including those in special effects such as two­color shaded fills.

Optimizing Microsoft Word

The following issues affect overall Word performance. You can control some of these issues during Setup, but other issues must be addressed on each user's computer.

  • Set your users' computers to use the correct video driver for faster screen display.

    Users may not need the highest resolution video driver and the up to 16 million colors their video drivers support. Additional color support in a video driver can dramatically decrease the speed of screen updates when your users scroll or update graphics.

  • Consider setting your users' monitors to use only 16 or 256 colors.

    For writing reports and working with spreadsheets, users may need only 16 to 256 colors, so they can switch to a video driver that supports a lower resolution and fewer colors. They can always switch back if there is no improvement in performance or if their work requires additional video capabilities.

To speed up printing, try the following:

  • If your users print large documents that take several minutes to print, disable any screen savers during the print job, or switch to a blank screen saver.

    Animated screen savers use computer processor time that you can allocate to a print job. For more information, see your screen saver documentation.

  • If your users do not need to continue working while Word is printing, turn off the Background Printing option in Word (Windows only).

    This option allocates processor time to Word during a print job so users can continue working while Word is printing, but this means less processor time is available for printing. To turn off background printing, clear the Background Printing check box on the Print tab in the Options dialog box
    (Tools menu).


Top
Contents | << Previous | Next >> | Index


© 1998 Microsoft Corporation
All rights reserved. Terms of Use.