Access Diva Handling Attachments

Handling Attachments...

Sooner or later you are going to want (or need) to add *attachments* to your database. While the Attachment Data Type may seem tempting it's not a good idea if you plan to have a lot of files (documents) and images. Access has a 2 gig limit and you will find your file quickly grow should you start adding images and files. And let's not forget the fact that you cannot upsize the Attachment Data Type to SQL Server. Instead, best practice is to *link* (file path of document or image) in the database to the file stored in a location available to all Users.

I set up a separate to handle links (tblLinks), as it's easier to update should something change...

	Field	Data Type
	lLinkID	Autonumber (PK)
	lRecordID	Number, Long Integer
	lPath	Text, 255
	lDescription	Text, 100
	lLinkTypeID	Number, Long Integer
	lAreaID	Number, Long Integer
	lDelete	Yes\No (set Default* to False)*
	lTimestamp	Date\Time

Optional

And then your Form (or Forms) depending on how many menus you want your Users to be able to link files from, i.e.

Single Form

Continuous Form

Now we can start to add the code behind those buttons. (Note, for the Navigation Buttons click here.

Private Sub cmdLinks_Click()
 
    Dim fDialog    As Object
    Dim varFile As Variant
    Dim strPath As String
 
   ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(1)
 
    With fDialog
      'Set the title of the dialog box. '
        .Title = "Select the File..."
        .InitialFileName = "C:\"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
        If .Show = True Then
                varFile = .SelectedItems.Item(1)
                Me.txtPath = varFile
                Me.txtDescription = GetFilenameFromPath(Me.txtPath)
                Me.txtTimestamp = FileDateTime(Me.txtPath)
        Else
                MsgBox "You clicked Cancel in the file dialog box.", vbOKOnly, "Select a File"
        End If
   End With
 
   Call CopyFile(Me.txtPath, "C:\adBEs\links\" & GetFilenameFromPath(Me.txtPath))
 
End Sub

Private Sub cmdOpenFile_Click()
 
    Dim strFile As String
 
    strFile = Nz(Me.txtPath, "")
 
    If IsNull(Me.txtPath) Or Me.txtPath = "" Then
        'If the File Path is empty
        MsgBox "No Link.", vbInformation + vbOKOnly, "Link"
    Exit Sub
    End If
 
    If Dir(strFile) = "" And Not IsNull(Me.txtPath) Then
        MsgBox "No Link.", vbInformation + vbOKOnly, "Link"
    Else
        Call GoHyperlink(Me.txtPath)
    End If
 
End Sub

When creating the Form(s) don't forget to add the hidden field(s) indicated in the Figure to the right. Note, you can leave off the optional fields.

cmdLink

cmdOpen

cmdRemoveLink

cmdAddNew

This button does double duty, it can either replace an existing file or you can go to new and select a file. You might be thinking why not just limit it to selecting a new file? Well, sometimes the file changes and you really don't want to add a new one you just want to update the existing link. For example, the file was .DOC but it's been upgraded and now it a .DOCX. You don't want to leave the .DOC so you just Browse to the updated document and select it.

Notice the CopyFile line where the code tags are? This line copies the file to the indicated folder. While this line is optional it really is a good practice to move the files to a central location where they won't accidently get deleted. If you do decide to do this then directly after the CopyFile line insert...

Me.txtPath = "C:\adBEs\links\" & GetFilenameFromPath(Me.txtPath)

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
 
    Dim intResp As Integer
 
        If Me.NewRecord And Me.Dirty = False Then
            MsgBox "This is a new record and has no Link.", vbInformation, "Link"
        Else
 
    intResp = MsgBox("You are about to DELETE this Link, are you sure?", vbYesNo + vbExclamation, "Delete")
 
            If intResp = vbYes Then
                Me.chkDelete = True
                Me.Requery
            Else
                DoCmd.CancelEvent
            End If
        End If
 
Exit_cmdDelete_Click:
    Exit Sub
 
Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
 
End Sub

Private Sub cmdAddNew_Click()
On Error GoTo SmartFormError
 
    Dim pubProjectID As Long
 
    pubProjectID = Me.txtProjectID
 
        DoCmd.RunCommand acCmdRecordsGoToNew
        Me.txtProjectID = pubProjectID
 
Exit_SmartFormError:
    Exit Sub
 
SmartFormError:
 
    If Err = 2046 Or Err = 2501 Then
        Resume Next
    Else
        MsgBox Err.Description
        Resume Exit_SmartFormError
    End If
 
End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
 
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

GetFilenameFromPath()

You will notice I use GoHyperlink() by Allen Browne (former Microsoft^® Access MVP) instead of Application.FollowHyperlink. Click the code tags above and read the Why a replacement? section to see why. However, you CAN use the built-in Application.FollowHyperlink if you prefer.