Extract PDF Files from OLE Field

[PTNL]

I have a client that is using an Access 2003 form as the front-end interface for their SQL Server 2005 database. All data is stored within the SS2005 database, and Access is just the user interface.

The problem they are running into is with the "Image" column that holds a file. The Access 2003 form is using a bound field for inserting the data, and is adding in the extra OLE headers to each file.

What they want is to have the following:
1 - Extract all of the original files from the "OLE Object" data to the file system.
2 - Add a control to the Access 2003 form that will do file writes to the SS2005 database, but with the file contents in the "image" column and the filename in the newly-added "varchar" column.



Related thoughts:

For #1.... From various Google-ing, I've learned that this is not an easy feat if you're trying to extract something other than an image file (gif, jpg, bmp, etc.). Something about adding a binary offset to skip past the "ole header". I did find a possible solution here, but my current post count in the message boards does not allow me to send a direct link. You can find the options I am looking at by searching on Google-Groups for the following string:
"pdf ole header access" (w/o quotes)


For #2.... Is it even possible for an Access 2003 form to upload a normal BLOB without the extra OLE information? If so, any links on doing a file upload without OLE info getting added is appreciated.


Additionally, I'll have some extra steps that will need to be done. Notably reading the PDF's individually and writing the BLOB's and filenames to the SS2005 database. But I can accomplish this easily with some DotNet code.


This is my first post to the VBA Express community, and it probably won't be the last :) I'm looking forward to everyone's thoughts on these issues.

TIA !!!

[PTNL]

Anyone?

[jake2008]

Hi!

Transfering a file into a BLOB is totally doable. You should use ADO and interate through chunks.

Have a looksy:

(stupid posting application won't allow the post with a link, so reassemble the URL below :dunno)
www26.brinkster.com/ alzowze/ blobs/ blobs.asp


Transfering out OLE's already embedded in image fields is kind of a pain in the arse. A method would be to activate the OLE (thus launching the object's client application), hooking to that application (easy if it's got COM hooks, rediculous if not), and calling a "SaveAs" operation on the object's client app. I've gone this for AutoCAD files.

Good luck!

[PTNL]

Hi!

Transfering a file into a BLOB is totally doable. You should use ADO and interate through chunks.

Have a looksy:

(stupid posting application won't allow the post with a link, so reassemble the URL below :dunno)
www26.brinkster.com/ alzowze/ blobs/ blobs.asp
I looked at the two code snippets at the bottom of that page. Would that work as-is when dropped into Access VBA?
If so, would this also ensure that no "OLE Header" or extra binary information gets added to the BLOB?



Transfering out OLE's already embedded in image fields is kind of a pain in the arse. A method would be to activate the OLE (thus launching the object's client application), hooking to that application (easy if it's got COM hooks, rediculous if not), and calling a "SaveAs" operation on the object's client app.
Iterating this manually is not necessarily preferred, as there are 3000+ files that would have to be extracted. But getting the files out is a priority, even if that involves clicking one/two times per file. The advantage I have is that every uploaded file was a PDF, so if binary offsets have to be done, I can do so.... it's just I have not yet found what that offset would be for a PDF.
Anyways, since a Record Selector control is being used, I could run a check on the "Update" or "Current" events of the form, and perhaps trigger some conditional extraction if an OLE object is found.


And as a side-thought... Getting the data out is a goal, but the next concern is to keep this from happening again. If Access forms cannot upload a file to a BLOB field without the OLE Object garbage, then I need to know that to pass on to the client so that they can start evaluating other options for a user interface for their data that resides on Sql Server 2005. Just thinking ahead on this...

[PTNL]

Problems resolved!!

For # 1....
Stephen Lebans wrote an Access forms app that will extract any files from the OLE object type, and write them to the file system without the extra OLE Header junk.
Link: www dot lebans dot com/oletodisk.htm (meh, link post count still won't let me post links yet...)

Instead of some trial/error guesswork on where the OLE Header ended, he just let Access open each file through its normal operations, then wrote the file to the file system using the details presented by Access. The app first tries writing to the original file's location, but will allow alternate placement options if that is not resolvable, if the the filename was not available, etc.


For # 2....

Some creative Google-ing for different individual tasks (VBA Dialog box, VBA file stream class, etc.) gave me the individual pieces that I needed to assemble and customize to allow the user to push files to the database without using a OLE Bound field. Basically binding the SQL Server Image column to a text box (as well as the filename to a textbox) allowed me to update the contents programmatically. This worked very well once I wrote a .Net app to take the files returned by Leban's app above and stream them to the SQL Server in the appropriately matched records for the Image and VarChar columns for FileContents and FileName respectively.

Thanks to all that chimed in on this thread!!