This one took more research and experimentation than I originally thought it would. There are a lot of posts out there on the internet that ask how to do this, but not many answers that actually work. I found the key that finally lead to a solution here. What really threw a wrench in the gears was the fact that Microsoft completely changed the file format between Office 2007 and previous versions, so the code to extract a Word 2003 document does not work to extract a Word 2007 document. It was definitely a challenge, and also a bit of fun (programmers are strange like that).
One side note before the code listing: the new Office 2007 file format is actually a zip file. If you change the extension of a Word 2007 document from .docx to .zip, you can open it with explorer and see how they are storing the various pieces of the document.
Now for the part you came here to see. I will provide a link at the bottom of this post to the vb class that will extract files from OLE Object fields in an Access database. You will need to modify it to fit your needs because your database will have different field names. The class in only about 450 lines long, contains no API calls, and does not require any of the programs used to create the files to be installed on your system. Access is not even required.
Let me know if it is useful to you. I appreciate positive feedback.
' this is how I use the class Public Sub Extract_Ole_Objects() Dim sql As String = "select * " & _ "from tblCorrespondence " & _ "where XPicture is not null" Dim dn as String = "c:\data\filetest\" Using dt As DataTable = m_Source.Execute_DataTable(sql) Using o As OleObjectExtraction = New OleObjectExtraction(dt, dn) o.Do_It() End Using End Using End Sub
Hopefully this will keep you from spending a week just to get data out of an Access table.
You can download the source code here: