VBA Excel和Access-在Access中为特定记录添加附件

Question

我有一个Excel文件，可以向Access读取和写入数据。我希望能够为某些记录添加附件。到目前为止，我已经成功地编写了代码，为所需表中的新记录添加了一个附件。但是，我无法将附件添加到某个记录中。以下是Excel VBA中的代码：

Public adodbConnectionString As String
Public adodbConnection As ADODB.Connection
Public adodbRecordset As ADODB.Recordset
Public daoDB As DAO.database
Public daoWS As DAO.Workspace
Public daoRecordset As DAO.Recordset
Public daoRecordset2 As DAO.Recordset
Public daoFields2 As DAO.Field2
Public Function refreshPath() 'this function updates the path to the .accdb-file globally
pathDb = "[...]Data.accdb"
End Function
Sub exportAttachmentToAccess() ' this function adds the attachment to the table
refreshPath
Dim filePath As String
filePath = SelectFile()
If Len(filePath) = 0 Then
Debug.Assert "No file selected!"
Exit Sub
End If
Set daoWS = DBEngine.Workspaces(0)
Set daoDB = OpenDatabase(pathDb)
Set daoRecordset = daoDB.OpenRecordset("SELECT * FROM N_C_A;", dbOpenDynaset)
daoRecordset.AddNew
Set daoRecordset2 = daoRecordset.Fields("Test1").value 'Test1 is the field name where the attachments are stored

daoRecordset2.AddNew
daoRecordset2.Fields("FileData").LoadFromFile filePath
daoRecordset2.Update
daoRecordset.Update
daoRecordset.Close
Set daoRecordset = Nothing
Set daoDB = Nothing
End Sub

这是访问表：

正如您所看到的，每次我运行宏时，附件都会出现在一个新的记录中。然而，当我改变daoRecordset.AddNew到daoRecordset.Edit它将附件添加到第一条记录中。

如何将附件添加到ID 12，即第四条记录？

Answer 1

在表中保存对象会消耗Access 2GB大小限制。通常最好将附件保留在外部，并将路径保存在文本字段中。

选项：

1. 在SQL中应用筛选器以仅使用应更新的记录打开daoRecordset

daoDB.OpenRecordset("SELECT * FROM N_C_A WHERE ID =" & Me!ID, dbOpenDynaset)

2. 使用记录集FindFirst方法转到所需记录

daoRecordset.FindFirst "ID = " & Me!ID
If Not daoRecordset.NoMatch Then
'code to add attachment
End If