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")
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
Dim Entry As String
Entry = ""
.MoveDown Unit:=wdLine, Count:=2
.Font.Bold = False
.Font.Underline = False
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.