Solved

Update one table from another table in MS Access

Posted on 2002-07-11
7
138 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 69

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now