Results 1 to 4 of 4
  1. #1
    Aaron5714 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    48

    Append Tables With Attachment Field - Possible Work-Arounds

    I have two databases that I need to merge into one. The structure of each DB is identical, however the data is different. My original thought was to import all the tables into one DB, then use Append queries to merge the similar tables. The problem is that the main table in each DB has an Attachment field (using the Attachment data-type). After doing some research, it seems that you cannot use an Append query that references a table that contains a multivalued field (such as the Attachment data-type).



    Every record has at least one attachment stored in this field, so removing the Attachment field is not possible. So my question is, if I have two identical tables, each with an Attachment field, how can I combine them into one table?

    The only idea I had was to write a VBA procedure that would loop through all the records in the main table and save each attachment in a folder outside the DB, then delete the attachments. I could then merge the two tables using an Append query. Finally, I would run another VBA procedure that would load each saved file back to the appropriate record in the DB. The procedure that saves the attachments would have to write the primary key for the record they were attached to in the filename (or create a new sub-folder that is named with the primary key value), then the procedure that loads the files could read that value from the file or folder name and know which record to attach the file to.

    Obviously that approach is somewhat complex so I was hoping there is an easier way to do this. Anyone have any ideas?

    Thanks,
    Aaron

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    One method

    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.

  3. #3
    Aaron5714 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    48
    Thanks Dal! This definitely seems like a better approach. I'll give it a try. Appreciate the quick reply!

    Quote Originally Posted by Dal Jeanis View Post
    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.

  4. #4
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    No prob. Best wishes.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with append query for attachment field
    By ahmadrezaahmad in forum Queries
    Replies: 9
    Last Post: 06-29-2013, 09:08 PM
  2. Replies: 16
    Last Post: 04-30-2012, 07:12 AM
  3. Replies: 8
    Last Post: 04-13-2012, 12:25 PM
  4. Replies: 16
    Last Post: 04-11-2012, 10:56 AM
  5. Replies: 11
    Last Post: 11-17-2010, 03:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums