ACC: Factors When Opening Attached ODBC Tables in MS Access

Last reviewed: May 20, 1997
Article ID: Q103255

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This articles describes the factors that determine whether Microsoft Access will open an attached ODBC table in read-only mode or in read/write mode.

MORE INFORMATION

The following are three factors that can cause Microsoft Access to open an attached ODBC table in read-only mode:

When the attached table does not have a unique index in the data source.

Microsoft Access is built on a keyset-driven cursor model, in which data is retrieved and updated based on key (or unique index) fields. If the key does not exist in the back-end table, Microsoft Access opens the table in read-only mode and the user is not able to modify the data.

If the ODBC data source is set to read-only by either the DBMS security system or the network security system.

Microsoft Access determines this status by calling the ODBC application programming interface (API) SQLGetInfo() with fInfoType=SQL_DATA_SOURCE_READ_ONLY. If *rgbInfoValue=TRUE, then the data source is read-only; as a result, the attached table is opened in read-only mode.

If cursors are treated by the ODBC driver in a certain way. When a transaction is committed or rolled back, the ODBC driver can:
```
    1. Preserve the cursor.
    2. Close the cursor.
    3. Close and delete the cursor.
```
A cursor is a construct that is defined to point to a record (or set of records) in the table. The data is then manipulated by referencing the cursor.
If the driver exhibits behavior 3, Microsoft Access opens the table in read-only mode. To determines this, Microsoft Access first calls the ODBC API SQLGetInfo() with fOption=SQL_TXN_CAPABLE. If
```
      *rgbInfoValue=TRUE,
```
Microsoft Access calls SQLGetInfo with
```
      fInfoType=SQL_CURSOR_COMMIT_BEHAVIOR
```
followed by
```
      fInfoType=SQL_CURSOR_ROLLBACK_BEHAVIOR.
```
If either of these values is 0, the ODBC driver closes and deletes the cursors on committals or rollbacks.

Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0
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.