CODEWITHSUNDEEP
ms-accessvba
user126284
user126284

Reputation:

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

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?

Upvotes: 1

Views: 32999

Answers (3)

user12263349
user12263349

Reputation: 1

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

Upvotes: 0

Alex
Alex

Reputation: 3405

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

Upvotes: 2

Fubzot
Fubzot

Reputation: 177

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

Upvotes: 0

Related Questions