Updating tables

[Guest]

I import data from a 3rd party file into MyTable. Over time I make changes to
individual records. I then get the 3rd party file again, which has new
records, changed records and deleted records as compared to the file when I
orignally imported. Conceptually, how would I go about updating MyTable to
reflect changes in the most recent 3rd party file?

 

[Douglas J. Steele]

You should be able to do it using 2 queries: one to delete records in
MyTable that are no longer in the latest 3rd party file, and one to update
records that are in both and insert records that are only in the 3rd party
file.

The Delete query would be something like:

DELETE FROM MyTable
WHERE MyTable.ID NOT IN
(SELECT ID From 3rdPartyTable)

To see how to create a query that both updates and inserts, see my November,
2003 "Access Answers" in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Pat Hartman \(MVP\)]

Keep in mind that your own changes will be lost when you update from the 3rd
party file. If you need to keep your own changes, you'll need to keep a
separate table that is not updated by the 3rd party source.

 

[Douglas J. Steele]

Very true, Pat.