You may receive an error message when you try to save an edited record that is based on a multi-table view in Access (837937)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002

This article applies only to a Microsoft Access project (.adp).

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

SYMPTOMS

When you edit a record in a form that is based on a multi-table view, and you are working in a multi-user environment, you may be unsuccessful when you try to save the changes to the record. You may also receive the following error message in the Write Conflict dialog box:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make the changes.

If you click either Copy to Clipboard or Drop Changes in the Write Conflict dialog box, you may notice that the changes that you made to the record are undone, and that the changes to the record that were made by the other user are now visible. However, if you edit the same record again, and you try to save the record, you may receive the error message that is mentioned in the "Symptoms" section again.

You may also notice the error message that is mentioned in the "Symptoms" section intermittently when you click Save Record.

Note You may not see this behavior when you use Microsoft Access 2000.

WORKAROUND

To work around this problem, use one of the following methods:
  • Update the form that is based on the multi-table view

    On the first occurrence of the error message that is mentioned in the "Symptoms" section, you must click either Copy to Clipboard or Drop Changes in the Write Conflict dialog box. To avoid the repeated occurrence of the error message that is mentioned in the "Symptoms" section, you must update the recordset in the form before you edit the same record again.

    Note To update the form, click Refresh on the Records menu.
  • Use a main form with a linked subform

    To avoid the repeated occurrence of the error message that is mentioned in the "Symptoms" section, you can use a main form with a linked subform to enter data in the related tables. You can enter records in both tables from one location without using a form that is based on the multi-table view.

    To create a main form with a linked subform, follow these steps:
    1. Create a new form that is based on the related (child) table that is used in the multi-table view. Include the required fields on the form.
    2. Save the form, and then close the form.
    3. Create a new form that is based on the primary table that is used in the multi-table view. Include the required fields on the form.
    4. In the Database window, add the form that you saved in step 2 to the main form.

      This creates a subform.
    5. Set the Link Child Fields property and the Link Master Fields property of the subform to the name of the field or fields that are used to link the tables.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

In a multi-user environment, Microsoft Access project (.adp) uses a technique that is named optimistic record locking to handle record contention. Therefore, when more than one user is working with the same record at the same time, one of the users may receive the error message that is mentioned in the "Symptoms" section. However, the error message that is mentioned in the "Symptoms" section may also intermittently appear when all the following conditions are true:
  • The form is based on a multi-table view.
  • The multi-table view is based on the tables that are involved in a parent-child relationship.
  • The record that is being edited has been changed and committed by another user since you began editing the record.
You may notice that the same problem occurs when you use the multi-table view directly in a multi-user environment.

Steps to reproduce the problem

  1. Start Access.
  2. Open the NorthwindCS.adp sample database project.
  3. In the Database window, click Queries in the Objects section.
  4. In the right pane, double-click Create view in designer.
  5. In the Add Table dialog box, select both Orders and Order Details from the list on the Tables tab, and then click Add.
  6. Click Close.
  7. Type or paste the following query in the SQL pane:
    SELECT     
	dbo.Orders.OrderID, 
	dbo.[Order Details].ProductID, 
	dbo.[Order Details].Quantity, 
	dbo.Orders.ShipName
FROM         
	dbo.Orders 
	INNER JOIN
	dbo.[Order Details] 
	ON 
		dbo.Orders.OrderID = dbo.[Order Details].OrderID

    Note If the SQL pane is not visible, point to Show Panes on the View menu, and then click SQL.
  8. Save the view. Name the view OrderView.
  9. On the View menu, click Datasheet View.
  10. Edit a record in the OrderView view.

    Note Make sure that the record has not been saved.
  11. Open another instance of the NorthwindCS.adp sample database project.
  12. Edit the same record that you edited in step 10. On the Records menu, click Save Record.

    You may receive the error message that is mentioned in the "Symptoms" section.

REFERENCES

For more information about handling concurrent updates in Access 2002, click Microsoft Office Access Help on the Help menu, type About handling concurrent updates in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about handling concurrent updates in Access 2003, click Microsoft Access Help on the Help menu, type About handling concurrent updates in the Search for box in the Assistance pane, and then click Start searching to view the topic.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

295225 ACC2002: Write Conflict error when you add a record in multi-table view

Modification Type:MinorLast Reviewed:2/1/2005
Keywords:kbUser kbADP kberrmsg kbprb KB837937 kbAudDeveloper kbAudEndUser
©2004 Microsoft Corporation. All rights reserved.