By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,302 Members | 1,716 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,302 IT Pros & Developers. It's quick & easy.

Update records from one database table to another

P: n/a
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

Feb 16 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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).

Feb 16 '06 #2

P: n/a
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
Feb 16 '06 #3

P: n/a
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.

Feb 17 '06 #4

P: n/a
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

Feb 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.