How to export attached jpeg files from Access to Word

[CapsFan]

I have working VBA that creates Word documents from Access data and can even insert jpeg's into the headers, but the jpegs are in a folder on the C: drive.

What is the VBA to insert the same jpegs that are now attachments in the Access database? The customer wants the jpegs in the database so it can move from machine to machine without reassigned the C: folder path for the jpegs.

Thanks,

[NeoPa]

I've heard of BLOBs (Binary Large OBjects) that can be handled in Access, but don't know much about them. It may be the direction to start looking in though.

[PhilOfWalton]

I Know Stephen Lebans certainly http://www.lebans.com/
has done works on Blobs, I can't remember which was the relevant title, but I have and old Mdb version of one of his programs.

Stephen sadly retired about 8 years ago, so I think most of his work was in Mdb format.

Phil

[CapsFan]

I'll check it out. Thanks for the replies NeoPa and PhilOfWalton.

[ADezii]

1. I am not sure how you could 'directly' insert Attachments consisting of *.jpgs into a Word Document. As the others have suggested, it would more than likely involve a Binary Transfer of the Images using some form of BLOB approach.
2. I do, however, have an indirect approach using the SaveToFile Method which will transfer the Attached *.jpgs as Binary Streams to a Folder of your choosing. Once they have been saved to Disk it should be a simple matter to Insert them into a Word Document then subsequently Delete them.
3. First, a couple of assumptions:
   1. I created a Table named tblTest that consists of only two Fields, namely: [PK] - {AutoNumber/Primary Key}, and [MyJPGs] - {Attachment/holds *.jpgs}.
   2. There are only two Records in this Table: Record #1 has a Primary Key of 1 with four *.jpgs Attached to the [MyJPGs] Field and Record #2 has a Primary Key of 2 with three *.jpgs Attached to the [MyJPGs] Field.
4. The following Code will extract the four *.jpgs in Record #1 to the CurrentProject.Path Folder. From this point, they can easily be inserted into a Word Document, either manually or programmatically.
5. Code Definition:
   

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset2
   Dim rsA As DAO.Recordset2
   Dim fld As DAO.Field2
   Dim strSQL As String
    
   strSQL = "SELECT * FROM tblTest WHERE [PK]=1"
    
   'Get the Database, Recordset, and Attachment Field
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
   Set fld = rst("MyJPGs")
    
   'Navigate through the Table
   Do While Not rst.EOF
     'Get the Recordset for the Attachments field
     Set rsA = fld.Value
       With rsA
         'Save all Attachments in the Field to Disk
         Do While Not rsA.EOF
           ![FileData].SaveToFile CurrentProject.Path & "\" & ![Filename]
             .MoveNext
         Loop
    
         .Close
        End With
    
       rst.MoveNext    'Next Record
   Loop
    
   rst.Close
   dbs.Close
    
   Set fld = Nothing
   Set rsA = Nothing
   Set rst = Nothing
   Set dbs = Nothing
    

6. Any questions, feel free to ask.

[CapsFan]

ADezii, I was considering something like this. Your code is cleaner than what I was working. :)

Thanks!

[ADezii]

1. I had some spare time, so I decided to write some follow-up Code should you decide to use the prior approach. I found the easiest method to Insert Graphics (*.jpg) once the *.jpgs are written to Disk is to have a Word Template with pre-defined Bookmarks. In my simple Demo:
   1. A Word Template was created as such:
      

      C:\Test\Test.docx

   2. Two *.jpgs exist in the C:\Test Folder, namely:
      

      JPG1.jpg and JPG2.jpg

   3. Two Bookmarks were also created and strategically placed - they are named Bookmark1 and Bookmark2.
2. The following Code will insert JPG1.jpg @ Bookmark1 and JPG2.jpg @ Bookmark2. Both Images were scaled to 25% both Horizontally and Vertically to ensure they fit on the Page.
   

   Dim wrdApp As New Word.Application
   Dim wrdDoc As New Word.Document
   Dim objWdRange As Word.Range
   Dim strJPG As String
   Dim strJPG2 As String
   Dim wrdPic As Word.InlineShape
    
   strJPG = "C:\Test\JPG1.jpg"
   strJPG2 = "C:\Test\JPG2.jpg"
    
   wrdApp.Visible = True
    
   Set wrdDoc = wrdApp.Documents.Open("C:\Test\Test.docx")
    
   With wrdDoc
     Set wrdPic = .Bookmarks("Bookmark1").Range.InlineShapes.AddPicture(Filename:=strJPG, _
                   LinkToFile:=False, SaveWithDocument:=True)
         wrdPic.ScaleHeight = 25
         wrdPic.ScaleWidth = 25
     Set wrdPic = .Bookmarks("Bookmark2").Range.InlineShapes.AddPicture(Filename:=strJPG2, _
                   LinkToFile:=False, SaveWithDocument:=True)
         wrdPic.ScaleHeight = 25
         wrdPic.ScaleWidth = 25
          .SaveAs "C:\Test\Test.docx"
   End With
    
   wrdDoc.Close
   Set wrdDoc = Nothing
    
   wrdApp.Quit
   Set wrdApp = Nothing

3. The Code is only meant to be a guide and easily can be improved upon and/or expanded.
4. It was meant to be an extension of saving the *.jpgs to Disk Code using SaveToFile. It has been tested and is fully operational.
5. Good Luck with your Project.

[CapsFan]

ADezii, thanks for taking the time to post this.

[ADezii]

You are quite welcome!

[NeoPa]

Nice one again Armund.

@CapsFan.
You won't go too far wrong once Armund has got involved. An expert of long standing.

[ADezii]

@NeoPa:
Thank you for your kind words. Actually, I learned a lot from you and you are still my Mentor.