I'm an old hand at databases but very new (a few weeks) to coding. This regards an Access 2010 db, although if there are any differences in 2013 it would be useful info (but not a barrier for this problem).
I have many attachments and their notes (sometimes multiple objects in a single attachment field, sometimes 1) in many fields, and indeed tables that I need to copy across to a new database. I realise that I can't use a simple Update or Append with attachments being a multipart field.
I can link or import all the relevant tables and make it a simpler local operation. I know this is possible, but the examples and code I have found around the web don't seem to fit directly. Given the complexities of what the documents are and how they are related, I think it's best for me to go through a field at a time and copy them over, rather than any db wide affair.
If we greatly simplify this to terms of
[tbl_Old].[fld_OldIndex] (a unique ID text field, matching that in the tbl_New)
[tbl_Old].[fld_OldAttach] (an attachment field)
[tbl_Old].[fld_OldNotes (sometimes text, sometimes memo fields)
[tbl_Old].[fld_OldDate (date/time field)
[tbl_New].[fld_NewIndex] (a unique ID text field matching that in the tbl_Old)
[tbl_New].[fld_NewAttach] (an attachment field)
[tbl_New].[fld_NewNotes (a memo field)
[tbl_New].[fld_NewDate (date/time field)
Could someone show me how to code a loop that runs through every field of the old table and attaches the file (or copies it) to the new attachment field, and does the same with the matching notes and date.
I can then make various passes changing the name of fields and tables to suit.
Also where would I place this code? In a module? Create a button on a blank form and make it an On click event?
Many thanks for your help!
I have many attachments and their notes (sometimes multiple objects in a single attachment field, sometimes 1) in many fields, and indeed tables that I need to copy across to a new database. I realise that I can't use a simple Update or Append with attachments being a multipart field.
I can link or import all the relevant tables and make it a simpler local operation. I know this is possible, but the examples and code I have found around the web don't seem to fit directly. Given the complexities of what the documents are and how they are related, I think it's best for me to go through a field at a time and copy them over, rather than any db wide affair.
If we greatly simplify this to terms of
[tbl_Old].[fld_OldIndex] (a unique ID text field, matching that in the tbl_New)
[tbl_Old].[fld_OldAttach] (an attachment field)
[tbl_Old].[fld_OldNotes (sometimes text, sometimes memo fields)
[tbl_Old].[fld_OldDate (date/time field)
[tbl_New].[fld_NewIndex] (a unique ID text field matching that in the tbl_Old)
[tbl_New].[fld_NewAttach] (an attachment field)
[tbl_New].[fld_NewNotes (a memo field)
[tbl_New].[fld_NewDate (date/time field)
Could someone show me how to code a loop that runs through every field of the old table and attaches the file (or copies it) to the new attachment field, and does the same with the matching notes and date.
I can then make various passes changing the name of fields and tables to suit.
Also where would I place this code? In a module? Create a button on a blank form and make it an On click event?
Many thanks for your help!
Last edited: