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.