Extract Access 2003 OLE field objects to file with VBA?

Situation:

I am preparing to sunset a very old Access 2003 FE/SQL Server 2008 R2 BE data application in the next 6 months. Part of the process will be to convert the data to PDF/A for importation into a new application and to store it for long-term offline archiving.

Problem:

A large amount of this data is stored as embedded OLE objects: Word/Excel 2003 and PDF. It is accessed through a series of ODBC linked tables connected to the SQL BE.

I have been using the S. Lebans ExtractInventoryOLE tool to successfully iterate through sets of related OLE objects and dump them to a folder hierarchy in their native format using Access 2003 but it does not appear to work with Access 2016. I want to get rid of any dependency on Access 2003 ASAP so I would prefer this to work in Access 2016.

The SSGetContents.dll declaration statement that the Lebans solution uses does not seem to be working in Access 2016, even with PtrSafe applied. This is all being done in a 32 bit environment.

Question(s):

1) Is the Lebans DLL and related code compatible with Access 2016?

2) If not, can Access 2016 VBA natively read OLE fields and export or print the embedded documents to PDF/A format?

3) If not, are there any commercial API solutions that will provide me with the tools that I can use to do this? I still need to be able to write code around the extraction and conversion routines so a batch solution without program control may not do the trick.
LVL 1
kkammAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not sure I understand why this needs to work in Access 2016...I would simply continue to use A2003 to dump the objects to disk and be done with it.

<<1) Is the Lebans DLL and related code compatible with Access 2016?>>

 As long as your using a 32 bit version of Access, I don't see why not.   but there have been a number of changes in Access since then.   I've never looked at the DLL, but I can't think of any reason off-hand that it would not work.

<<2) If not, can Access 2016 VBA natively read OLE fields and export or print the embedded documents to PDF/A format? >>

  Yes, but I don't know that it will read the older formats anymore.  Construct a form with a bound OLE object frame as a test and see if you can see the object.  If so, you can save it to disk that way.

<<3) If not, are there any commercial API solutions that will provide me with the tools that I can use to do this? I still need to be able to write code around the extraction and conversion routines so a batch solution without program control may not do the trick.>>

  None that I'm aware of.   Almost no one stores objects as embedded, especially with the older versions because of database bloat.   I wrote a form based extraction utility years ago which was used by a few people, but Stephen is the one that really had it covered and no one worked on it outside of him that I am aware of.

  If you must use A2016, try the test and if it works, I'll dig out the code I used.

Jim.
kkammAuthor Commented:
OP UPDATE:

I deleted and recopied  SSGetContents.dll and StrStorage.dll to /System32 and, for whatever reason, the extractions are working  in Access 2016. Possibly the files were corrupt(?)

That leaves the second part of the question - conversion to PDF/A. I have access to the raw byte array containing the OLE extraction BUT it seems like it is going to take a Rube Goldberg approach to get that data to file as a PDF/A. Any suggestions for streamlined approaches would be much appreciated.
kkammAuthor Commented:
Jim,

Thanks for the response. I posted the update before I saw your response.

The OLE extraction appears to be working in 2016 now. I had considered the bound OLE frame approach early on but I remembered the Lebans tool and wanted to give it a shot out of curiosity. Surprisingly, it worked so I started to build out a generalized application around it so I wanted to continue along that path using Access 2016 to hopefully leverage some of the new capabilities but we also have some internal reasons - mainly security - for deprecating Office 2003.

As indicated in the update post, it looks like going from a byte array to a PDF object is going to require a bit of a convoluted path. I haven't used Access 2016 long so I am not aware of some of the newer tools that may help facilitate this but it strikes me that I will have to include an intermediate step of streaming to file first to do the conversion. Are there any more streamlined options?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I deleted and recopied  SSGetContents.dll and StrStorage.dll to /System32 and, for whatever reason, the extractions are working  in Access 2016. Possibly the files were corrupt(?)>>

 Some .DLLs need to be in \system32 even on a 64 bit OS.

<<As indicated in the update post, it looks like going from a byte array to a PDF object is going to require a bit of a convoluted path. I haven't used Access 2016 long so I am not aware of some of the newer tools that may help facilitate this but it strikes me that I will have to include an intermediate step of streaming to file first to do the conversion. Are there any more streamlined options?>>

 I would let Access do the work and use a form with a bound object frame.   It should be possible to "print" to PDF from that (you certainly can save to disk and Access has PDF capability with an add-in, or you can use something like CutePDF as a printer driver).  I don't think I've ever done that, but you should have the full capabilities of the OLE server.

  This by the way is the major difference between this and Stephan's approach; with his, you didn't need the software installed to get it to disk.   But as your finding out, if you don't have it installed, there's not much you can do with it afterward.

  Also keep in mind that I'm answering this from the viewpoint of doing a one-time conversion and just getting the objects to disk.  Based on your last comment, it seems like you want to do more with it, so what I'm suggesting might not be good.

Jim.
kkammAuthor Commented:
Jim,

Sorry for the delay. I had a few days off.

Do you have that block of code from your OLE export project? Printing to PDF through a bound frame will at least give me a short-term path to getting these docs converted. Points would be awarded if I can get at least a couple of Word docs to PDF/A format as a proof of concept.

Thanks,

Bruce
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So I went back and looked at what I had done, which was for bitmaps:

Private Sub btnExport_Click()

  Dim rst As Recordset
  Dim strFileName As String
  
  ' Export all BMP's to current directory.
  
  Set rst = Me.RecordsetClone
  
  ' Jump to the first record.
  rst.MoveFirst
  
  Do While Not rst.EOF
    ' Move the forms recordpointer to the same record.
    Me.Bookmark = rst.Bookmark
    strFileName = rst![Employee ID]
    
    With Me![olePhoto]
         .Verb = -2
         .Action = acOLEActivate
         SendKeys "%F", True
         SendKeys "A", True
         SendKeys strFileName, True
         SendKeys "%S", True
         SendKeys "%F", True
         SendKeys "X", True
         DoEvents
    End With
    
    ' Get the next record.
    rst.MoveNext
  Loop
  
  ' Clean up
  rst.Close
  Set rst = Nothing
  
End Sub

Open in new window


   The idea being that you had a bound object frame on the form, and that you used the recordsetclone to move through the records.

   The SendKeys statements would depend on the OLE server that is supporting the object.  In this case it was MS Paint, and those commands save the BMP to disk.

    I did this in Access 97, and although a lot has changed since then, there is no reason why it would not work.    I never extended it beyond bit maps though because Stephan came along and did it differently, which many found more appealing (they had a DB, but not the software that created the object).

   Hopefully this will get you going.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.