How Do I Use Word VBA To Open An Attachment RecordSet From Access 2007 DB and Insert The Attachment (Image) In Word 2007?

davysouthernboy
davysouthernboy used Ask the Experts™
on
I have been trying to use ODBC  and Word 2007 VBA to to retrieve an attachment (an image) and insert it into a Word document.

Here is some sample code that I was playing with:

Private Sub InsertDigitalSignature()



'Establish a connection to database

Set cnn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set rst = CreateObject("ADODB.Recordset")

cnn.Open "DSN=ECCM2009;uid=sa;pwd=;"

Set cmd.ActiveConnection = cnn

Dim childRst As ADODB.Recordset
Set childRst = CreateObject("ADODB.Recordset")


cmd.CommandText = "SELECT [Digital_Signature] FROM [Staff] INNER JOIN [Cases] on [Staff].[ID] = [Cases].[Case_Manager_ID] WHERE Claim_Number = '" & Claim_Number & "'"

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockBatchOptimistic

Do While Not rst.EOF

childRst = rst.Fields("Digital_Signature").Value

MsgBox (childRst.Fields(0).Value)
MsgBox (childRst.Fields(1).Value)
MsgBox (childRst.Fields(2).Value)

Dim Entry As String

Entry = ""


With Selection
    .Collapse Direction:=wdCollapseEnd
    .MoveDown Unit:=wdLine, Count:=2
    .Font.Bold = False
    .Font.Underline = False
    .TypeParagraph
    .TypeParagraph
    .InsertAfter Text:=Entry
End With

rst.MoveNext
Loop
rst.Close
cnn.Close
End Sub

However, the above code hangs on childRst = rst.Fields("Digital_Signature").Value with a message saying an object was expected.  However, if I change the above line to read MsgBox(rst.Fields("Digital_Signature").Value) then I get the image's filename with the extension.  However, my understanding was rst.Fields("Digital_Signature").Value should be a recordset that contains the name of the image and the binary data that constitutes the image.

All the examples I've found on the web use DAO, but when I try to use DAO in Word VBA 2007, I get a message saying that this is not supported.

Any assistance would be greatly apprecated.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You made childRst as a recordset and then tried to equate it to a value of a field in another recordset. If Digital_Signature is text, you should declare childRst as a string. If Digital_Signature is a number, you should declare childRst as a Long or a Double.
-Chuck
Top Expert 2009
Commented:
Here is some code for working with Access attachments (saved in the new Attachments-type field in Access 2007).  You will need to set a reference to DAO 3.6 object library in the Word module to use DAO.  The code is Access VBA, so you will need to modify it for use in Word VBA, but it should be of some help for the syntax needed to work with Attachments fields.

A zip with the Access Archon article with this code, and a sample database, can be downloaded from this link:  http://www.helenfeddema.com/Files/accarch188.zip
Public Function LoadAttachments()
 
On Error GoTo ErrorHandler
   
   Set appWord = GetObject(, "Word.Application")
   strDefaultDocsPath = appWord.Options.DefaultFilePath(wdDocumentsPath) & "\"
   strDocsPath = GetProperty("InputDocsPath", strDefaultDocsPath)
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fld = fso.GetFolder(strDocsPath)
   Set dbs = CurrentDb
   Set rstTable = dbs.OpenRecordset("tblContacts", dbOpenDynaset)
   
   For Each fil In fld.Files
      strFile = fil.Name
      Debug.Print "File name: " & strFile
      Debug.Print "File type: " & fil.Type
      
      'Check whether file name starts with 'Contact ID'
      If Left(strFile, 10) = "Contact ID" Then
         'Extract Contact ID from file name, using Mid and
         'InStr to start at the beginning of the number and
         'end before the space following the number,
         'if there is one
         strTest = Mid(String:=strFile, Start:=12, Length:=3)
         intSpace = InStr(strTest, " ")
         
         If intSpace > 0 Then
            lngContactID = CLng(Mid(String:=strTest, _
               Start:=1, Length:=intSpace - 1))
         Else
            lngContactID = CLng(strTest)
         End If
         
         strSearch = "[ContactID] = " & lngContactID
         Debug.Print "Search string: " & strSearch
         strFileAndPath = strDocsPath & strFile
         
         'Search for matching Contact ID in table
         rstTable.MoveFirst
         rstTable.FindFirst strSearch
         If rstTable.NoMatch = True Then
            strTitle = "Can't find contact"
            strPrompt = "Contact ID " & lngContactID _
               & " not found in table; can't add attachment"
            GoTo NextDoc
         Else
            rstTable.Edit
            'Create recordset of attachments for this record
            Set rstAttachments = _
               rstTable.Fields("File").Value
               
         'Turn off error handler to prevent errors if the
         'code attempts to add the same file twice; in this
         'case the Attachments recordset won't be updated
