ACC97: Can't Update Recordset After Refreshing Linked Table (165352)


The information in this article applies to:

  • Microsoft Access 97
This article was previously published under Q165352
Novice: Requires knowledge of the user interface on single-user computers.

SYMPTOMS

After you refresh the link to a linked table by using the Linked Table Manager Wizard, you cannot update the data in a form based on the linked table the first time that you open the form. You receive a message that you cannot update the data.

RESOLUTION

This behavior only occurs if:
  • You use the Linked Table Manager to refresh the link to a linked table.
  • You open a form based on the linked table that has its RecordsetType property set to Snapshot.
  • You open another recordset based on the same table using a form or Visual Basic code and try to add or update a record.
To resolve this problem, close all recordsets based on the table, and then reopen them. For example, close the form with the snapshot-type recordset, close the form with which you are trying to update the recordset, and then reopen them both.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Access 2000.

MORE INFORMATION

If you try to update data using a form, you receive the message: 
   This Recordset is not updatable.

If you try to update data using Visual Basic code, you receive the message: 
   Run-time error '3027':
   Can't update. Database or object is read-only.

This problem occurs the first time you open the form with the snapshot-type recordset and try to update the recordset while the form is open. This includes updating the table directly or using a form or Visual Basic code. Even if you have previously edited the data or if you quit and restart Microsoft Access, the first time you open the snapshot-type form this behavior is exhibited.

Steps to Reproduce Problem


  1. Start Microsoft Access and create a new database named TestUpdate.mdb.
  2. On the File menu, point to Get External Data, and then click Link Tables.
  3. In the Link dialog box, locate and select the sample database Northwind.mdb, and then click Link.
  4. In the Link Tables dialog box, select the Customers table, and then click OK.
  5. Create a new form based on the linked Customers table using the AutoForm: Columnar Wizard.
  6. Switch the form to Design view.
  7. Set the form's RecordsetType property to Snapshot.
  8. Save the form as frmReadOnly, and then close it.
  9. Use the AutoForm: Columnar Wizard to create another new form based on the Customers table.
  10. Save the form as frmUpdate, and then close it.
  11. On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
  12. In the Linked Table Manager dialog box, click to select the check box next to the Customers table, and then click OK.
  13. Click OK when you receive the message that the table was successfully refreshed, and then click Close in the Linked Table Manager dialog box.
  14. Open the frmUpdate form, change some data, and then close the form.
  15. Open the frmReadOnly form, and then minimize it.
  16. Open the frmUpdate form again and try to update a record. Note that you receive the message "This Recordset is not updatable." If you close both forms and reopen them, you will be able to update data using the frmUpdate form.

REFERENCES

For more information about Snapshot-type recordsets, search the Help Index for "snapshot-type Recordset objects."
Modification Type:MajorLast Reviewed:10/22/2003
Keywords:kbbug kberrmsg KB165352
©2003 Microsoft Corporation. All rights reserved.