Add attachment via VBA in MS Access

Question

One of my form is as follows

enter image description here

It is a form made in MS Access which allows my users to add observations. An observation may include attachments as shown. I am able to click on attachment control and Add attachments to the popup that pops up. However, what is expected is that when I click ADD button as shown in the form above, this attachments shall be added to the corresponding field of a table.

All controls on this form are unbound.

The code written behind ADD button is as follows:

Private Sub cmdAdd_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblObservation", dbOpenDynaset)

    rs.AddNew

    rs![Artifact] = artifactId
    rs![Observation Text] = txtObservationText.Value

    'rs![Attachments] = ' not able to solve this

    rs.Update
    rs.Close

    Set rs = Nothing
    Set db = Nothing
End Sub

Have you considered adding the path rather than the attachment itself? — html_programmer, Jan 23 '13 at 11:47
You either need to add name and move all files to one centralized folder for observations, so given any time based on the name the file can be picked up from that folder else provide the full path string ( have enough length allowed) :-) — bonCodigo, Jan 23 '13 at 11:51

score 1 · Answer 1 · answered May 30 '19 at 15:45

You can use the following reference from Microsoft

The relevant piece of code is this:

'  Instantiate the parent recordset.  
Set rsEmployees = db.OpenRecordset("Employees") 

'… Code to move to desired employee 

' Activate edit mode. 
rsEmployees.Edit 

' Instantiate the child recordset. 
Set rsPictures = rsEmployees.Fields("Pictures").Value  

' Add a new attachment. 
rsPictures.AddNew 
rsPictures.Fields("FileData").LoadFromFile "EmpPhoto39392.jpg" 
rsPictures.Update 

' Update the parent record 
rsEmployees.Update

zokaee hamid · Answer 2 · 2021-02-19T09:58:46.933

First, bounded the attachment control to the attachment field of the desired table

   Private Sub cmdAdd_Click()
           'Me.Dirty = False  'fix Error 3188 is Could not update...
            Dim db As DAO.Database
            Dim rs As DAO.Recordset
            Dim rsFiles As DAO.RecordSet2
        
            Set db = CurrentDb
            'Create a DAO Recordset from a table in the current database
            Set rs = db.OpenRecordset("tblObservation")
                           
            rs.AddNew
            rs![Artifact] = artifactId
            rs![Observation Text] = txtObservationText.Value        
    
            Set rsFiles = rs.Fields("Attachments").Value 
            'This is a piece of code to get multiple files
            For i = 0 To AttachmentControl.AttachmentCount-1
                rsFiles.AddNew
                'Attachment control is used as an uploader file 
                rsFiles.Fields("FileData").LoadFromFile  AttachmentControl.FileName(i)
            Next i
        
            rsFiles.Update 
            rsFiles.Close
        
            rs.Update
            rs.Close
        
            Set rs = Nothing
            Set db = Nothing
        End Sub

Add attachment via VBA in MS Access

2 Answers2

Linked