ACC2002: How to Force Microsoft Access to Use "Snapshot" Mode for Linked Data (292783)
The information in this article applies to:
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 "snapshot" 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.
REFERENCESFor more information about ODBC registry settings, click Microsoft Access Help on the Help menu, type customize driver settings in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
| Modification Type: | Major | Last Reviewed: | 11/5/2003 |
|---|
| Keywords: | kbhowto KB292783 |
|---|
|