Microsoft KB Archive/209514

= ACC2000: How to Force Microsoft Access to Use "Snapshot" Mode for Linked Data =

Article ID: 209514

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209514



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 2002 version of this article, see 292783.



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. It then selects only the data needed for display, 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, though 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 "snapshot" mode for linked data, run the Windows Registry Editor (Regedit.exe) and locate the following key:   HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC Under the ODBC key, change the value of the SnapshotOnly key to 01. Under the Engines Key, add a new Key named ODBC, and then add the following binary value to the ODBC Key:   Name            Type      Value --   -   SnapshotOnly    Binary    01 (True) This restriction will apply only to tables linked after you make the change and will simply short-circuit the call to the SQLStatistics function during the linking phase.

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

