Update records from one database table to another

[Momo666]

I have used this code to append my records:
INSERT INTO EducationTbl
SELECT *
FROM EducationTbl IN 'C:\ProgramFiles\AMSRepUpdate.mdb';

but I now need to UPDATE the records because some of the data has
altered slightly.

What I need is essentially:
UPDATE INTO EducationTbl
SELECT *
FROM EducationTbl IN 'C:\ProgramFiles\AMSRepUpdate.mdb';

but I do not know how to correctly write this?

Any Help?

Thanks in advance
Momo

[Lyle Fairfield]

I suggest a two step approach, eg:
With DBEngine(0)(0)
.Execute "DELETE * FROM Suppliers WHERE SupplierID IN (SELECT
SupplierID FROM Northwind.mdb.Suppliers)"
.Execute "INSERT INTO Suppliers SELECT * FROM
Northwind.mdb.Suppliers"
End With

This may seem inefficient, but TTBOMK it's what databases do for an
update anyway (that is mark for deletion and append).

[Tim Marshall]

Momo666 wrote:

What I need is essentially:
UPDATE INTO EducationTbl
SELECT *
FROM EducationTbl IN 'C:\ProgramFiles\AMSRepUpdate.mdb';

but I do not know how to correctly write this?

Look up help on update queries in Access help. You obviously come from
a SQL writing background 8) but one of the nice things about Access with
Jet is the query builder. It's a relatively easy way to construct
queries and you can always switch to SQL view to see how Access
constructs the Jet SQL after you put something together on the query
design interface.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Momo666]

Lyle,

I would delete the records and insert them again but there is some data
that would be lost, what I need is to just update the records because
for example test scores are updated weekly.

Thanks for your reply.

[Lyle Fairfield]

UPDATE [SELECT s.*, ns.* FROM Suppliers AS s INNER JOIN
Northwind.Mdb.Suppliers AS ns ON s.SupplierID=ns.SupplierID]. AS
SubQuery SET s.CompanyName = ns.CompanyName;

The syntax here must be exact.

If you are using JET 4.0 then you are laughing because you can
substitute ( ) for [ ] for the subquery and omit the "." and alias as
in:
UPDATE (SELECT s.*, ns.* FROM Suppliers AS s INNER JOIN
Northwind.Mdb.Suppliers AS ns ON s.SupplierID=ns.SupplierID) SET
s.CompanyName=ns.CompanyName

This would mean that you can use [] to delimit your external db,
required if the path has spaces. [] are not nestable as delimiters