Microsoft KB Archive/292783

= ACC2002: How to Force Microsoft Access to Use &quot;Snapshot&quot; Mode for Linked Data =

Article ID: 292783

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q292783



IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 209514.



SUMMARY
Microsoft Access addresses linked data differently than it does its own native data. This article discusses how Access retrieves linked ODBC data.



MORE INFORMATION
If SQLStatistics, an ODBC API function, reports a unique index on the table, Access goes to the recordset to select the values from the unique key fields that adhere to the user's WHERE clause restrictions. Access then selects only the data that is needed for display by using the unique key values (10 at a time).

If the table does not have a unique key, Access pulls down all the data at once in read-only (snapshot) mode. In most cases, this process is faster after the data is pulled down, although the data cannot be updated.

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To force Access to use &quot;snapshot&quot; mode for linked data, run Registry Editor (Regedit.exe), and then locate the following subkey:   HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC Under the ODBC subkey, change the value of the SnapshotOnly subkey to 01. Under the Engines subkey, add a new key named ODBC, and then add the following binary value to the ODBC subkey:   Name            Type      Value --   -   SnapshotOnly    Binary    01 (True) This restriction will apply only to tables that are linked after you make the change, and the change will simply short-circuit the call to the SQLStatistics function during the linking phase.

In general, a table in recordset mode typically takes 4 to 5 seconds to open, as opposed to 1 to 2 seconds for a table in read-only mode.

