Importing data from one DB into another DB

[nfcma368]

How can I import data from a field in one db into a field in another db based
on a "key" field in a record that is the same in both db? What happens if
both db do not have the same number of records?

 

[Chris O'C via AccessMonster.com]

You need an update query. First backup both dbs. Example query below where
2 dbs have a table of the same name, tblSuppliers, the key field to match is
SupID, and the field to be updated is SupCode. The alias TS means the
tblSuppliers table in the other db and the alias TSup means the tblSuppliers
table in the current db.

UPDATE [;DATABASE=c:\db\db.mdb;].tblSuppliers TS
INNER JOIN tblSuppliers TSup
ON TS.SupID = TSup.SupID
SET TS.SupCode = TSup.SupCode
WHERE TSup.SupID = 203

It doesn't matter if the 2 tables have different numbers of records. The
query will spot the ones that match and update the appropriate field in those
records in the destination db.

Chris
Microsoft MVP