Copying attachments from one table to another. (1 Viewer)

Kelanen · May 10, 2013

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!

cyberman55 · Jun 20, 2013

Hi Kelanen, you're in luck! I just developed code and a sample database that does exactly what you are looking for. The code allows one to call a function and pass the names of the source and target tables, as well as, optionally, the name of an attachment field. It then creates a table containing all the field definitions of the source table, and loops through the source (with an inner loop through an attachment field) picking up one or more attachments and moving them to the target table. It's fairly generic and I plan to incorporate it in our data migration/upgrade system to allow one to browse to another back end, select it and then cycle through all specified tables, moving data as needed. I'll upload that, more complete solution when I finish it.

Incidentally, I was very irritated when I implemented attachments in my application, only to learn I could not move the data when the time for ugrades came around. My irritation was that MS proudly announced the feature but did not clearly warn about this flaw. It took a 120 hours of very hard work, but I did find a way to fix the problem.

As a new user, I can't get a zipped file to upload, although they say I should be able to do this. So, if you want this sample database, you'll have to go to informedcorp.com and then click on the Access Developers page link (at the top). Just scroll down to the second solution, Data Mover and you're in business.

Kelanen · Jun 20, 2013

Thank you, I've downloaded this, and am taking a look through now - it looks just the job!

Luckily this came just in time (my contract here ends in 6 days), so I'm hoping to do something with this (the problem is still outstanding), which will save some poor temps weeks of depression-inducing manual detach/reattach work.

cyberman55 · Jun 20, 2013

Oh, I know the problems. I'm just amazed the next higher tier of programmers didn't come up with this earlier. In retrospect it would seem to be fairly trivial for a guru to do. I'm getting there, but don't consider myself a guru. I get things done through persistence and just never giving up. The weird thing is that an error is raised on the LoadFromFile statement that the file cannot be found, but once I shut it up (with a resume next) it did the operation anyway. I must have lost a couple of hours on that "issue", and can only assume it's caused by a bug of some sort if not a minor flaw in the code.

I've talked to some of the really good coders and get the sense that they just don't like attachments for a variety of reasons and just work around the problem via storage of links to files rather than the files themselves. Personally, I find this feature very useful and nice for my customers - but I'm lucky since the potential size of my client's databases would be much less than the 2 GB limit even with plenty of attachments. I ship the software with approximately 6000 images and find it better to work with stored links in that case, but employee records with attached pdfs and the like, well it would take a long time to hit the limit and when it does, this code would let one just archive the attachments off to another database for long-term storage.

Please keep in touch and you are welcome - as far as I know, you're the first person outside Informed to have a look at this. Hopefully, you'll find it useful.

Kelanen · Jun 20, 2013

Yes, it's a similar situation here - Attachment fields are used to store copies of letters sent, observation forms, etc - they are are almost all very small 1 page word or pdf docs, but they are very important to track and report on for regulatory reasons.

Beyond a few necessities for this project I've not done any coding since university (20 years ago, before VBA or Objected-Oriented Programming was a twinkle in anyone's eye), so I will probably have some really basic questions getting it working!

cyberman55 · Jun 20, 2013

Well, one way to quickly test is:
1. Rename the source and target tables in the sample database
2. Import the two tables you're concerned with (your source and your target)
3. Rename the two tables imported to "sourcetable" and "targettable"
4. The subforms in frmDataMover will not show correctly unless you change their underlying queries since the field names are different - but that matters not - just click the Move Data to Target button and check the target table manually to see if the data moved.

The only thing I think of that might make it fail is easily corrected: Namely, if you have additional fields in the new target table, you may need some additional statements in the code to insert default data. Or, easier, you could just edit the target table to place the default values in automatically.

