Extract OLE Objects from MS Access to Disk

Problem Statement - 

Fetch the documents(images, .pdf, .doc etc.) from the MS Access(OLE Object) and store it on disk.

Initially, i thought it would be quite easy. Being a C# developer, i quickly began with the VS2010. I  already had the MS Access DB which has a table with several columns. But my focus was on only few such as

• ID - Primary Key - Int
• Name - text
• Structure - OLEObject (Contains images)
• Certificate - OLEObject (Contains PDFs)

Within half an hour, i had all the information in the DataTable with OLEObject as byte[]. Now, the point was how would i know the format of the images in the Structure columns. Because if we view the Access, it simply displays the type of document ( Bitmap Image for .bmp, Microsoft Word Document for .doc etc.), no file or extension name. Moreover , for other types, it just display the text as "Package".
To start with, i assumed that System.Drawing.Image might have the detailed information  once i convert the byte[] to MemoryStream and assign this to Image Object.

byte[] bPhoto;//Assign it with data.

int id;//Primary key - Used as the name for the image here.

/*We have also hard-coded the extension as .png for now.*/

 using (MemoryStream stream = new MemoryStream(bPhoto)){
       System.Drawing.Image tempImage = System.Drawing.Image.FromStream(stream);
       string FName = tempPathPhoto + Path.DirectorySeparatorChar + Convert.ToString(id) + ".png";
       tempImage.Save(FName);
  }

As soon as i executed the code to debug and analyze the tempImage data, it threw an exception on this line -

System.Drawing.Image tempImage = System.Drawing.Image.FromStream(stream);

and the exception was - “ Parameter is not valid. ”  
I googled it and found various links to get rid of that. But, those solutions didn't worked for me. So i tried a couple of other ways to write the files such as - 

FileStream file = new FileStream(tempPathPhoto + Path.DirectorySeparatorChar + Convert.ToString(id) + ".GIF", FileMode.Create, System.IO.FileAccess.Write);
file.Write(bPhoto, 0, bPhoto.Length);
file.Close();

and

File.WriteAllBytes(tempPathPhoto + Path.DirectorySeparatorChar + Convert.ToString(id) + extension, bPhoto);

Using the above specified ways, i was able to write the file on my disk. Hmmm.. That wasn't tough.    But these files didn't open as expected. The Image viewer displayed it as the crashed image and the PDF were displayed as corrupted as well. I was on Zero again. 

After googling with some new keywords, i landed on this page. I found more than 50 blogs and sites pointing to this link. The first thing I (and most probably whoever visited) noticed was that the site is out of support. But don't worry, the download link and the project works just fine. Run the Access page and fill all the fields accordingly and viola!! It extracted the files perfectly. Nice work Mr. Leban.

All my .bmp,.pdf files were there in the correct place. But wait!! What about the other file format. Those who had banged their head on wall might have noticed that when we insert the .bmp file, the field value says "Bitmap File Format" but when we insert other type of images, it display "package". When you try to extract the data with "package" name, the code runs smoothly but the files were created with ".pak" extension and that too didn't opened up correctly. So, Is the issue with the images uploaded?? - I don't think so as the Access form displays the images perfectly.  So, what is the issue??

Lets see the code. I went through almost all the lines of my interest especially the fGetContentsStream(). Going through the  "package" case, Mr. Leban mentioned all the bits he has added to the "lPos" variable  so as to get the correct data from the file. I don't know if this is the case only with me because thousands of people have used his code and if there is some issue there, someone must have pointed it out. Anyways, i analyzed some of the files that were getting displayed as "package" and found that before extracting the file information( such as File Name, Full Path etc.), the lPos value should be set to "84".
So i reinitialize the lPos just before the following line of code in fGetContentsStream() "Package" case - 

'Reset the lPos
lPos = 84

' Package original File Name
Do While bCurValue <> 0
    bCurValue = arrayOLE(lPos)
    FileNamePackage = FileNamePackage & Chr(bCurValue)
    lPos = lPos + 1
Loop

This is it. Run the form again and it will extract all the OLE Object named as "package". All hail Mr. Leban.

One more issue, i faced was with MS Office 2007 Docs. For ex- .doc file would be extracted fine but the .docx file be displayed as corrupted when you try to open it.
I tried some alterations and after a while of head-banging, i got this code working - 

Case "Word.Do"
' MS Word document

sExt = "doc"
If sClassName = "Word.Document.12" Then
FileExtension = "docx"
sStreamName = "Package" ' This is important
Else: FileExtension = "doc"
sStreamName = "WordDocument"
End If

And

Select Case sStreamName

Case "CONTENTS", "Package" ' Package added by Shadab
' Contents stream in Root Storage
' Call our function in the StrStorage DLL
    lLen = GetContentsStream(arrayOLE(0), UBound(arrayOLE) - lOffSet, sStreamName)

Case "Ole10Native"
' "Ole10Native" stream in sub storage named "Embedding 1" Of Root Storage
' Call our function in the StrStorage DLL
    lLen = GetContentsStreamChild(arrayOLE(0), UBound(arrayOLE) - lOffSet, sStreamName)

Case Else
' Need to fix this to allow for other stream names other than Office Docs.
' Extract Office doc
    lLen = ExtractOfficeDocument(arrayOLE(0), UBound(arrayOLE) - lOffSet, sStreamName)

End Select

I was able to extract .docx files now. Unfortunately, the same didn't work for the other files such as .xlsx (Excel2007). Not that much important as of now but I am still looking for the workaround for this problem. Any suggestions???

Thanks
S. Shafique
Fokat RnD Team Member