An Approach for Updating A Table with New Records

[Tonk]

Dear Ng,

I've got a tblDeerData on server A which I add records to on a daily
basis. The data are scanned forms and auto export directly from the
scanning software to Access each day. I have a copy of tblDeerData
sitting on server B. It does not get populated each day with new
records because I have no control over that table. It is essentially
available for public use. What I would like to do each morning ( I
start work before most folks), is add the records that were added to
the copy of tblDeerData on server A to tblDeerData on server B. I
could of course delete the table on server B and import the new table.
But that isn't very elegant and I don't learn Access that way. I would
welcome any suggestions you might have.

As always, your time and help is truly appreciated.

Mike

 

[kingston via AccessMonster.com]

If you have a unique field (e.g. autonumber) in the table, you can use an
append query to add records from A to B. Access will prevent duplicates so
only new data will be added.

However, if you have the luxury of simply replacing the table, I think it's
the better way to go. Consider what you'd have to do if data within existing
records were changed. How would you copy the changes from A to B? (The
append query only adds new records. You'd also have a problem with deleted
records.) A scan of every field in every record would have to be performed,
a comparison made, and then a data exchange if needed. There is a
replication tool in Access (you can find out more about it in Access's built-
in Help), but a simple make table query (to replace the entire table) would
be an efficient solution to this problem. HTH

 

[Tonk]

I think that is a great suggestion and that is precisely what I will
do. Thank you very much for your help.