Using file path links instead of document attachments

[Shem K]

Hi guys.

I'm referring to the insight by Rabbit on this link: http://bytes.com/topic/data-manageme...external-media
and by jforbes on this link: http://bytes.com/topic/access/answer...i-attachements

I made a dummy database with attachments and realised attachments could take up too much space, as compared to the actual data. This inevitably would use up the 2GB provided by Access in a short time on my back-end. I need to know how to use a file path link that opens to the document in a specific location, and avoid at all costs using attachments in the database.

Secondly, in an instance where a record has multiple attachments, how can I be able to use multiple file path links on the same record instead of multiple file attachments (as is the case with Access 2007)?

Thank you.

Kind regards,

SK

[TheSmileyCoder]

First off, note that there is a 1 major difference between a file link and an attachment. When you have an attachment, you are making a copy of the file and storing it in the database.
With a file link, you run the risk of someone outside of your control modifying/editing/moving/deleting the file.


In my application, what I do, after the user has selected the file is to copy the file to a location on the server, named by my app. I also create a record in my table, and the file is then named by the primary key. So the file path could look like:
\\MyNetworkDrive\MyAppName\Filestorage\737.StoredF ile

Now this sort of naming convention has 1 benefit. People will not be able to randomly search out the file and mess with it. Furthermore its also hidden.

Now when the user needs to restore the file, the app will copy the file from that location, into C:\MyAppName\WorkingFolder\ and then open it.

To have multiple files, the solution is to use a 1-many relationship between your record and the filetable.

[zmbd]

I do basically the same as TheSmileyCoder; however, I don't rename.... something I'll be doing in the future!

[jforbes]

I like the option of obfuscating the filename in TheSmileyCoders scenario. For attachments that should be indelible, this is a great idea.

In our environment we have two types of Attachments. Live Documents and Reference Documents. The Live Documents are to be edited as part of the daily workflow, where the Reference Documents are to be viewed only. (I might have to rework the Reference Documents to be stored off the way TheSmilyCoder does...) The two types of documents enter the system differently. The Live Documents get the FilePath to the original file saved off, where the Reference Documents are first copied to an Attachements Folder on the Server, while stripping out SQL reserved characters and then the FilePath is saved into the database. Then going forward, they are launched the same.

To launch the Attachment... to edit or view gets complicated by what programs are used to edit and by our network topology which crosses a few hundred miles, but the basics to launch an attachment are in the mocked up code below:

Function OpenAttachment(ByVal sTargetAndLocation As String) As Boolean
 
    Dim dTaskID As Double    
    OpenAttachment = False
 
    If InStr(sTargetAndLocation, "http") > 0 Then
        ' Open hyperlink
        FollowHyperlink (sTargetAndLocation)
        OpenAttachment = True
    Else
        ' Open with Shell
        If fileExists(sTargetAndLocation) Then dTaskID = Shell("explorer.exe "
& sTargetAndLocation, vbNormalFocus)
        OpenAttachment = (dTaskID >= 0)
    End If
 
End Function
 
Function fileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long
 
    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
 
    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If
 
    'If Dir() returns something, the file exists.
    On Error Resume Next
    fileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

There are a couple schools of thought on how to open a URL. One is to send it to the Windows Shell, the other is to use FollowHyperlink. They both have their merits. As you can see, I open anything with "HTTP" in it with FollowHyperlink and everything else gets opened through a Shell. This seems to work best for us, you might want to experiment. While your experimenting, you might want to look at http://allenbrowne.com/func-GoHyperlink.html.