使用 MS Access VBA 添加/查看附件

Add/view attachments using MS Access VBA

提问by Seth E

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 Access 接口，因此人们无法删除或破坏内容，但是，每当我尝试调用这些函数中的任何一个时，我都会收到 Argument not Optional 编译器错误。

http://blogs.office.com/b/microsoft-access/archive/2007/08/24/adding-removing-and-saving-files-from-the-new-attachment-field-in-access-2007.aspx

http://blogs.office.com/b/microsoft-access/archive/2007/08/24/adding-removing-and-saving-files-from-the-new-attachment-field-in-access-2007。 aspx

in the onclick event of a button i have

在我有一个按钮的 onclick 事件中

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

回答by Renaud Bompuis

Your first problem comes from the fact that you didn't carefully read the code in the link you mention.
The AddAttachmentsubroutine is defined as:

您的第一个问题来自您没有仔细阅读您提到的链接中的代码。
该AddAttachment子程序被定义为：

AddAttachment(ByRef rstCurrent As DAO.Recordset, _
              ByVal strFieldName As String, _
              ByVal strFilePath As String)

This means that it has 3 mandatory parameters:

这意味着它有 3 个强制参数：

• rstCurrentan open recordset for the table where you want to store your file. The file will be added to the recordset current record.

• strFiledNamethe name of the attachment field where the file will be saved. Your tblAttachtable 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).

• strFilePaththe absolute path to where the file to be attached is located.

• rstCurrent要存储文件的表的打开记录集。该文件将被添加到记录集当前记录中。

• strFiledName将保存文件的附件字段的名称。您tblAttach在 Access 中创建的表必须至少有一个附件字段（可能还有其他字段以及与附件相关的信息，以便您可以找到它，例如文档名称和 ID，可能是文档的原始路径等）。

• strFilePath要附加的文件所在位置的绝对路径。

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.

调用这个函数SelectFile()让用户选择一个文件。如果操作被取消或没有选择文件，该函数将返回文件的完整路径或空字符串。

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.

SelectSaveAs("toto.xls")例如，呼叫建议附件的名称，并让用户选择将其保存的位置（他们也可以更改名称）。该函数将返回保存附件的文件的完整路径。

Now, you can put everything together.

现在，您可以将所有内容放在一起。

Say you have created a tblAttachthat has a Filesfield in it.
We can rewrite the test in the link you mention as such:

假设您创建了一个tblAttach其中包含一个Files字段的字段。
我们可以在您提到的链接中重写测试，如下所示：

    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 SaveAttachmentsubroutine.

要让用户保存文件，您将执行类似的操作：打开记录集，移动到包含您要保存的文件的记录，询问用户文件名，然后将所有这些信息传递给SaveAttachment子例程。

回答by G_P

'----- code to browse, select file and attach to access table
'----- Thanks a lot for earlier submissions, I have just put together all related codes
'----- the code is to add attachments to the attachment field in Ms Access Table. If you don't want to bring up built in form, you can use this code to browse the file and attach
'------ please add this code as Module in Ms Access.  
'------ being a public function, you call this code from any form just by adding 4 parameters  for example  Table name is EmpMaster, Attachment field name is Empcertificate, name of the ID field is EmpID, record ID number say 101
'----- Add_Attachment "EmpMaster", "Empcertificate", "EmpID", 101
'---- it works for me

Option Compare Database
Public Function Add_Attachment(strTableName, 
strAttachField, strIDfield As String, i As Long)

'------------ code to browse file and select file to attach
    Dim fd As FileDialog
    Dim oFD As Variant
    Dim strFileName As String

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Title = "Choose File"
        .InitialView = msoFileDialogViewDetails
        .Show

        For Each oFD In .SelectedItems
            strFileName = oFD
        Next oFD
        On Error GoTo 0
    End With

    Set fd = Nothing

'------------ from here code for file attachment process

    Dim cdb As DAO.Database, rstMain As DAO.Recordset, rstAttach As DAO.Recordset2, _
        fldAttach As DAO.Field2
    Set cdb = CurrentDb
    Set rstMain = cdb.OpenRecordset("SELECT " & strAttachField & " FROM " & strTableName & " where " & strIDfield & "= " & i, dbOpenDynaset)

    rstMain.Edit
   Set rstAttach = rstMain(strAttachField).Value
    rstAttach.AddNew

    Set fldAttach = rstAttach.Fields("FileData")

    fldAttach.LoadFromFile strFileName
    rstAttach.Update
    rstAttach.Close
    Set rstAttach = Nothing
    rstMain.Update
    rstMain.MoveNext
rstMain.Close
Set rstMain = Nothing
Set cdb = Nothing
End Function