I'm trying to use these functions to manage an attachment table without using the in Access interface so people can't delete or break stuff, however, I'm getting Argument not Optional compiler errors whenever I try and call any of these functions.
in the onclick event of a button i have
Database.OpenRecordset tblAttach
Recordset.AddNew
Call AddAttachment
Recordset.Update
Another problem I'm having with this is that this code is only for importing from a direct path and I'd really need a file dialog method of file selection, but I'm not sure what to put beyond
Dim f As Object
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
'*** not sure how to get the path to f to insert it into the table
f.Show
Your first problem comes from the fact that you didn't carefully read the code in the link you mention.
The AddAttachment subroutine is defined as:
AddAttachment(ByRef rstCurrent As DAO.Recordset, _
ByVal strFieldName As String, _
ByVal strFilePath As String)
This means that it has 3 mandatory parameters:
rstCurrent an open recordset for the table where you want to store your file. The file will be added to the recordset current record.
strFieldNamethe name of the attachment field where the file will be saved. Your tblAttach table that you created in Access must have at least one Attachment field (and probably other fields as well for information related to the attachment so you can find it, like a document name, and ID, maybe the original path of the document, etc).
strFilePath the absolute path to where the file to be attached is located.
Your second problem is to let users select the file they want through a file dialog:
Public Function SelectFile() As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.Title = "Please select file to attach"
If .show = True Then
SelectFile = .SelectedItems(1)
Else
Exit Function
End If
End With
Set fd = Nothing
End Function
Call this function SelectFile() to let the user choose a file. The function will return the full path to the file or an empty string if the operation was cancelled or no file selected.
For letting the user select the name and location of the file when they want to save the attachment, the code is similar:
Public Function SelectSaveAs(initialName As String) As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)
With fd
.Title = "Save Attachment"
.InitialFileName = initialName
If .show = True Then
SelectSaveAs = .SelectedItems(1)
Else
Exit Function
End If
End With
End Function
Call SelectSaveAs("toto.xls") for instance to suggest a name for the attachment and let the user select where they will save it (and they can change the name as well). The function will return the full path to the file where you will save the attachment.
Now, you can put everything together.
Say you have created a tblAttach that has a Files field in it.
We can rewrite the test in the link you mention as such:
Dim dbs As DAO.database
Dim rst As DAO.RecordSet
' Ask the user for the file
Dim filepath As String
filepath = SelectFile()
' Check that the user selected something
If Len(filepath) = 0 Then
Debug.Assert "No file selected!"
Exit Sub
End If
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblAttach")
' Add a new row and an attachment
rst.AddNew
AddAttachment rst, "Files", filepath
rst.Update
' Close the recordset
rst.Close
Set rst = Nothing
Set dbs = Nothing
To let the user save back the file, you would do something similar: open the recordset, move to the record that contains the file you want to save, ask the user for the filename, then pass all this information to the SaveAttachment subroutine.