Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to force Microsoft Access to use read-only (snapshot) mode for linked data


View products that this article applies to.

This article was previously published under Q292783
Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 (http://support.microsoft.com/kb/322756/ ) How to back up and restore the registry in Windows
Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 209514 (http://support.microsoft.com/kb/209514/ ) .
For a Microsoft Access 2000 version of this article, see 209514 (http://support.microsoft.com/kb/209514/ ) .

↑ Back to the top


Summary

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

↑ Back to the top


More information

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

If the ODBC API function SQLStatistics reports a unique index on the table, Access goes to the recordset to select the values from the unique key fields that meet 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 immediately in read-only (snapshot) mode. In most cases, this process is faster after the data is pulled down, although the data cannot be updated.
To force Access to use snapshot mode for linked data, follow these steps:
  1. Click Start, click Run, type regedit, and then click OK.
  2. If you use Access 2002 or Access 2003, locate the following registry subkey:
    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC
    If you use Access 2007 on a 32-bit version of the Windows operating system, locate the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ODBC
    If you use Access 2007 on a 64-bit version of the Windows operating system, locate the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ODBC
  3. Under the ODBC registry subkey, change the value of the SnapshotOnly registry entry to 01. Under the Engines registry subkey, add a new registry entry named ODBC, and then add the following binary value to the ODBC registry entry:
    Collapse this tableExpand this table
    NameTypeValue
    SnapshotOnlyBinary01 (True)
This restriction applies only to tables that are linked after you make the change. The change merely short-circuits the call to the SQLStatistics function during the linking phase.

Generally, a table in recordset mode typically takes four to five seconds to open, instead of one to two seconds for a table in read-only mode.

↑ Back to the top


References

For 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.

↑ Back to the top


Keywords: KB292783, kbhowto

↑ Back to the top

Article Info
Article ID : 292783
Revision : 6
Created on : 1/9/2008
Published on : 1/9/2008
Exists online : False
Views : 332