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

   Dim rsGlobal As Recordset

   Function InitializeLinkTables()

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

   End Function
				

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.