Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 282391 - Last Review: November 5, 2003 - Revision: 3.0
ACC2002: Unable to Update Table with Values from Another Table
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).
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.
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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
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.
APPLIES TO
- Microsoft Access 2002 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate