Solved

Update one table from another table in MS Access

Posted on 2002-07-11
7
140 Views
Last Modified: 2010-05-02
Can any one tell me that in MS Access 2000 Queries
like SQL Server Update .. From clause works on not.

I want to use "UPDATE tablename1 FROM tablename2 SET ..."


I mean I have to Update some fields of table A with table B fields on some Key.


Pl. let ke know ASAP.

Thanks

0
Comment
Question by:hnd_ra
7 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 7146602
For MS Access
=============
INSERT INTO Table1 IN 'ExternalDatabasePath1' SELECT *
FROM Table1 IN 'ExternalDatabasePath2';
where externaldatabasepath1 & 2 are something like c:\databases\db1.mdb & c:\databases\db2.mdb
(INSERT INTO Table1 SELECT * FROM Table2  IN 'c:/aaa/bbb/Database.mdb')
0
 

Author Comment

by:hnd_ra
ID: 7146615
Dear iboutchkine ,
       Thanks, But I have to update not insertion, And Table1 and Table2 r having different structure.

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7146635
what you are trying to do sounds VERY suspect, as it is USUALLY not a good idea to have the SAME data in two different tables , unless there is VERY good reason to have this duplication.  Why do you not simply create query to JOIN the two tables, and then retrive the data from Table2.

Also, if the two tables have DIFFERENT STRUCTURES, then the question becomes which fields from table 1 are to be updated from which fields in Table2.  You will need to provide a lot more descriptive information about the two tables, before anyone can help you.

I doubt that the Access query engine is smart enough to KNOW which fields in the two tables are the same (name is NOT always enough).

Arthur Wood
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7146645
as an alternative, go into the Access query Builder, build the necessary UPDATE query, and then look at the SQL that the Query Builder will generate to carry out the necessary Update.

Arthur Wood
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 20 total points
ID: 7146653
the Query Builder in Access 2000 will genrate the UPDATE something like this:

UPDATE Table1 INNER JOIN Table1 ON Table1.OrderID = Table2.OrderID SET Table1.CustomerID = [Table2].[CustomerID], Table1.EmployeeID = [Table2].[CustomerID], Table1.OrderDate = [Table2].[OrderDate];

Arthur Wood
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7146656
the beginning should read:

UPDATE Table1 INNER JOIN Table2 ON Table1. ...
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7147878
the syntax is (at least with SQL Server):

Update AliasA
Set Field1 = AliasB.Field
FROM Table1 AS AliasA
inner join Table2 AS AliasB
ON AliasA.FieldKey = AliasB.FieldKey
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question