Microsoft KB Archive/282391

= ACC2002: Unable to Update Table with Values from Another Table =

Article ID: 282391

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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 </li> Save and run the stored procedure, and then open Ship1. Note that the Phone column has been updated with values from Ship2.</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce the Problem

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

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

Additional query words: pra

Keywords: kbbug kbnofix KB282391

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.