C H A P T E R 7 | Part 2 Deploying Microsoft Office | Microsoft Office Resource Kit |
Customizing and Optimizing Microsoft Office |
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
|
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. |
The Office applications use keys in the Windows registry to record the values of userdefined 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.
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
– 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, doubleclick 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, doubleclick 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 doubleclick 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
The System Policy Editor creates an icon for that user or group in the main window.
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
The System Policy Editor creates an icon for that computer in the main window.
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 |
|
|
Client Computer Requirements for System Policies
Client computers must meet the following requirements to use system policies:
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 userspecific 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 computerspecific 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. |
|
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.
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:
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 applicationspecific commands (commands one through four on the submenu), or the Officewide 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 applicationspecific commands on the Microsoft on the Web submenu
– 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).
– 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.
User\application\Internet\Help_Microsoft on the Web
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 Officewide commands on the Microsoft on the Web submenu
– 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).
– 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.
User\Office 97\Internet\Help_Microsoft on the Web
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
– 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.
– 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.
User\Office 97\Internet\Help_Microsoft on the Web
&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/
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, addons, 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 Officewide and applicationspecific information. |
Send Feedback | An Officewide 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
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. |
|
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.
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
To view a list of all database objects at once, click All in the Object Type box.
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 randomaccess 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:
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 LowMemory Computer
If the Windows desktop has a wallpaper (fullscreen 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:
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.
These changes affect only Access 97.
These settings affect only a particular database application or session of Access. You use the /profile commandline 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 readahead caching, writebehind 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.
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 highwater 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 highwater mark.
By default, Microsoft Jet allocates memory for its internal buffer on an asneeded 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 highwater 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.
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.
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 peertopeer 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 serverbased networking system that manages lock retries itself, such as Windows NT Server or Novell NetWare, there is no need to change this setting.
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.
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 welldesigned 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
The first two panels of the wizard contain introductory information about normalization.
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 MultipleField Indexes When Joining Multiple Fields
If you use multiplefield 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 multiplefield 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 multiplefield 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 multiplefield 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 singlefield index on FirstName to optimize the query.
The simplest approach is to add multiplefield indexes to fields used in multiplefield joins and also add a singlefield index to any field on which you use criteria to restrict the values in the field. You can add both a multiplefield index and a singlefield 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:
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.
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.
This allows Access to transfer less data over the network.
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.
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:
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.
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.
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.
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:
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, Addins 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:
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.
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.
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 recordlocking 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):
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.
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.
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.
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.
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.
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.
For example, suppose this is Query1:
SELECT Format$(Field1)As TempField FROM Table1
Query1 is nested in the following query, Query2:
SELECT * FROM Query1 Where 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.
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.
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 dataaccess 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.
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.
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 multiplefield 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")
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
For example, if you have a multiplefield index on LastName, FirstName, you can optimize a query on LastName or on LastName and FirstName, but you cannot optimize a query on FirstName.
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 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:
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:
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.
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:
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.
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:
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 intArrayWhile 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 fixedsize 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 zerolength 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 resave 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 frontend database in a frontend/backend database application. A frontend/backend database application consists of two database files. The backend database contains only the application's tables. The frontend database contains all other database objects (queries, forms, reports, macros, and modules) and links to the tables in the backend database. Typically, the backend database is located on a network server, and copies of the frontend 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."
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.
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.
To optimize Excel for speed, use the following guidelines:
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.
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.
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 = xlPlaceholders End Sub
Sub ViewObjects() ActiveWorkbook.DisplayDrawingObjects = xlAll End Sub
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.
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
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 | Nexttofastest | ||
Currency | Nexttoslowest | ||
Variant | Slowest | ||
Virtual Memory |
When you run PowerPoint with virtual memory turned off in Windows 95, you may experience slow performance or receive memoryrelated 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 addins slow down the launching of PowerPoint if they are loaded automatically. Although PowerPoint addins can be created in Visual Basic, consider using DLL addins whenever possible for better performance. DLL addins load faster than Visual Basic addins, and can call Visual Basic addins as needed, depending on user input.
A good strategy is to use DLL addins for controlling user interface items, and Visual Basic addins to carry out the commands added by the DLL. If the DLL addin 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 addin. This way, PowerPoint loads Visual Basic code and the associated type library on an asneeded basis, which speeds startup considerably.
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 onscreen 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 onscreen viewing for optimum performance. The following table shows the difference in file size depending on which option you select.
Selecting this option | Creates a 24bit bitmap image of this size |
Best for printing | 24bit (16 million colors) |
Best for onscreen viewing | 8bit (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 fullcolor, 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.
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.
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.
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.
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:
Animated screen savers use computer processor time that you can allocate to a print job. For more information, see your screen saver documentation.
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).
|