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.
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.
ASKER
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.
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.
ASKER
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?
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?
<<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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<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.