How to auto attach images in Access 2010 using vba/macros?

Question

I have a table where there is a "Photo" text field with the name of the file. I also have the actual files in a separate folder. I want to attach those files to the database, and not keep them in a separate folder. So I've created a separate "Picture" attachment field. But I don't know how I can attach those files to this field automatically. Can you give me some directions?

Why do you think you need to store the binary data in the database? This is usually problematic. OLE fields are thorny enough, but the new Attachment type has issues with multiple users (locks are not enforced and users can easily step on each others' edits). Why not just store the path to the pictures and the filename and then display that in an unbound OLE frame? I've implemented that plenty of times. — David-W-Fenton, Jan 25 '11 at 04:37
that's actually a good idea. But I thought there must be a reason for introducing this new attachment type to access. Also, I don't have to think about uploading files manually, access will do it automatically with the attachment file type. — , Jan 25 '11 at 14:07
There is definitely a reason they introduced the Attachment field type, and it's for compatibility with Sharepoint, just as the introduction of multi-valued fields was (and the Attachment field is a particular type of MV field, actually). It's not something that I think is of much use, as it puts too many things behind the scenes. All the MV fields are implemented behind the scenes with a proper N:N join table, but that structure is not easily accessed or manipulated. You should do it yourself if you need N:N. — David-W-Fenton, Jan 27 '11 at 00:15

score 2 · Accepted Answer · edited Jul 26 '19 at 19:07

Attachments are quite different from OLE objects. The first should be compacted and are managed without OLE servers installed on machine. For example, when you add a OLE object to a MS-Access field, this object is transformed in a kind of bitmap, which ought to be very large. In attachment fields, several file formats are automatically compacted on database. Also, you are able to import more than just only one file. In this case, Access does, behind the scenes, relational database model for improving efficiency.

You should load and save file formats in attachment fields as follows:

'  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

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

   '… Code to move to desired employee

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

   '  Loop through the attachments.
   While Not rsPictures.EOF

      '  Save current attachment to disk in the "My Documents" folder.
      rsPictures.Fields("FileData").SaveToFile _
                  "C:\Documents and Settings\Username\My Documents"
      rsPictures.MoveNext
   Wend

for more information, visit http://msdn.microsoft.com/pt-br/library/bb258184%28v=office.12%29.aspx

I wonder what `'… Code to move to desired employee` would look like. — StockB, Nov 26 '13 at 15:52

score 0 · Answer 2 · answered Oct 23 '19 at 13:12

On Error GoTo Err_AddImage Dim db As DAO.Database Dim rsParent As DAO.Recordset2 Dim rsChild As DAO.Recordset2 Set db = CurrentDb Set rsParent = Me.Recordset rsParent.Edit Set rsChild = rsParent.Fields("AttachmentTest").Value rsChild.AddNew rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg") rsChild.Update rsParent.Update Exit_AddImage: Set rsChild = Nothing Set rsParent = Nothing Exit Sub Err_AddImage: If Err = 3820 Then MsgBox ("File already part of the multi-valued field!") Resume Next Else MsgBox "Some Other Error occured!", Err.Number, Err.Description Resume Exit_AddImage End If

score 0 · Answer 3 · answered Nov 22 '11 at 13:07

This helped me:

Originaly posted by HiTechCoach on http://www.access-programmers.co.uk/forums/showthread.php?t=169056

On Error GoTo Err_AddImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.Edit

Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg")

rsChild.Update
rsParent.Update

Exit_AddImage:

Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_AddImage:

If Err = 3820 Then
MsgBox ("File already part of the multi-valued field!")
Resume Next

Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_AddImage

End If

How to auto attach images in Access 2010 using vba/macros?

3 Answers3

Linked