CountryGirlMD
asked on
Extract PDF files from OLE field in Access 2007
I have an Access database in the 2007 .accdb format that contains a number of OLE fields with embedded PDF files. I need to extract the PDF files and save them to disk. I’ve seen examples for saving embedded word documents, see link below, but none for pdf files.
http://support2.microsoft.com/default.aspx?scid=kb;EN-US;Q132003
I have over 2400 records with 5000+ pdf files. When I open the documents manually they contain the original file name, I would like to save the files with their original names.
Can the Word example be modified to work with PDFs or is there another solution?
Any help with this task would be appreciated.
http://support2.microsoft.com/default.aspx?scid=kb;EN-US;Q132003
I have over 2400 records with 5000+ pdf files. When I open the documents manually they contain the original file name, I would like to save the files with their original names.
Can the Word example be modified to work with PDFs or is there another solution?
Any help with this task would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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):
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:
'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
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
ASKER
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.
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
Thanks,
Sorry I could not provide a more complete solution
ASKER
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