VBA Excel and Access - Add attachment to certain record in Access

Question

I have an Excel file that reads and writes data to Access. I want to be able to add attachments to certain records. So far, I have managed to write code that adds an attachment to a new record in the desired table. However, I can't manage to add the attachment to a certain record. Here is the code in 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

This is the Access table:

As you can see, the attachments end up on a new record every time i run the macro. However, when I change daoRecordset.AddNew to daoRecordset.Edit it adds the attachment to the first record.

How do I add the attachment to ID 12, i.e. the fourth record?

Answer 1

Saving objects in table consumes Access 2GB size limit. Often better to leave attachments external and save path in text field.

Options:

1. apply filter in SQL to open daoRecordset only with record that should be updated

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

2. use recordset FindFirst method to go to desired record

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