Copy attachment fields to another table

[Lhoj]

Hi everyone. First off, apologies if that's been asked and solved before, which it probably has. I've searched the forums a bit and couldn't find a fitting thread.

I had a 'big' table with around 50 fields and I'm in the process of 'modularizing' it. I have created two new tables that will contain records with related fields from the original table. The problem is that two of those fields are attachment fields with consent pdfs and other related data. Obviously Access doesn't allow the inclusion of this type of multivalue fields in an Update query. So I though about moving them manually, since it's just about 150 records on the original table (hell of a work, considering it's 2-4 attachments per record, but still). Thing is it's constantly giving me 'file is not secure' or 'file is too big' errors with files that are already attached to the original table which makes no sense (specially since if I copy the file it will let me add it despite being the same size).

So, is there any easy and painless way of doing it? My first option would be doing it programmatically, but that would require saving the attachments somewhere and reattaching them to the new table, then clean all the old attachements which are in very specific net shares' locations. Is there any way I can somehow access the path of the original attachments from the attachment field and set the same path in the attachment field of the new table (or at least use it to save the files I recover from the attachment field in the corresponding folder)?

Second option would be including a path and not an attachment, but I'd prefer the files to be in the DB since it's a pretty small DB (currently under 100MB) and I'd rather have the files on the db than just a simple path.

Thanks!

[ArviLaanemets]

Second option would be including a path and not an attachment, but I'd prefer the files to be in the DB since it's a pretty small DB (currently under 100MB) and I'd rather have the files on the db than just a simple path.

Having attachment field in DB does mean, you never can be sure your DB will remain small! (Really, 2 GB is a small size for a BD, of for a table in most of DB's, and this is max limit for whole Access database!). In case your old DB did slow down (what is probably the reason you started this redesign at all), then having your data in several tables instead one doesn't help in any way. Probably contrary instead, as additional bites will be spent for table definition, indexing, etc.)

[Lhoj]

what is probably the reason you started this redesign at all

Oh, not at all. It just happens there where a number of related fields (i.e fields related to a phone line including number, ICC... ) that where just fields of this bigger table that kept growing bigger in fields. That way I can keep it more organized and easily change all fields of said, for example, phone line by just selecting another record from the new table.

Regarding the attachments, I wouldn't consider doing it that way if I expected it to grow out of control, but it's a tiny amount of small files (that I intend of periodically removing after some conditions are met) and I rather keep them in the DB for easier access and for safekeeping just in case they get removed or edited on the net share.

[Micron]

I wouldn't have attachment field in Access even if it meant having network backups for attachments (not anything I ever had to fear as people were responsible). Perhaps for your case you can copy the tables as many times as required then delete the fields you don't need.

[Lhoj]

Perhaps for your case you can copy the tables as many times as required then delete the fields you don't need.

Just had the same idea, so simple yet so convenient. That will save me a ton of time!

[June7]

If you want to understand how to programmatically manipulate Attachment data type, review:

https://superuser.com/questions/1565...ther-using-vba

https://learn.microsoft.com/en-us/of...chments-in-dao

[Lhoj]

Hi, June. Thanks for the links. The Microsoft one I'd already checked, in fact, that's what I do with the some critical data I move to an auxiliar table (including a couple of attachements). Second one looks way more interesting to me, assuming you can transfer an attachment from recordset to recordset with just those three properties. Now I'm tempted to try it when I have some time. Then again I ended going for the table copy since it looked faster and easier, but thanks anyway!