How to create attachments in 2003?

[munkee]

I have made a database which is used to record information where users are starting to ask to be able to attach "evidence" to records.

This evidence could be a pdf document, word document, image etc

Now I have researched a little and found most sites tell you to use an OLEObject to do this. However the file size is really hit hard by doing this. So here is my solution...

Users click attach on a form and use the windows open file dialog to find the path. They then click open.However instead of opening the path two things occur:

The file is copied to a central folder on the server (where my back end is kept). This then updates a table which contains a list of all files within the folder. This list of files just contains a unique reference number, the file extension and also the path to the file.

My form then shows the attachment as a hyperlink within a subform (can have mutliple records attached). The subform is ofcourse linked to the main record via the parent ID number. This for instance could be displayed within a datasheet view with the hyperlinks beside the unique ID and then the file type in a 3rd column.Users can click the hyperlink to open the file which is the path to the copy within the back end file storage folder.

Any ideas if anything like this has been done before or whether this sounds feasable? The coding behind it should seem relatively OK using vba I expect.

Anyway, thoughts please.

[ADezii]

Your approach is definitely feasible as well as practical. Personally, I avoid the use of Hyperlinks. I'll usually display Descriptive Text in the Field such as: 3rd Quarter Sales for 2010. This Text will be formatted as Underline, Blue, and possibly Italic to set it off as well as to mimic the look of a Hyperlink. In the Dbl-Click() Event of the Control, I'll perform a DLookup() to retrieve the Absolute Path to the File, then execute it directly based solely on its Extension. Again, this is the simply the System that I like to use, and I am not saying that it is a better/worse than any other.

[munkee]

Thanks for the reply Adezii, I have begun creating the code now. Do you have any reason why you avoid the usage of hyperlinks?

Also have you seen similar set ups being used in applications for storing attachments as I described? Just wanting to make sure I am not covering anything which could be easily bypassed with a quicker method.

[ADezii]

There have been similar Threads before covering this very Topic, usually under 'Storing of Images in a Database' or related Topic. You can research these Threads to cover all bases.

P.S. - I do believe that I can save you a lot of time and effort. Here is an Application that I developed for a User awhile ago. It will Open the Office File Dialog, allow the User to select any File (*.*), then write that File to a Text Box whose Control Source is a Hyperlink Field, while displaying the Caption of the Hyperlink only. Be sure that the Reference to the Microsoft Office XX.X Object Library is made. Download the Attachment and let me know what you think.

[munkee]

Thanks for that ADezii, it has helped me with understanding a lot of the properties associated with the file dialog that I didnt know about.

I will be using the primary key of the parent to make a new folder if it does not exist and then transfer the file over.

The basic set up so far:

Sub copyfile()
Dim SourceFile, DestinationFile As String
Dim StrIDNum As Integer
Dim fso As Object
Dim Strfol As String

'Create generic error handler
On Error GoTo Ouch

'Open up a dialog for us to select a file to attach
'Find the file name of the file we selected 4th in our delimited array
    SourceFile = Split(OpenDialog, "\", , vbTextCompare)(4)

'Select the ID number to use to archive our attachments into
    StrIDNum = 400 'Replace this with a reference to ID number of parent record I.E. me.parent.nccid

'Create the destination folder path
    Strfol = "C:\Documents and Settings\mobbe00C\Desktop\nonconformancereports\" & StrIDNum & "\" ' change to match the folder path"

'Check if the destination folder exists, if not create it
Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(Strfol) Then
        fso.CreateFolder (Strfol)
    Else
    End If

'Create the destination file string, which will be equal to our folder and sourcefile name
    DestinationFile = "C:\Documents and Settings\mobbe00C\Desktop\nonconformancereports\" & StrIDNum & "\" & SourceFile    ' Define target file name.

'Copy the file from the source to the destination folder
    FileCopy SourceFile, DestinationFile

LeaveIt:
    Exit Sub

Ouch:
    MsgBox Err.Number & "-" & Err.Description
    Resume LeaveIt
End Sub

Function OpenDialog()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant

    With fd
    .AllowMultiSelect = False
    .ButtonName = "Attach file"
    .InitialFileName = vbNullString
    .InitialView = msoFileDialogViewDetails
    .Title = "Select a file to attach to the database."
    
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                OpenDialog = vrtSelectedItem
            Next vrtSelectedItem
        Else
        End If
    End With

    Set fd = Nothing

End Function

I just need to work on creating a table and appending a new record each time an item is added. I think I will also sort out a way of deleting the file if the attachment record is removed.

[ADezii]

Looks good munkee, If you wish to keep things simple, and remove the External Reference to the Scripting Runtime, you can. For me, I always try to use what is intrinsic, as long as it performs the task adequately.

If Dir$(Strfol, vbDirectory) = "" Then
  MkDir Strfol
End if

[munkee]

Learn something new everyday, I didnt even know there was a Dir$ function! I'll be using this more often. Thanks