On Error Resume Next
               
            With rstAttachments
               .AddNew
               .Fields("FileData").LoadFromFile _
                  (strFileAndPath)
               .Update
               .Close
            End With
            rstTable.Update
            Debug.Print "Added " & strFileAndPath _
               & " as attachment to Contact ID " _
               & lngContactID; "'s record"
         End If
      End If
      
NextDoc:
   Next fil
   
   'Open Contacts form to see the attachments
   'that have been loaded
   DoCmd.OpenForm FormName:="frmContacts"
   
ErrorHandlerExit:
   Exit Function
 
ErrorHandler:
   If Err = 429 Then
      'Word is not running; open Word with CreateObject
      Set appWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " _
         & Err.Description
      Resume ErrorHandlerExit
   End If
   
End Function
 
Public Function SaveAttachments()
 
On Error GoTo ErrorHandler
   
   Set appWord = GetObject(, "Word.Application")
   strDefaultDocsPath = appWord.Options.DefaultFilePath(wdDocumentsPath) & "\"
   strDocsPath = GetProperty("OutputDocsPath", strDefaultDocsPath)
   Debug.Print "Output docs path: " & strDocsPath
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fld = fso.GetFolder(strDocsPath)
   Set dbs = CurrentDb
   Set rstTable = dbs.OpenRecordset("tblContacts")
   
   Do While Not rstTable.EOF
         'Create recordset of attachments for this record
         Set rstAttachments = _
            rstTable.Fields("File").Value
         With rstAttachments
            Do While Not .EOF
               strFileAndPath = strDocsPath _
                  & .Fields("FileName")
               
               'Save this attachment to a file in the Output
               'Docs folder
               Debug.Print "Saving " & strFileAndPath _
                  & " to " & strDocsPath & " folder"
 
               'Turn off error handler to prevent errors if
               'the file already exists in the folder.
               
On Error Resume Next
 
               .Fields("FileData").SaveToFile strFileAndPath
               .MoveNext
            Loop
            .Close
         End With
         rstTable.MoveNext
   Loop
   
   rstTable.Close
   strPrompt = "All new attachments saved to " _
      & strDocsPath & " folder"
   strTitle = "Done!"
   MsgBox strPrompt, vbOKOnly + vbInformation, strTitle
   
ErrorHandlerExit:
   Exit Function
 
ErrorHandler:
   If Err = 429 Then
      'Word is not running; open Word with CreateObject
      Set appWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " _
         & Err.Description
      Resume ErrorHandlerExit
   End If
 
End Function

Open in new window

Author

Commented:
Helen,

I actually attempted to use DAO 3.6 to start with instead of ODBC.  However, see, I'm using Access 2007's new accdb file format, which DAO 3.6 does not recognize as being a valid file format.  
In other words, this:

Dim db As DAO.Database
Set db = DAO.OpenDatabase("c:\\users\\davieoverman\\documents\\eccm\\eccm2009.accdb")

generates runtime error 3343:  "Unrecognized database format".  

That's why I was trying to work with attachments with ODBC.  

It's very strange that I can use DAO in an Access 2007 VBA module but not in a Word 2007 VBA module.  

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009
Commented:
I replicated this error -- I guess I haven't yet had occasion to work with an Access 2007 database from Word VBA.  In Access, you have the new, peculiarly named object library called Microsoft Office 12 Access database engine Object Library, which more or less replaces DAO -- but it is not visible for selection in the References dialog from Word VBA.  I tried selecting it by browsing.  (It should be in this location:  C:\Program Files\Common Files\Microsoft Shared\OFFICE12\ACEDAO.DLL, in case you want to try.)  However, after selecting it, the reference did not appear in the list of references, so apparently that is not supported.  It might be possible to work with the Attachments collection using an ADO recordset instead -- I will test that and post the results later today.
Helen_Feddema has the answer. Here are the steps to add Access 2007 DAO to your Word project:
1. In the References dialog box browse to C:\Program Files\Common Files\Microsoft Shared\OFFICE12\ACEDAO.DLL, select it, click the Open button, and close the dialog box.
2. Close Word and reopen it.
3. In the References dialog box, scoll down and select the "Microsoft Office 12 Access database engine Object Library", check it, and close the dialog box.
4. You will now be able to use DAO to connect to your accdb file.
I tested it and it works in Word 2007.
-Chuck
Top Expert 2009

Commented:
Great!  I will note that.  In the meanwhile, I came up with an alternate, much more awkward solution involving getting to the DBEngine object through the regular Access object model, but this is much cleaner.
Top Expert 2009

Commented:
I also tried ADO, and though I was able to get an ADO connection to an Access 2007 database, using the ACE provider instead of Jet, the Field object did not support the methods needed to work with the Attachments recordset, so it wasn't much use.

Author

Commented:
Thank you Chuck and Helen. That was exactly what I needed to know. :)
You are welcome. Good luck with your project.
-Chuck

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial