ACC2002: Unable to Update Table with Values from Another Table (282391)

The information in this article applies to:

Microsoft Access 2002

This article was previously published under Q282391

Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

When you try to create an Update query that updates the values in one table with values from another table, the process cannot be completed because the source table disappears from the query grid.

RESOLUTION

To resolve this issue, bypass the graphical query designer and type the SQL statement manually; to do so, follow these steps:

Open the sample project NorthwindCS.adp.
Make two copies of the Shippers table: Ship1 and Ship2.
Open Ship2 and modify the phone numbers in the Phone field.
Click Queries, and then double-click Create Stored Procedure in Designer.
On the View menu, point to Show Panes, and then click SQL.

Type the following SQL syntax:

UPDATE Ship1 
SET Ship1.Phone = Ship2.Phone 
FROM Ship1, Ship2 
WHERE Ship1.ShipperID = Ship2.ShipperID

Save and run the stored procedure, and then open Ship1. Note that the Phone column has been updated with values from Ship2.

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 Problem

Open the sample project NorthwindCS.adp.
Make two copies of the Shippers table: Ship1 and Ship2.
Open Ship2 and modify the phone numbers in the Phone field.
Click Queries, and then double-click Create Stored Procedure in Designer.
Add both Ship1 and Ship2 to the grid.

Note that they are correctly joined on ShipperID.
Select the Phone field in Ship1 to add it to the field grid.
On the Query menu, click Update Query.
In the Update Table dialog box, select dbo.Ship1 as the table to update. Note that the Ship2 table disappears from the grid.

Modification Type:	Major	Last Reviewed:	11/5/2003
Keywords:	kbbug kbnofix KB282391