Attachments

[MvP14]

Hi,

I want to add attachments. I want advice before getting started.

I figure I could do one of two things:
- use attachments in a table, storing the attachment in the db;
- leave the attachments outside the db and hyperlink to it.

The first solution is easy, but I prefer the second one, since it limits the size of the db.

However, the second one is hard for me to put into practice, given my limited vba knowledge.

What I can do is use a form to open a dialog selecting a file and add a hyperlink to that file.

But here's what I want to be able to do: not simply link to the file, but first copy the file to a different directory and then hyperlink to the new location.

I've looked around, but I'm not sure where to start.

Should I go for the first solution, or can someone lead the way on going with the second one? Thank you in advance.

 

[pr2-eugin]

Hi, my advice would be not to store attachments inside the tables, Hyperlinks are better.. However they have some disadvantages as well.. I was in the same dilemma, I had some advice from Pat Hartman who advised me as such..

I don't use the hyperlink data type for 2 reasons:
1. It can't be upsized.
2. It requires you to store the complete path which makes moving stuff harder if you have a server change.

One database I manage that has a lot of associated files is used for conducting employee benefit audits. Each audit has its own folder. The name and path are stored in the Audit record. Each type of file has a different file folder beneith that. So, letter templates are stored in one folder, exports in another, inports in another, etc. The documents themselves are usually associated with an individual employee so there is a table that holds the file name and a received date and reviewed date. To open a file, I concatenate the path from the audit table with the document type (for the subfolder) and name from the scanned documents table and use the FollowHyperlink method to open the file. This alows the code to open files of any type as long as the file type is registered. So .pdf's are opened by the pdf reader. spreadsheets are opened by Excel, word docs are opened by Word, etc. All very neat and simple and when the users move stuff around, they change the folder name in the audit record and we don't have to run bulk update queries to fix the individual links.

But I used Hyperlinks.. Not as sophisticated as her system, but a simple one.. where the user can open/add hyperlinks.. I am sorry I do not have the exact answer you were looking for, but something I thought might shed some light on your question..

 

[MvP14]

Thank you for your reply. It convinced me I should not go for internal attachments. The Pat Hartman reply made clear I should also avoid hyperlinks.

Thus, I've been looking around a bit, and I think I'll go for a third solution, building on the second one: I copy the selected file to a subfolder of the BE location and in doing so I rename the file, giving it a name related to some records fields (ID (unique) and other field). If an attachment is stored, I just put the name of the file in a text field.

If the text field is null, there is no related file, it the textfield contains a filename, I can rebuild the location creating a string using the subfolder BE location, and the filename stored in the table. Using that string I call the relevant application to open the file.

I'm pretty close to getting this to work. If I get it to work, I'll post the code.

 

[Simon_MT]

To me the trick with images is that the image naming convention correlates to a record. If you save the image by adding a record the corresponding image automatically appears. The Image file is handled by the underlying query and the Path set by VBA.

This way you can recognise the image as it relates to an specific ID.

Simon

 

[gemma-the-husky]

when you say, store the hyperlink - all you need to do is store the path. is that what you mean?

then

application.followhyperlink filepath

will open the target with whatever application is registered.