Update Records from Another Database

D

damiankinsella

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 or to build it into the
query design page. Deleting the records then re-inserting them would
cause a lose of data.


Any Help? Even an example of an update from another database would be
useful.


Thanks in advance
Momo
 
J

John Spencer

To update the records in the target table from the source table you must be able
to join them on some primary key.

Assumption:
There is a PRIMARY Key (I'll call it PK)
It is ok to just update all the records

UPDATE EducationTbl as E
INNER JOIN EducationTbl IN 'C:\ProgramFiles\AMSRepUpdate.mdb'
ON E.PK = EducationTbl.PK
SET E.FieldA = EducationTbl.FieldA,
E.FieldB = EducationTbl.FieldB
.... For every field in the table ....

If you want to only update records with changed values then you need to add a
where clause. Add it needs to handle NULLS. You can use the NZ function to do this
WHERE NZ(E.FieldA) <> NZ(EducationTbl.FieldA) OR
NZ(E.FieldB) <> NZ(EducationTbl.FieldB) OR ...


More complex would be something like the following, although it might be quicker
if there are a large number of records involved.
WHERE
(E.FieldA <> EducationTbl.FieldA
OR E.FieldA Is Null and EducationTbl.FieldA is Not Null
OR E.FieldA Is Not Null and EducationTbl.FieldA is Null)
OR
(E.FieldB <> EducationTbl.FieldB
OR E.FieldB Is Null and EducationTbl.FieldB is Not Null
OR E.FieldB Is Not Null and EducationTbl.FieldB is Null)
 
Top