Extract PDF files from OLE field in Access 2007

jeff,

Thanks for the info

I've looked at option 2 - and modified it to accept the accdb format rather than the old mdb - but when I ran it against my first ole field - 2411 records with pdfs - it extracted 7 files.  There where no errors but I haven't been able to figure out why only 7 files  where extracted.

I'll take a look at options 1 & 3 and see what that gets me.

Option 4 is my last resort - with over 5000 images I just don't have the time


Thanks
Pamela

I haven't been able to figure out why only 7 files  where extracted.

Not sure either, ...perhaps those PDF's were created by a certain version of Adobe...?

Here is some code from my Working with Word ebook for extracting files from an OLE Object field in an Access 2003 database (I also have code for extracting files from an Attachment field in an Access 2007 or higher database):

      'Extract template from the bound object frame control in the
      'subform and save it to the Templates path
      blnEmbedded = True
      With Me![subWordTemplates]![frbWordTemplate]
         .Class = "Word.Template"
         .Verb = acOLEVerbOpen
         .Action = acOLEActivate
         Set docTemplate = .Object.Application.Documents.Item(1)
      End With
      
      'Save the extracted template to the default Templates path
      docTemplate.SaveAs FileName:=strTemplateNameAndPath, _
         FileFormat:=wdFormatTemplate

Select allOpen in new window

This code is for extracting a Word template, but I think it would work with a PDF file as well.  Note that it doesn't work directly with the table, but with a form that has a bound object frame control bound to the OLE Object field.  With an Access 2007 (or higher) database using an Attachment field, you can work directly with the table, using this procedure:

Public Sub SaveAttachment(strTemplate As String)
'Created by Helen Feddema 1-Nov-2009
'Last modified by Helen Feddema 9-Jun-2014

On Error GoTo ErrorHandler
   
   Dim rstAttachments As DAO.Recordset
   Dim rstTable As DAO.Recordset
   Dim strDefaultTemplatesPath As String
   Dim strSearch As String
   Dim strFileAndPath As String
   
   Set appWord = GetObject(, "Word.Application")
   
   'Get default Templates path from Word Options dialog
   strDefaultTemplatesPath = _
      appWord.Options.DefaultFilePath(wdUserTemplatesPath) & "\"
   strFileAndPath = strDefaultTemplatesPath & strTemplate
   
   Set rstTable = CurrentDb.OpenRecordset("tlkpWordTemplates", _
      dbOpenDynaset)
   strSearch = "[TemplateName] = " & Chr(39) & strTemplate & Chr(39)
   'Debug.Print "Search string: " & strSearch
   
   rstTable.FindFirst strSearch
   If rstTable.NoMatch = False Then
      'Create recordset of attachments for this record
      Set rstAttachments = _
         rstTable.Fields("WordTemplate").Value
      With rstAttachments
         Do While Not .EOF
            'Save this attachment to a file in the default Templates folder
            'Debug.Print "Saving " & strFileAndPath
            .Fields("FileData").SaveToFile strFileAndPath
            .MoveNext
         Loop
         .Close
      End With
   End If
   
   rstTable.Close
   
ErrorHandlerExit:
   Exit Sub

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 _
         & " in SaveAttachment procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Select allOpen in new window

After 3 days of playing around with this I have not been able to get any of the options 1-3 to work with the PDF files.

But in the process of playing with the code I did discover one thing that helped – some of my OLE files were added as embedded and others as linked.  Also my data does have a field that holds the folder path of where they saved the PDF as they transitioned away from using the OLE fields (the used both for about a year).  I did find 500+ records that had no folder path but the OLEs were linked files – by opening a couple of the files in text mode I was able to figure out where the linked files where stored (a different path on my server) and move them to our current location & create folder links in the new database.

I had originally hoped to compare the db OLE documents to the folder link documents for the other 1900+ records to ensure that all the OLE documents had been saved in the folders but since I was unable to reference the original file names I settled for count comparison between the OLE files and folder files – with this check I found about 20 files that hadn’t been saved into the folders (I saved these manually)  
With these results I’m hoping that the majority of OLE documents had been saved in the folders already – not a perfect solution but it will have to do at this point.

Awarding the points to Jeff because his links helped find the linked files for the 500+ records.

Ok
Thanks,

Sorry I could not provide a more complete solution