Update records from one database table to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Momo666 - 16 Feb 2006 16:52 GMT

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

Reply to this Message

Lyle Fairfield - 16 Feb 2006 18:33 GMT

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).

Reply to this Message

Momo666 - 17 Feb 2006 08:22 GMT

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.

Reply to this Message

Lyle Fairfield - 17 Feb 2006 16:13 GMT

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

Reply to this Message

Tim Marshall - 16 Feb 2006 18:51 GMT

> 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.

Signature

Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Reply to this Message