Microsoft KB Archive/165352

= ACC97: Can't Update Recordset After Refreshing Linked Table =

Article ID: 165352

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



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.

