ACC1x: Five Parameters to Set in ISAM Section of MSACCESS.INI

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article describes five parameters for Microsoft Access version 1.x that you can set in the ISAM section of the MSACCESS.INI file, found in the WINDOWS directory. For information on setting these parameters for version 2.0, search for "MSACC20.INI" then "Customizing MSACC20.INI Settings" using the Help menu.

The MSACCESS.INI file can contain the following five parameters:

   MaxBufferSize
   ReadAheadPages
   PageTimeout
   LockRetry
   CommitLockRetry

MORE INFORMATION

ISAM parameters are read at only one time: during the launch of the first instance of Microsoft Access. While the parameters can be changed while Microsoft Access is running, these changes do not take effect until all instances of Microsoft Access are closed and Microsoft Access is restarted.

The examples in the following five sections assume the sample parameter values below:

   MaxBufferSize = 512
   ReadAheadPages = 5
   PageTimeout=20 (sets PageTimeout to 2 seconds)
   LockRetry = 100
   CommitLockRetry = 100

MaxBufferSize

Microsoft Access physically stores all database data in 2K-memory pages, generally in a large storage medium, such as a hard disk, RAM drive, or CD-ROM. Once placed in the page cache, memory pages are then used by Microsoft Access to hold data for tables, reports, and so forth.

The MaxBufferSize ISAM parameter defines, in kilobytes, how large a memory page cache to set up. A memory page cache is a section of memory used to temporarily store (or, buffer) the 2K-memory pages that Microsoft Access creates. Microsoft Access uses physical memory (RAM), and, if necessary, virtual memory (for example, a hard disk) to create the page cache that you specify.

Because the page cache stores data in 2K segments, you should set the MaxBufferSize parameter only to an even number (for example, 18, 512, 1024, and so forth). If the parameter is set to an odd number, Microsoft Access uses a MaxBufferSize setting of the given number minus 1.

Because the page cache size is measured in kilobytes, and because each memory page is 2K in size, you can calculate how many memory pages will fit in your new page cache by using the following formula:

   maximum number of memory pages = (MaxBufferSize / 2)

   (18 / 2) = 9 possible memory pages

NOTE: The PageTimeout parameter can also be a factor in this process. Please read the section below titled "PageTimeout."

If the data you need is present in the page cache, Microsoft Access is able to immediately read the memory pages from it. Since Microsoft Access does not have to wait for the cache to be filled from the storage medium, this feature saves you time. However, if the data does not appear on a memory page already present in the page cache, Microsoft Access fills the cache from the storage medium and then retrieves the data from the cache itself.

All memory pages stay in the page cache indefinitely, either until it becomes full or until the computer is turned off. If the page cache becomes full, pages are purged as necessary to make room for the new data.

   Specifications
   ---------------------------------------------------------
   Maximum Setting: 4096 (4096 = 4MB)
   Minimum Setting: 18   (18 = 18K)
   Default Setting: 512
   Granularity: 2 (for example, 512 is valid, but 511 is not)

ReadAheadPages

The ReadAheadPages ISAM parameter specifies the size of another cache, known as the "read-ahead" cache. The read-ahead cache is used by Microsoft Access to cache sequential page reads from a database. Reading data from a read-ahead cache is more efficient than reading it from a storage medium, since sequential data reads can occur more quickly. This is especially true when you are accessing data stored on a network, where speed is increased by sending small numbers of large packets, rather than large numbers of small packets.

The read-ahead cache is not used for all reads, but triggered instead when Microsoft Access detects that a sequential read is taking place. Microsoft Access attempts to detect a sequential read in the following manner:

When Microsoft Access receives a read request, it checks to see if the previous read is adjacent to the current request. If it is, Microsoft Access reads the requested page, plus the next <n> pages (where <n> is the value that the ReadAheadPages parameter is set to) in that direction and places the data in the read-ahead cache. If Microsoft Access correctly detects a sequential read, it then reads the next <n> reads directly from the read-ahead cache, thus improving the speed.

When the read-ahead cache is created, Microsoft Access attempts to place it in the first 640K of memory (also called conventional memory) to benefit from the ability of Microsoft Windows to directly read from and write to conventional memory. A separate read-ahead cache is created for each database open on the host machine. Each library database has its own read-ahead cache.

   Specifications
   -----------------------------
   Maximum Setting: 31
   Minimum Setting: 0
   Default Setting: 16
   Granularity: 1   (1 = 1 page)

PageTimeout

NOTE: The PageTimeout parameter is enabled for non-exclusive (shared) data only.

The PageTimeout ISAM parameter defines the amount of time, in tenths of a second, that a page is held in the memory page cache before it must be refreshed to fulfill the next read of the same page. The following scenario describes how the PageTimeout parameter is used:

If page <x> is read and placed in the memory page cache at time T1, and later another read request is made for page <x> at time T2, the memory page can be read directly from the memory page cache, if T2 minus T1 is less than the PageTimeout setting. Otherwise, Microsoft Access must reread the data from the storage medium to refresh that particular memory page.

NOTE: PageTimeout applies only to situations in which a database or database object is opened with shared access. When a database is opened with exclusive access, only one user has access to the data; as a result, that user always holds the most current data and has no need for refreshed data.

The PageTimeout parameter is used only when operating through the Microsoft Access user interface. It is ignored when running an Access Basic program, unless you allow background processing to occur by periodically calling the DoEvents() statement.

   Specifications
   --------------------------------------------------
   Maximum Setting: 2147483647 (maximum long integer)
   Minimum Setting: 0
   Default Setting: 20
   Granularity: 1 (1 = 1/10 second)

LockRetry

The LockRetry ISAM parameter defines the number of times Microsoft Access tries to lock a page before it reports an error.

For example, if user A tries to lock page <x>, which was previously locked by another user, user A's lock attempt fails. After this failure, Microsoft Access tries <n> more times (where <n> is the value LockRetry is set to) to lock the page. If the lock cannot be secured by the <n>th retry, an error is reported either in the user interface or in Access Basic code.

   Specifications
   --------------------------------------------------
   Maximum Setting: 2147483647 (maximum long integer)
   Minimum Setting: 0
   Default Setting: 20
   Granularity: 1

CommitLockRetry

The CommitLockRetry ISAM parameter is used in conjunction with the LockRetry parameter to determine the number of retries performed when locking pages within transactions. The total number of retries performed is determined by multiplying the two parameters (LockRetry and CommitLockRetry).

For example, if LockRetry has a value of 5 and CommitLockRetry has a value of 6, 30 more retries (5 * 6 = 30) are made after the initial failure to secure a lock.

   Specifications
   --------------------------------------------------
   Maximum Setting: 2147483647 (maximum long integer)
   Minimum Setting: 0
   Default Setting: 20
   Granularity: 1

REFERENCES

For more information about Customizing MSACC20.INI settings, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122246
   TITLE:      ACC2: Sample MSACC20.INI File for MS Access Version 2.0