
Originally Posted by
Dal Jeanis
You don't need to take the attachments outside Access. Here's what I'd do -
(1) back up the database
(2) In a test copy of the database, copy one of the tables to a new table, that will become the merged table.
(3) Into the new (merged) table, append all the records from the second table, ignoring the attachments.
(4) Loop through the old table, and for each record, copy the attachment to the same record in the new merged table.
(5) verify that the data all made it.
If the data all looks good, then
(6) back up the database again
(7) delete the old tables
(8) back up the database again - that's the official backup of your new database
(9) move the old version of the database out of the way
(10) move the updated database into place
Or some such sequence designed to make sure that you get three or more chances to verify that you haven't screwed up your database before you do anything irrecoverable. Also, obviously, you'd have to figure out in the append step how to identify the same record again, if you're using an autokey as the pk for each file. I'd probably append a temporary field onto the table structure, to preserve the key from the prior table during this process. In step 7, you'd delete that oldkey field.