Saving Attachments (vba and Attachement control)

[Sck]

I have an unbound entry form on a tool and now need to be able to add attachments to the entry. What I have tried is setting up a temp table to as the record source for the form so I can use the attachment control on the form. What I need to happen is once the new record is saved on the entry form, I want to move the attachment to the new record and clear the temp table in VBA. Because you cant include a multivalued field in a update query, I am at a loss on how to proceed.



Just hit me that I might be able to just change the temp table to a table just for attachments with a link to the main table. I will have to play with this to see if it will work. But I would appreciate any assistance on the VBA coding or thoughts on the pros/cons of a linked table. Other forms will be looking at the data and will need to access the attachments so I'm not sure how well the linked table will work based on issues I have had working with attachments in queries.

Access 2010

[rpeare]

what do you mean by 'attachment' an actual file? You shouldn't be storing an actual image in your database (if it's going to remain local to you, and even if it's not it would be better to copy all the image files to the host location) because it causes unnecessary bloat in your db. Instead store the path to the file you want related to the record and use something like .followhyperlink to open the file whenever necessary.

Secondly

... you cant include a multivalued field ...

I am going to save you a lot of pain, aggravation and sorrow when I say this... DO NOT USE multi value or calculated fields in your database, they are tremendously hard to manipulate if you are not an experienced programmer. Review your database structure and opt instead for a properly normalized structure.

[Sck]

the Attachment data type is a multi valued field. my structure is normalized.

I am required to allow attachments in the tool as backup documentation for the data. I would prefer to store them outside of the BE database but I have no control over where the tool is stored or when it is moved by several different users. For this reason, I have not tried to use .followhyperlink. I guess if there was way to programmatically update the hyperlink to a subfolder in the current directory of the BE it would be feasible but I am not sure how to implement that. I have explained the bloat issue to my bosses and they are willing to accept it as a downside to having attachments.

[rpeare]

I've handled this a couple of different ways but probably, want something like this:

Any time someone 'attaches' a document copy that file to a 'central' location, store the path to the original location and the 'permanent' location. Then the user can link to the original file (so that if it's modified it will still be current) and also have a 'backup' copy, so that if the file is deleted, renamed, or the directory structure changes a permanent copy is in the folder where the databases is so you can always use application.path to get to the 'permanent' copies. You can enhance this with a periodic copying of all files to your permanent location to make sure they're updated and test your links and create a list of files with outdated (non functional) links.

All of this functionality is available through filesystemobject commands and you can still use the .followhyperlink method.

You just have to use the filesystemobject .fileexists function to check to see if it's valid, if it's not pop up a message and direct them to the 'permanent' file instead.

[Sck]

That gives me an idea that I'm going to take back to the boss. thanks