MS Access 2010 - Linked files?

[jonathanchye]

Hi all,

A Client wants to attach PDF files to our current Access DB. I know the most straight forward way to do it in MS Access 2010 is to use the "Attachments" fields. However, I've read it is mostly not recommended by most people due to restrictions and potential problems in the future.

I've read about another method called "Linked files" but can't seem to find more information about this.

Can anyone comment about this? Basically the PDFs could be 1-2 MBs each or could add up to 30MB in size... (per record)

 

[ted.martin]

One way would be to create a table and form which lets call them 'Document Hyperlinks' and 'F-Document Hyperlinks'.

Enter into this table the path of the .pdf.

By using VBA code to 'browse' to the .pdf location, you can avoid having to type in the path. Have another text field as a descriptor for the pdf as a long path name won't be that useful.

I then use ALT + O to open the .pdf (file) the Document Hyperlink form.

Here is the browse code (you can put it in a module):

I have also provide the Folder Browse option and two version for the File Browse, one where the Document Hyperlinks form is on a Main form and then other where is is a Sub-Form.

Option Compare Database
Option Explicit

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal Hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Const SW_SHOWNORMAL = 1
Public Const SW_SHOWMAX = 3

Public Sub FolderBrowse()

'// requires Microsoft Office 14.0 Object Library \\

'   Dim fDialog As Office.FileDialog
'   Dim varFile As Variant
'
'   Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
'
'   With fDialog
'      .AllowMultiSelect = False
'      .Title = "Select the Folder"
'      .Filters.Clear
'      If .Show = True Then
'            [Save Directory].Locked = False
'            [Save Directory].Text = .SelectedItems(1) & "\"
'      Else
'         Exit Sub ' Cancel clicked
'      End If
'   End With
'
'    [Save Directory].Locked = True
   
End Sub
Public Sub FileBrowse(myForm As String, mySubform As String, myControl As String)   ' for Control on a Sub-Form
'Public Sub FileBrowse(myForm As String, myControl As String)   for Control on a Main Form

' This requires a reference to the Microsoft Office 12.0 Object Library.

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant

   ' Set up the File dialog box.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   
   With fDialog
      ' Allow the user to make multiple selections in the dialog box.
      .AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      .Title = "Select a File"

      ' Clear out the current filters, and then add your own.
      .Filters.Clear
      '.Filters.Add "Access Databases", "*.MDB"
      '.Filters.Add "Access Projects", "*.ADP"
      '.Filters.Add "HTML Document", "*.htm*"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the user picked at least one file.
      If .Show = True Then
            
        '.Application.Forms(myForm).Controls(myControl).Value = .SelectedItems(1)    'for Control on a Main Form
            
        .Application.Forms(myForm).Controls(mySubform).Controls(myControl).Value = .SelectedItems(1)   ' for Control on a Sub-Form
        'Forms(myForm).Controls(mySubform).Controls(myControl).Value = .SelectedItems(1)  ' also works
            
      Else
         Exit Sub ' Cancel clicked
      End If
   End With
   
End Sub

Here is the Code for the form F-Document Hyperlinks - I am here using the Subform version.

Option Compare Database
Option Explicit
Const SW_SHOWNORMAL = 1
Const SW_SHOWMAX = 3

Private Sub Hyperlink_DblClick(Cancel As Integer)

    Call FileBrowse(Me.Parent.Name, "SF-Document Hyperlinks", "Hyperlink")

End Sub

Private Sub Hyperlink_KeyPress(KeyAscii As Integer)

If KeyAscii = 111 Then ' Alt + O

    If Len(Hyperlink.Text & "") > 0 And Dir(Hyperlink.Text) <> "" Then
        ShellExecute Me.Hwnd, "open", Hyperlink.Text, vbNullString, Hyperlink.Text, SW_SHOWMAX
    Else
        MsgBox Hyperlink.Text & vbNewLine & vbNewLine & "Hyperlink is not at this Path" & vbNewLine & " ... Or file does not exist", vbExclamation, "Need to Reset"
        DoCmd.RunCommand acCmdDeleteRecord
    End If

End If
End Sub

Good luck.

 

[jonathanchye]

Wow! This is a very good solution

I've actually found my own solution with comprises of creating a one-to-many subtable which a field for Hyperlinks. You can format the subform in datasheet view to add On Click events. This works but its not as elegant.

Thanks for your solution though I will definitely have a look into this.

edit: Had a quick look through your code and I've noticed this only works for a single attachment per record? I could be wrong here tho....

 

[ted.martin]

No - its kinda unlimited as the subform you use is a continuous form. (see attached pic)

 

[Badenser]

Is there any possibility to save not the absolute form but the relative form of a file link this way?

I really like to select the hyperlink via file browser, but i need the relative link since the database is copied to other computers some times...

i really like your work an hope you can help me.

P.S.:I also tried to select the file an just delete the first part, but i get an error as soon as i press die backspace button.