ACC97: One Linked Table Opened Read-Only Makes All Read-Only

Last reviewed: November 11, 1997
Article ID: Q172160
The information in this article applies to:
  • Microsoft Access 97

SYMPTOMS

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

When you open a linked table as read-only with the OpenRecordset method in code, all linked tables in that database are read-only until the recordset is closed.

RESOLUTION

You can either open linked tables read-write instead of read-only, or you can create a global recordset variable and run the following code when the database opens:

   Dim rsGlobal As Recordset

   Function InitializeLinkTables()

      Dim mydb As Database
      Set mydb = CurrentDb
      Set rsGlobal = mydb.OpenRecordset("select * from Shippers")

   End Function

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97.

This problem no longer occurs in Microsoft Jet Database Engine version 3.51, which is available from the Microsoft Software Library.

For information on how to obtain Microsoft Jet Database Engine 3.51, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172733
   TITLE     : ACC97: Updated Version of Microsoft Jet 3.5 Available on MSL

MORE INFORMATION

When you open a recordset against a linked table, Microsoft Access caches a Table object with the properties of the first linked table you specify. When you open additional linked tables, they are opened with the cached information. If the first linked table is opened as read-only, all additional linked tables opened are opened as read-only.

Steps to Reproduce Problem

NOTE: There may be other steps that exhibit this behavior. The following steps outline just one known way of duplicating this problem.

  1. Create a new database.

  2. Create a linked table to the Suppliers table in the sample database Northwind.mdb.

  3. Create a linked table to the Shippers table in the sample database Northwind.mdb.

  4. Add the following code to a new module:

          Function TestReadOnly()
    

             Dim dbTest As Database
             Dim rsTest As Recordset
             Set dbTest = CurrentDb
             Set rsTest = dbTest.OpenRecordset("Select * From Shippers", _
                          dbOpenDynaset, dbReadOnly)
             Stop
             rsTest.Close
    
          End Function
    
    

  5. Open the Debug window, type the following line, and then press ENTER:

          ?TestReadOnly
    

    NOTE: The code will pause on the Stop line in the code.

  6. From the Database window, open the Shippers table and note that you cannot modify any data as expected.

  7. From the Database window, open the Suppliers table and try to change the Company Name of any supplier.

    RESULT: The Suppliers table is read-only as described in the "Symptoms" section.

  8. Return to the Module window and press F5 to resume running the code.

  9. From the Database window, open the Suppliers table and try to change the Company Name of any supplier.

    RESULT: You can now modify data appropriately.

Keywords          : MdlDao kbprg
Version           : WINDOWS:97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbbug kbhowto


================================================================================


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: November 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.