Solved

Extract PDF files from OLE field in Access 2007

Posted on 2015-01-27
6
796 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:CountryGirlMD
  • 3
  • 2
6 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40573620
1. You may wish to investigate the Adobe SDK.
There may be some info there on the VBA syntax to modify the MS code you link refers to...
http://www.adobe.com/devnet/acrobat/downloads.html
http://www.khk.net/wordpress/2009/03/04/adobe-acrobat-and-vba-an-introduction/
...or perhaps they already have a tool to extract PDF form OLE datatype fields.

2. Years ago, ...this would work:
http://www.lebans.com/oletodisk.htm
...But I tried it in 2010 and it threw an error.
Sadly the author has retired form Access work.
...But feel free t try it, ...Perhaps you can modify the code and get it to work.
Or perhaps you have an older configuration, ...and this will still work...

3. Here is a link to a "Sendkeys" solution for this posted *years* ago:
http://www.experts-exchange.com/Database/MS_Access/Q_21540911.html
http://www.experts-exchange.com/Database/MS_Access/Q_21540911.html#a14773976

4. I know it may be unrealistic, but the only other alternative I can see is to open each of these files through the OLE control and save it manually to a disk file. To make this less painfull, perhaps you could just designate the most crucial PDFs and extract them first...?

5. I have seen code to extract the OLE to BLOB, ...but again, here there is a lot of convoluted code involved, ...you can search the web or here for more info on this.

Sorry for all the links, ... But I tried to provide as much info as I could.
Extracting data form OLE fields was never a straightforward proposition.

Buts lets see if any other Experts can contribute more...

JeffCoachman
0
 

Author Comment

by:CountryGirlMD
ID: 40573772
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40574369
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...?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40577723
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

Open 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

Open in new window

0
 

Author Closing Comment

by:CountryGirlMD
ID: 40589366
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40589762
Ok
Thanks,

Sorry I could not provide a more complete solution
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now