Updating an Access table with a table from another database

[Dawn08]

I'm trying to update a table using Access 97 and VB5 with an identical table in another database that contains changes- changes within the records, addtions & deletions of records. The old table can't be replaced because of existing relationships and database replication is really not an option. If anyone has any ideas please respond. Thanks.

[Clunietp]

If there is no reason to keep the data in the second table, and you don't want to use replication, you can delete all data in the table and then add it in record by record from the other table....

[Dawn08]

Originally posted by Clunietp:
If there is no reason to keep the data in the second table, and you don't want to use replication, you can delete all data in the table and then add it in record by record from the other table....



The data can't be deleted because that will cause deletions in other tables of the database. The update works fine if no records or added or deleted. I'm running into a problem when the number/order of the records change.

[JHausmann]

Sounds like you need to have 1) a unique key and 2) two recordsets opened that use the unique key (one in old DB, the other in newdb). Then its a matter of

serial read newdb
read olddb using unique key
if record exists check to see if update is needed
if record doesn't exist, add it
go to serial read until newdb.EOF

[Clunietp]

sorry, I must have misunderstood your situation