How To Attach Files In An Access Database

If you noticed Access now has a datatype to add attachments. If you don’t want to program this with VBA as I show you how to do below, you can use it (if you dare :)).

I think Microsoft wanted to make things easier for the user, so they added the ability to add attachments as a field datatype.

Ok…if that’s what you want to do, but I would not recommend it. You really should store the file in the operating system and the file’s path in a text datatype field. In the case of Access, the datatype is “Short Text”. The database’s strength is to store data, and the operating system’s strength is to store files.

We don’t want to “bloat” the database with files, that’s what the OS is for.

The following example will simply show you how to attach files to a customer and then open the file paths you attached.

Attach File (s)

When you click the “Attach Files” button, you’ll get this code:

Private Sub btnAttachFiles_Click()
    
    'Requires reference to Microsoft Office 16.0 Object Library (change your reference to the library based on your microsoft office version).
    
    Dim fDialog As Office.FileDialog
    Dim strPath As String
    Dim varFile As Variant
    Dim strInsertSQL As String
    
    'Set up the File Dialog.
    
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    With fDialog
        .AllowMultiSelect = True
        .InitialFileName = CurrentProject.Path
        .Title = "Please select one or more files"
        .Show
        
        'loop through the selected files
        For Each varFile In .SelectedItems
            'insert the file paths into the table
            strInsertSQL = "INSERT INTO CustomerFiles (CustomerID,FilePath) VALUES ('" & Me.CustomerID & "','" & varFile & "')"
            CurrentDb.Execute strInsertSQL
        Next
    End With
    
    'show the results in the subform
    [frmCustomerFiles_Sub].Form.Requery
    
    Set fDialog = Nothing
    
End Sub

With the above code, you are able to select a file from your file system to assign to a customer.

Doing it this way you are selecting a file already in your file system.

So we choose the file, and the file’s path gets entered into the table.

To open the file and read it gets attached to the “Click” event of the “FilePath” textbox. This is the code:

Private Sub FilePath_Click()
    If Me.FilePath <> vbNullString Then
        
        Call ShellExecute(0, "Open", Me.FilePath, vbNullString, vbNullString, 1)
        
    End If
End Sub

This little bit of code actually calls a shell function (an api call) to “ShellExecute”, which is part of the windows operating system:

Declare PtrSafe 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

Make sure these shell api functions are in a module by themselves

The database is attached for you to check out. Let me know if you need help.

Download Attachments.accdb

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!