Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Modifying Hardware and Software Configurations

The Access

environment refers to the combination of hardware and software configurations under which Microsoft Access runs. These environmental settings can greatly affect the performance of an Access application.

The easiest way to improve an application's performance is to upgrade its hardware and software configuration. This form of optimization requires no direct intervention from the developer. Plus, a side benefit of most of the environmental changes you can make is that any improvements made to the environment are beneficial to users in all their Windows applications.

Improving the environment involves more than just adding some RAM. It also can mean optimally configuring the operating system and the Access application.

Hardware, Hardware, More Hardware, Please!

The bottom line is that Windows XP, Windows 2000, Windows Server 2003, and Access 2003 all crave hardwarethe more, the better. The faster your users' machines are, and the more memory they have, the better your applications will run. Obtaining additional hardware might not be the least expensive solution, but it certainly is the quickest and easiest thing you can do to improve the performance of your application. You can do a number of things to your system's hardware to improve your application's performance, as the next sections illustrate.

RAM, RAMThat's All I Need!

Memory is what Access craves most, whether you are running under the full version of Microsoft Access or using the runtime version of the product. Microsoft Access

requires 64MB of RAM just to run, and Microsoft

recommends 128MB. Microsoft considers these minimums to be the standard operating environment for Access. Microsoft recommends an extra 8MB of RAM for each application that your user is running simultaneously with Access 2003. Put in a very straightforward way, the more RAM you and the users of your application have, the better. 512MB of RAM is a great environment for Access 2003. In fact, if every one of your users has at least 512MB of RAM, you can stop reading this chapter, because everything else covered here is going to provide you with minor benefits compared to adding more RAM. If you are like most of us, though (meaning that not every one of your users has a Pentium 4 running at 3.2GHz with 512MB of RAM), read on.

NOTE

Developers should have a bare minimum of 256MB of RAM installed on their machines. Remember that this is a minimum! Most developers agree that 512MB of RAM or more is ideal if you intend to do any serious development work, especially if you plan to develop client/server or Internet/intranet applications.

Defragment Your User's Hard Disk

As your computer writes information to disk, it attempts to find contiguous space on which to place data files. As the hard disk fills up, the computer places files in fragmented pieces on the hard disk. Each time your application attempts to read data and programs, it must locate the information scattered over the disk. This is a very time-consuming process. Therefore, it's helpful to defragment the hard disk on which the application and data tables are stored using a utility such as the Disk Defragmenter that ships with Windows 2000, Windows XP, and Windows 2003.

Compact Your Database

Just as the operating system fragments your files over time, Access itself introduces its own form of fragmentation. Each time you add and modify data, your database grows. When you delete data or objects within your database, it does not shrink. Instead, Access leaves empty pages available in which it will place new data. The problem is that these empty pages are not necessarily filled with data. You can free the empty space using the Compact utility, which is included in the Microsoft Access software. The Compact utility frees this excess space and attempts to make all data pages contiguous. You should compact your database frequently, especially if records or database objects (for example, forms and reports) are regularly added and deleted. You can access the Compact utility by selecting Tools, Database Utilities. Then choose the Compact and Repair Database option.

NOTE

It is worth noting that if you plan to distribute an Access application to other users, possibly via the runtime module, it is a good idea to include some means of compacting the database. The runtime module does not allow access to the Compact menu item. The CompactDatabase method enables you to compact a database from within an Access database, but you cannot call this command from within the current application. You must create a second application to use the CompactDatabase method on the original application.

Tune Virtual Memory: Tweak the Paging File

Although Windows 2000, Windows XP, and Windows Server 2003 attempt to manage virtual memory on their own, you might find it useful to provide them with some additional advice. To modify the physical location of the paging file, right-click My Computer, and choose Properties. The System Properties dialog box appears. Click the Advanced tab (see Figure 17.1). Then click Settings under the Performance Options. The Performance Options dialog box appears. Click the Advanced tab (see Figure 17.2). Click the change button under Virtual Memory. The Virtual Memory dialog box appears (see Figure 17.3). This is where you can modify all of the settings for the paging file. It might be useful to move the paging file to a faster disk drive, or to a drive connected to a separate controller card. Any changes you make might adversely affect performance. It is important that you evaluate whether any changes you make will help the situationor perhaps make things worse! In general, it is advisable to let Windows dynamically manage the size of the paging file unless the system is running very low on disk space.

TIP

If Access 2003 or Windows is running on a compressed drive, you can improve performance by moving the paging file to an uncompressed drive. If possible, the paging file should be located on a drive or partition solely dedicated to the paging file, or on a drive or partition that is accessed rarely by other applications. This helps to ensure that the entire paging file remains in a contiguous location on a disk.

Run Access and Your Application Locally

In Chapter 20, "Developing Multiuser and Enterprise Applications," you will learn that it is best to install both the Access software and your application objects on each user's local machine. You should only store the data tables on a network file server. Otherwise, you will be sending dynamic link libraries (DLLs), object linking and embedding (OLE) objects, help files, type libraries, executables, and database objects all over the network wire.

TIP

One very viable option is to run Access 2003 using Windows 2000 Terminal Services or Windows 2003 Terminal Services. In this scenario, Access is installed on a very powerful server machine running Windows 2000 Server Terminal Services or Windows 2003 Terminal Services. Workstations connect to the terminal server using the Terminal Services Client utility. No data travels over the network wire. Each user becomes a session running on the server machine. All processing is done on the server machine. Keystrokes and mouse movements are sent from the client machine to the server, which processes them and sends a screen image back to the client.

Do Everything You Can to Make Windows Itself Faster

It always amuses me that the users with the slowest machines and the least memory have the most accessories running. These accessories include multimedia, fancy wallpapers, and other nifty utilities. If performance is a problem, you might try to see whether eliminating some of the frivolous niceties improves the performance of your application. If it does, encourage the user to eliminate the frills, get more memory, or accept your application's performance. Furthermore, if you are finished using other applications, such as Microsoft Excel, close them. This frees up system memory for Access.

Another tip to make Windows XP run faster is to shut down and restart on a regular basis. Memory tends to get fragmented, making applications run more slowly. Although I can go weeks or months in Windows 2000 or Windows 2003 Server without rebooting, I find it beneficial to reboot my Windows XP machine once a week.

Change Access's Software Settings

In addition to the more obvious measures just outlined, some minor software tweaking can go a long way toward improving performance. Adjusting several settings in the Windows registry can dramatically improve performance. These changes all involve the registry's ISAM section. The properties you might want to change include MaxBufferSize and ReadAheadPages. Both of these settings determine how the Jet Engine uses memory.

MaxBufferSize controls the maximum size of the Jet Engine's internal cache. By default, it is set to optimize performance on most machines. It does this by reading data in 2KB pages, placing the data in a memory cache. The data in the cache is readily available to forms, reports, tables, and queries. Lowering the value for MaxBufferSize frees memory for other tasks. This might be helpful on a machine with a minimum memory configuration.

ReadAheadPages controls the number of 4KB data pages that the Jet Engine reads ahead when performing sequential page reads. This number can range from 031, with the default at 16. The higher this number is, the more efficient Access is at reading ahead so that data is available when you need it. The lower this number is, the more memory is freed up for other tasks.

As you configure any of these settings, remember that what is good for one machine is not necessarily good for the next. The settings for each machine must be optimized with its unique hardware configuration in mind.