ACC2002: Error Inserting Row into Multi-Table View or Function (295250)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q295250
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

When you insert a row into a multi-table view or function in an Access project (.adp), you receive the following error message:
You can't update the record because another user or application deleted it or changed the value of its primary key.

CAUSE

There is no defined relationship between the tables.

RESOLUTION

Create a relationship between the two tables. To create a relationship, follow these steps:
  1. Open the primary table in Design view.
  2. On the View menu, click Relationships.
  3. Click New.
  4. Select the name of the primary table in the Primary Key Table box.
  5. Select the name of the related table in the Foreign Key Table box.
  6. Under the Primary Key Table box, select the field(s) that make up the primary key in the primary table.
  7. Under the Foreign Key Table box, select the field(s) that make up the foreign key in the related table.
  8. On the File menu, click Save.
  9. Click Yes when Microsoft Access prompts you to continue.

    NOTE: You will receive an error during the save operation if data in the related table violates referential integrity (one or more values in the foreign key field do not exist in the primary key field of the primary table). If you want to avoid this, you can click to clear the Check existing data on creation check box before saving.
  10. Quit and then restart Microsoft Access.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open an Access project (.adp) file that is connected to a Microsoft SQL Server 2000 database.
  2. Create a new table with the following fields and properties:
       Table: ParentTable
   ---------------------------
   Field Name: ParentID
   Data Type: int
   Allow Nulls: No (unchecked)
   Identity: Yes

   Field Name: ParentName
   Data Type: varchar
   Length: 50
   Allow Nulls: Yes (checked)

   Table Properties: ParentTable
   -----------------------------
   PrimaryKey: ParentID
  3. Save the table as ParentTable, and then close it.
  4. Create a new table with the following fields and properties:
       Table: ChildTable
   ---------------------------
   Field Name: ChildID
   Data Type: int
   Allow Nulls: No (unchecked)
   Identity: Yes

   Field Name: ChildName
   Data Type: varchar
   Length: 50
   Allow Nulls: Yes (checked)

   Field Name: fParentID
   Data Type: int
   Allow Nulls: Yes (checked)
   
   Table Properties: ChildTable
   ----------------------------
   PrimaryKey: ChildID
  5. Save the table as ChildTable, and then close it.
  6. On the View menu, point to Database Objects, and then click Queries.
  7. Click New.
  8. In the New Query dialog box, click Design View, and then click OK.
  9. In the Add Table dialog box, click the ParentTable and the ChildTable tables, click Add, and then click Close.
  10. Join the two tables on ParentID to fParentID.
  11. Select all fields from both tables in the query design grid. When you are finished, the view's SQL should appear as follows: 
       SELECT 
      ParentTable.ParentID, ParentTable.ParentName, ChildTable.ChildID,
      ChildTable.ChildName, ChildTable.fParentID
   FROM
      ParentTable INNER JOIN
      ChildTable ON ParentTable.ParentID = ChildTable.fParentID
  12. Save the view as ParentChildView.
  13. On the Query menu, click Run.
  14. Add a new record by editing the ParentName and ChildName fields. The ParentID and ChildID Identity fields should be automatically populated.
  15. On the Records menu, click Save Record.
Note that you receive the error message:
You can't update the record because another user or application deleted it or changed the value of its primary key.
Then, the newly inserted record disappears, although a record was successfully added to the child table.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug kberrmsg kbnofix KB295250
©2003 Microsoft Corporation. All rights reserved.