If there are related tables that must have data prior - then run the code on them first. I'll be in and out today if you need more help. Nan just got up and is stirring around, so I'll go up and set with her a bit (I've been up since 1:30 AM (or 01:30 as you would probably say).

cyberman55 · Jun 24, 2013

Just a quick note: The savetodisk/loadfile method I used turns out to be inefficient (slow). Albert Kallel, showed me a better way via transfer directly from one table to another.

I uploaded an update to my developers page (home page of informedcorp.com, then click the developer's page link at the very top). I'm hopeful it's much faster (and it is definitely a lot less complex). It's the second one down, called "Data Mover".

That's part of a larger project to allow field upgrading older back ends and the I'm updating that as well since it proved too slow for my taste. That should be finished and uploaded later today or tomorrow morning. Now that Albert solved the problem, it's just a matter of optimizing it.

If you run into issues, feel free to send me a source table and a target table and I'll see if we can get it working.

cyberman55 · Jun 24, 2013

Update on speed: Testing against a table with 30 fields and 1 attachment field having 132,000 records:

Import the entire table via Access' built-in commands: about 10 seconds
Append all records (omitting the attachment field) via a query: ~10 seconds
Move records via new code, including attachment field: roughly 110 seconds
Move records via old code, including attachment field: roughly: 10 minutes (6000 seconds)

Of course, this is hardware dependent on I'm working on a fast workstation. I'd guess time could increase quite a bit with a slow Non-SSD drive.

cyberman55 · Jun 25, 2013

In case anyone stumbles on this: This software machine is running much faster now due to the kind help of Albert Kallal who corrected my inefficient technique - the inner loop of the MoveData function (where the attachments are handled) is entirely his.

deanamiranda · Jan 31, 2014

The sample database has been removed.

Does anyone else have a copy? I'm trying to copy an attachment from one table to another as well.

cyberman55 · Feb 1, 2014

Hi - I just removed all my files from box.net and forgot about these samples. I moved them to Citrix's Sharefile system which doesn't allow anyone to download. It's late here in New Hampshire. In the morning, I'll see if I can upload them to my ISP. Otherwise, you can email me at support@informedcorp.com and I can give you access to these files. I did retain copies locally, so at least it's just a matter of finding a repository. Sorry about that.

cyberman55 · Feb 1, 2014

Hi again, the files are again available on our developer's page here: http://www.informedcorp.com/Access-Development-developers-page.html

The link to this page has been moved to our Resources page.

deanamiranda · Feb 3, 2014

Thank you sooooo much! I went from :banghead: to

. You have made my week!

cyberman55 · Feb 3, 2014

Great - let me know how it works out and/or if you run into any roadblocks. It's pretty complex code, or at least it was for me. I had it running, but slowly and Albert Kallal swooped in with a very elegant re-write of that critical core where it loops through the attachment field.

smudger70uk · Apr 23, 2014

Hi Cyberman,

I stumbled across your demo database as part of my search to try and find a quicker way to move 800 records with 1+ attachments from one web access database to another.

I've taken a copy and given it a go but without success, now it might be that I'm just being stupid but you'd mentioned that the code would work out the table structures but it doesn't seem to do this in my db. I've renamed my source and target tables and when I go to the frmDataMover and select the "Move Data To Target" button I get the following error (highlighted in orange):

Compile error: Expected user-defined type, not project

Function fExistTable(strTableName As String) As Boolean
'found at xxxxxxxx (in the thread) originally from xxxxxx but not specified where
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function

I'm just learning basic code so this is all a bit above my head, any pointers?

Thanks for your assistance

cyberman55 · Apr 23, 2014

Hi, it's tough code - took me weeks to figure out. If we can figure out how to get a copy of the problem child, I might be able to help. If you'd like, just send me an email at support@informedcorp.com and I'll set up an account for you to upload the file.

Did the original DB compile and run before you changed the source and target tables? I don't know if web databases behave differently with this code and if you need to set/reset references. Researching it a bit, I find that sometimes this error message occurs when references aren't set properly. In the debug window, under Tools > References you should see these, from top to bottom:

1. Visual Basic For Applications
2. Microsoft Access 14 Object Library
3. OLE Automation
4. Microsoft Office 14.0 Access Database Engine Objects

It's probably fine if you have others, but you should set these.

I think you have to running Access 2010 (at least) and I haven't tested this on Access 2013.

TNB · Nov 17, 2014

Hi Cyberman,

I am not an advanced programmer. I am just an user and design my own database with a little of code. However, I got a problem today with my database that I hope you can help.

I have 2 tables which is linked to Sharepoint list for multi-users.
By accident, I deleted all of their content, including Attachments. Anyway, I still have local tables to back up for them. So I can easily append data from these table to Sharepoint list, except the Attachment fields.

I tested your "Data Mover" sample with locals table and it works perfectly to copy attachment from one table to another (with many types of files). However, it did not work for the Sharepoint linked table (I just replace the name of the local table with the name of linked table in the function and thought it would work).

So, I don't know if your function can apply to copy attachment from local people to Sharepoint linked table with some modifications. I would highly appreciate it you can give me some modifications in details. Since the linked table contain lots of records with attachments and for multi-users, it would be annoying if I have to ask them attach all the files manually.

Thank you in advance for your help

cyberman55 · Nov 17, 2014

Hi TNB,

I have absolutely no experience with SharePoint lists. This is something I cannot help you with - sorry about that!

CoffeeBunny · Dec 2, 2014

TNB said:
Hi Cyberman,

I am not an advanced programmer. I am just an user and design my own database with a little of code. However, I got a problem today with my database that I hope you can help.

I have 2 tables which is linked to Sharepoint list for multi-users...

Did you get an answer to this? I cannot even find the download of the sample DB from the original poster (referenced website doesn't have a dev's link anymore), however I too am trying to figure out how to code VBA to move attachments from one SharePoint list to another.

I did get some code to work that would find if the list item in the 'legacy' list has attachments, create the attachment folder for the 'new' list item (named with the new Row ID), and then copy over all the attachments from the old list ID over to the newID. The problem I have is that the mkDir function throws a code 75 'Path/File access error' when VBA tries to create the attachment folder. If I could get past that, I have the code working to loop through a recordset in one sharepoint list and copy the attachments to the respective item in the new list. (It works if I manually create the folder first - not really an option when I'm moving thousands of items from one list to another)

cyberman55 · Dec 2, 2014

Hi Coffee Bunny, the new link to download the code sample is posted in one of my responses in the above thread. It's here: http://www.informedcorp.com/Access-Development-developers-page.html Just scroll down to the section: Data Mover (Sample Database and Documentation):

However, I'm not sure if will be of any help with SharePoint lists and I personally have not experience with them.

Copying attachments from one table to another. (1 Viewer)

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

New member

Registered User.

Registered User.

New member

Registered User.

New member

Registered User.

New member

Registered User.

New member

Registered User.

Similar threads

Users who are viewing this thread