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

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

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

   Name	Type	Value
   SnapshotOnly	Binary	01 (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.

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.