ACC2000: Force MS Access to Use "Snapshot" Mode for Linked Data

ID: Q209514

The information in this article applies to:
IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.
Moderate: Requires basic macro, coding, and interoperability skills.


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: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

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.


REFERENCES

For more information about ODBC Registry Settings, click Microsoft Access Help on the Help menu, type "Windows Registry settings" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: snapshot attach attached 


Keywords          : kbusage kbdta OdbcOthr 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: May 13, 1999