ACC2000: How to Force Microsoft Access to Use "Snapshot" Mode for Linked Data (209514)

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.