# Retrieving OLE Object from Access(BLOB data) to a file



## medhak (Aug 14, 2006)

I have an Access database that contains column of type OLE Object. This column can store any OLE object including images, word doc, excel sheet etc. 
My task is to read this OLE data from OLE Object column & store it into a temp file say data.tmp so that the OLE data can be retrieved without any information loss.
Since the definition of OLE object storage is not documented (contains some header information before actual data), simply extracting the field contents as a byte array using GetChunk (0, fieldSize) and saving it to disk does not work.

I could not find the convincing solution on internet, which reads any OLE Object from Access table column, saves it in a file in such a way that, the original OLE Object data can be retrieved from this file without any data loss.
I thought of one approach as described below: 
Any OLE Object file has some standard signature. e.g. BMP image has singature as "42 4D" & files with extensions DOC, DOT, PPS, PPT, XLA, XLS, WIZ Microsoft Office applications (Word, Powerpoint, Excel, Wizard) will have singature as " D0 CF 11 E0 A1 B1 1A E1"

This approach will read the OLE Object data using DAO library & RecordSet using GetChunk (0, fieldSize) method into a byte array. Now this byte array will be compared for above file extensions & whenever the match is found the corresponding byte offset will be saved. All the data before this byte offset will be rejected (the OLE Object Header part of byte array) & the data starting from this offset will be stored as the actual data in temp file. This approach seems to be working for .bmp, .doc & .xls files. i.e. I was able to save OLE Object data for a bitmap image, word doc & excel sheet without any data loss. One thing I observed was, the size of the new file constructed from OLE Object was greater than the corresponding original file size. This may be bcos, apart from header information & actual file data, OLE object stores some additional information at the end (after actual data bytes). 
This approach may not work in the situations where actual data bytes = Signature Bytes
One more disadvantage is, it involves lot of comparisons with all the possible file signatures till the match is found. Apart from this, this approach should work for most of the cases. 
Any other major disadvantages of this approach which I am overlooking?
Any expert comments on this approach will be really appreciated. 
Thanks a lot!
Medha.


----------



## P.O.L (Oct 20, 2007)

Hi,

I am trying to solve the same problem. I was wondering if you can post sample code for me to get a starting point. I have an Access db application that saves data in an OLE Object data type. I do not know the file type of this data, but need to retreive it to a file so I can try to open it. I am using a form that I created in Access using a Bound Object Frame, then a command button with this code to retreive the data, but get nothing but garbage. This is the code I am using:

Private Sub Command2_Click()
On Error GoTo Err_cmdSave_Click

Dim a() As Byte
Dim lTemp As Long
Dim sl As String

lTemp = LenB(Me.OLEBound0.Value)
ReDim a(0 To lTemp)
a = Me.OLEBound0.Value

sl = "OLE_file" & ".dat"
Open sl For Binary Access Write As #1
Put #1, , a
Close #1

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

If you can help I would really appreciate it.

Thanks,
Stan


----------



## OBP (Mar 8, 2005)

Why not just let Access handle opening the OLE objects instead?
I have a Document Management database that does exactly that.


----------



## P.O.L (Oct 20, 2007)

OK, can you help me by posting sample code or instructions please, thanks.


----------



## OBP (Mar 8, 2005)

This database does not store the OLE objects, just their location and type. The design of the Form and Visual Basic handle the different types of objects, it may not cater for all of yours but should do for most of them.
It has an Excel worksheet showing the VBA Editor's Reference Library items that need to be set.


----------



## P.O.L (Oct 20, 2007)

Thanks, I will give it a shot


----------



## Mr2Magic (Feb 21, 2008)

Hi OBP,

i just nabbed your Databse for a test. I hope you don't mind!? Unfortunately because i work in a citrix environment my options for installing or referencing new items are limited. Can you tell me what the Kodak and fpdtc libs do and whether any other 'standard' items could maybe do the same?

Thanks for any help you can give me :O)

Mark


----------



## OBP (Mar 8, 2005)

If I remember correctly, the Kodak items are jpeg files, i.e. standard photo format.
Some items can be displayed as "Images" on the form, but others do not open the originating software properly and therefore need a Hyperlink.
I am not familiar with "citrix", but if I can help with anything specific let me know.


