Save Attachments in Folder Not Database

[Cat_129]

Hey,

I have been trying to work this out but think what I am looking at as examples are doing more than I need and I am getting brain melt!

I have been using these as help documents (https://www.access-diva.com/d15.html & https://www.access-programmers.co.u...o-save-attachments-in-specific-folder.282135/)

We have a database with forms, the forms have attachments, database is now getting to limit, bad database designer (me)...

What I would like is this.... User is on form, clicks button 'Add Attachment", popup appears asks them where it is, it then copies that file into a central directory (same file name, no folder structure, just a dumping ground), and puts a hyperlink into the form on the database. I want to give the user the option to add more than one hyperlink.

Please help, my brain is broken

 

[arnelgp]

firstly, to be able to add more hyperlink, you need another table that will save the location/filename of the attachments:

tblAttachments:

pkID (long, foreign key)
Description (short text, description of attachment)
Attachment (short text, path + filename of attachment)


you need the code from the first Link you have (FileDialog) to be able to locate the associated file.
you can use FileCopy (to copy the file to central directory).
you can then be able to save the filename (+ path) of the attachment.
use FollowHyperlink to view the file.

 

[Cat_129]

firstly, to be able to add more hyperlink, you need another table that will save the location/filename of the attachments:

Sorry if I am being stupid buy why would it need to be in another table? I was going to add a field to the existing table that the link could go into

 

[isladogs]

@Cat_129
If you will only ever have one attached file for each record, the file path can be stored in the same table.
However if records could have multiple attachments then you need a separate table with a one to many relationship to the main table

 

[Cat_129]

@isladogs

Thanks, this was my understanding, one field per hyperlink, we want to limit people to a maximum of 5 hyperlinks per form so I was just going to add 5 fields.

I know it's not pretty but only have a couple of days to get this done in, and the other way is beyond my skills at the moment.

Thanks for the reply

 

[isladogs]

@isladogs

Thanks, this was my understanding, one field per hyperlink, we want to limit people to a maximum of 5 hyperlinks per form so I was just going to add 5 fields.

I know it's not pretty but only have a couple of days to get this done in, and the other way is beyond my skills at the moment.

Thanks for the reply

I strongly advise against doing that. You are just making lots of extra unnecessary work for yourself.
Having a 'non normalised structure' like that will make it far harder to design queries etc
Also at some point there will be a good reason for adding a 6th 'attached' file and your table will need modifying along with associated queries, forms and reports.

Instead use a separate table as arnelgp suggested. It will be easy and quick to implement with far less work required.

 

[theDBguy]

Hi. I agree with using a separate table. But, one other issue that you may have not considered yet is by copying the selected file into a single folder, you could have a conflict when two different files have the same name. The second file could overwrite the first.

 

[Cat_129]

Hi. I agree with using a separate table. But, one other issue that you may have not considered yet is by copying the selected file into a single folder, you could have a conflict when two different files have the same name. The second file could overwrite the first.

The files that are being attached are from a controlled drawing system, so if two files with the same name are added it wont make a difference because they will be the same file. If they are different something has gone wrong in a different system, but yes you are correct this would happen if two of the same name are added.

Ok, will look at doing it in a separate table, im still trying to figure out the code to do the first bit, but once I have got that sorted will come back and hopefully some lovely person can help me again