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

Last reviewed: May 20, 1997
Article ID: Q100972
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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

MORE INFORMATION

If SQLStatistics(), an ODBC API function, reports a unique index on the table, Microsoft 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, Microsoft Access pulls down all the data at once in read-only ("snapshot") mode. In most cases, this process will be faster once the data is pulled down, though the data cannot be updated.

If you want to enforce read-only mode all the time, do the following.

In Microsoft Access 7.0 and 97

IMPORTANT: This section contains information about editing the registry. Before you edit the registry, you should first make a backup copy of the registry files (System.dat and User.dat).

For information about how to edit the registry, view the Changing Keys And Values online Help topic in Registry Editor (Regedit.exe). Note that you should make a backup copy of the registry files (System.dat and User.dat) before you edit the registry.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

In Microsoft Access 97, run the Windows Registry Editor (Regedit.exe) and locate the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines key. Under the Engines Key, Change the value of the SnapshotOnly key to 01.

In Microsoft Access 7.0, run the Windows 95 Registry Editor (Regedit.exe) and locate the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines key. 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)

For more information about changing the ODBC Registry Settings, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q139044
   TITLE     : ACC95: How to Add Former MSACC20.INI ODBC Section to
               Registry

In Microsoft Access 2.0

In Microsoft Access version 2.0, add the following line to the [ODBC] section of your MSACC20.INI file. In Microsoft Access 1.x, add the following line to the [ODBC] section of your MSACCESS.INI file:

   SnapshotOnly=1

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 attachment 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, search the Help Index for "Windows Registry settings," or ask the Microsoft Access 97 Office Assistant.


Additional query words: snapshot
Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.