----------



## Mr2Magic (Feb 21, 2008)

Thanks for the quick answer! Citrix is a terminal services system which means i have no applications that run locally. All software is administrated by a citrix server. I have no way of installing any specific objects that aren't already registered. I only really need to 'play' with the following filetypes:

Word
Excel
PDF
Generic Text

So maybe I could do without the Kodak and fpdtc??? What do you reckon? 

Thanx once more


----------



## OBP (Mar 8, 2005)

Have you tried just adding an example of each of those 4 data types to the database to see if it works.
If it finds them and puts them in, then you can test to see if it will open them for you.
I think PDF is the one that needs a hyperlink on my computer as it opened the document and them promptly shut it again when I tried to use an Image.


----------



## Mr2Magic (Feb 21, 2008)

No, I haven't tried anything yet because the app doesn't run due to the missing refs. I'm not the greatest programmer so *noob question on* tell me will the app compile if i just deactivate the missing references? *noob question off*


----------



## OBP (Mar 8, 2005)

No you have to have some of them, unless you just want to try entering a "path" manually and see if it can then open it.
Did you open the Excel Worksheet to show you which ones you need?


----------



## Mr2Magic (Feb 21, 2008)

I just tested  I removed the Kodak and fpdtc refs and the app compiles - almost  I get an error telling me that 'call test' is not a valid function or procedure but i can still use the app. I also get an error when opening pdf :-( shame, because thats the one document i need most


----------



## OBP (Mar 8, 2005)

What error do you get for pdf?


----------



## Mr2Magic (Feb 21, 2008)

It shuts down here:

If Me.Document_Type = ".jpg" Or Me.Document_Type = ".jpeg" Or Me.Document_Type = ".gif" Then
Me.test.Class = "Package" ' Set class name.
Me.test.OLETypeAllowed = acOLEEmbedded
Me.test.SourceDoc = Me.Document_Location
Me.test.Action = acOLECreateEmbed
Me.test.SizeMode = acOLESizeZoom
With Me.test
.Action = acOLEActivate
.Verb = acOLEVerbOpen
End With
If Me.Document_Type = ".jpg" Or Me.Document_Type = ".jpeg" Then Me.Include_in_Photo_Catalogue.Visible = True
Exit Sub
End If
If Me.Document_Type = ".pdf" Then
MsgBox "1"
Me.Document_Link.Class = "Package" ' Set class name.
MsgBox "2"
Me.Document_Link.OLETypeAllowed = acOLELinked
MsgBox "3"
Me.Document_Link.SourceDoc = Me![Document Location]
MsgBox "4"
Me.Document_Link.Action = acOLECreateLink <==****This line****
MsgBox "5"
Me.Document_Link.SizeMode = acOLESizeZoom
With Me.Document_Link
.Action = acOLEActivate
.Verb = acOLEVerbOpen
End With
Else
Application.FollowHyperlink Me.Document_Location, , True
End If
Exit Sub

Standard error 'A problem occurred while Microsoft Office
Access was communicating with OLE server or ActiveX control'


----------



## OBP (Mar 8, 2005)

Did it highlight a particular line of code?


----------



## Mr2Magic (Feb 21, 2008)

No, unfortunately not. Maybe I need to use a different pdf control or something!? This OLE stuff is irritating when you start go non-microsoft  I do like the idea of importing all documents within a given Folder!! Very sweet bit of programing


----------



## OBP (Mar 8, 2005)

Did you select PDF when you imported the File name in to the database or did you just manually enter the file name.
The reason that I ask is that the PDF Import routine creates a Hyperlink rather than an OLE Image and it may not have done that.
I can't remember all of the code now, but I think that is what it does.
The Folder routine is vey good, especially as it searches "Subfolders" as well.


----------



## Mr2Magic (Feb 21, 2008)

Wait a sec. Is that what the field document link is for? I'll check that out straight away!


----------



## Mr2Magic (Feb 21, 2008)

No ( I don't have any valid link available. I think it's a problem with regards to the adobe control. I might just have to let our programmers have a go at this one.


----------



## OBP (Mar 8, 2005)

Did you set the dll and Library references?


----------

