Download OLE Embedded Object in Access

[jennuhw]

Someone setup a database and saved all of the files in the database. Now, I am the one that gets to get all of the files out. There are 3 different file types: pdf, tif and dwg. I have searched all over, but all I can find is how to export BLOBs which these are not. Even if I could just open the file from the dataset that would help tremendously. Thanks!

 

[ThatRickGuy]

not sure if it would work, but you could try creating a file object and setting it equal to the value of the field in a data set.

Or maybe you could copy the value to a byte array and then write the byte array to the disk.

.tif files are graphics, you may be able to read the field in and load it into a picture box or something.

just some ideas. writing the byte array to the disk would probrably be the most likely to work.

-Rick

----------------------

http://www.ringdev.com

 

[jennuhw]

Do you know where I can find documentation on writing the byte array to disk? I found some, but it won't let me do it from a table only a form. Thanks!

 

[ThatRickGuy]

This link Chrissie posted shows you how to get an image out of a table and into a memory stream. What you want to do is similar.

http://www.vbcity.com/forums/faq.asp?fid=44&cat=Database&#TID74665

assuming you have a dataset ds that has the embeded object in it:

    Dim buffer() As Byte = CType(ds.Tables(0).Rows(0)("TheColumnName"), Byte())
    Dim myFileName As String = "C:\" 'create a file name
    Dim fs As New System.IO.FileStream(myFileName, IO.FileMode.CreateNew)
    Dim strm As New System.IO.BinaryWriter(fs)
    strm.Write(buffer)

Untested, but give it a whirl.

-Rick

----------------------

http://www.ringdev.com

 

[jennuhw]

I went to vbcity and checked out the code. I got it to export a file, but it is corrupted. From what I understand, when the file is embedded into the database, it gets another header put on it. So, somehow, I have to get rid of the extra header. Any ideas on how to open the files? Thanks!

 

[ThatRickGuy]

you could try figuring out how long the new header is. Open the files in a hex editor and see if there is a pattern to the first few bytes. Or if you can find out what the original header was you may be able to find where it starts.

Then you can work with the streams and skip the first few bytes that are the header.

-Rick

----------------------

http://www.ringdev.com

 

[jennuhw]

I think I am on the right track now. I do have one problem. I found where the files' header begins. And I am getting rid of the header, but now the file is corrupt! My guess is becuase I am not doing this through hex. Here is my code:

      Dim filepath, filename, checkingFile As String
        Dim objStreamReader As StreamReader
        Dim objStreamWriter As StreamWriter
        Dim z As Integer
        Dim s As String
        filepath = "\\Esp3\company\Purchasing dept\DrawingDB\Database\test\"
        filename = Dir$("\\Esp3\company\Purchasing dept\DrawingDB\Database\test\*.txt")

        'Do While filename <> ""
        checkingFile = filepath & filename
        objStreamReader = File.OpenText(checkingFile)
        s = objStreamReader.ReadToEnd

        z = s.IndexOf("AC1015")
        s = s.Remove(0, z)

        'objStreamWriter.Write(s)
        Dim SwFromFile As StreamWriter = New StreamWriter("c:\test123.dwg")
        SwFromFile.Write(s)
        SwFromFile.Flush()
        SwFromFile.Close()

Any suggestions?
Thanks!!

 

[ThatRickGuy]

hmm, it could be a few things. Access could put a footer on the file also. The filename might have to be the same as the original (If it's in the original file's header). Working with the binary file as a text stream could also do it. Or the header could be longer/shorter then you think. I'd love to munky arround with it, but it's 5:04pm on a friday afternoon yo If it still isn't working by monday I'll try some stuff out.

-Rick

----------------------

http://www.ringdev.com

 

[jennuhw]

I tested it in a hex editor, and all of the files came out fine. I will try it as a text stream today. Thanks for all of your help!

 

[jennuhw]

I have been trying this! I know, still???? Ugh, I wish the nightmare was over! I am trying to get the excess header off before saving the file to disk. This is what I have, but indexof is only spitting back out 0 or -1. From what I have read, it is suppose to spit out the index. Am I losing it??

Dim buffer() As Byte = CType(ds.Tables(0).Rows(x)("drawing"), Byte())
'code to figure out file name and stuff
Dim myObjectOdd As Object = 65
y = buffer.BinarySearch(buffer, myObjectOdd)
'MsgBox(myFileName)
MsgBox(y)

y isn't the index of the the object.  I am at a loss!  Thanks!!

 

[ThatRickGuy]

The following code successfully pulled PDF's out of an access database.
System is Win XP Pro, Office 2k, VB.Net 2k2, .Net 1.1 and Adobe 1.1

Not sure on the .Tif and .Dwg files though.

On a side note, if this doesn't need to be performed programmatically, you should be able to highlight the cell that contains the file and press [Control]-C to copy, then paste it right into explorer.

    Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source = db1.mdb"
    Dim buffer() As Byte
    Dim myFileName As String
    Dim fs As System.IO.FileStream
    Dim strm As System.IO.BinaryWriter

    Dim conn As OleDbConnection = New OleDbConnection(conString)

    Dim selectString As String            'Variable to hold the SQL statement.
    Dim cmd As OleDbCommand         'Create an OleDbCommand object.
    Dim reader As OleDbDataReader   'Create an OleDbDataReader object.

    Try
      'Open the connection.
      conn.Open()

      'Initialize SQL string.
      selectString = "SELECT item " & _
                     "FROM items "

      'Initialize OleDbCommand object.
      cmd = New OleDbCommand(selectString, conn)

      'Send the CommandText to the connection, and then build an OleDbDataReader.
      reader = cmd.ExecuteReader()

      'Loop through the records and print the values.
      Dim i As Integer
      While (reader.Read())
        i += 1
        buffer = CType(reader(0), Byte())
        myFileName = Application.StartupPath & "\SaveFile" & i & ".unk"

        If System.IO.File.Exists(myFileName) Then
          fs = New System.IO.FileStream(myFileName, IO.FileMode.OpenOrCreate)
        Else
          fs = New System.IO.FileStream(myFileName, IO.FileMode.CreateNew)
        End If

        strm = New System.IO.BinaryWriter(fs)
        strm.Write(buffer)
        strm.Close()
      End While

      reader.Close()
      conn.Close()

    Catch Excep As System.Exception
      MessageBox.Show(Excep.Message, "Access Database", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

-Rick


----------------------

http://www.ringdev.com

 

[jennuhw]

PDF's aren't really giving me the problems that the tifs and dwgs are. Any ideas on how to get rid of an extra Access header? That is what I was trying to do in my last post. Thanks.

 

[ThatRickGuy]

I only took a peek at a tif that I found. I'll have to look more closely on monday.

-Rick

----------------------

http://www.ringdev.com

 

[HitechUser]

jennuhw,

I have/had the same problem.

This tool (FREE) works great. But it does not support all file types. I was able to extract .tif but not images created with PhotoEditor 3.

http://software.reinhardt.nu/aie/

Now if I can figure out how to retrieve the PhotoEdit images.

Hope this helps..



Good Luck...

 

[HitechUser]

All,

As I stated previously I also need to extract many OLE objects. My previous post extracts .tif's fine, but the PhotoEditor file that is written is corrupted.

I found this link to extract a Word document.

http://support.microsoft.com/default.aspx?id=KB;EN-US;Q132003

Does anyone know how to change this for PhotoEditor 3?

Any help would be great.

Thanks in advance.

 

[jennuhw]

Sorry, I have been informed that a program called WinBatch will extract all of the documents. One of our vendors has a script that changes the bytes to binary, and gets rid of the excess header that Access puts on the documents.

 

[HitechUser]

All,

"Anotherlog for the fire"

Here is another link (competing site) and commment from a post.

http://www.experts-exchange.com/Databases/MS_Access/Q_20317096.html

Quote:

The code Nico refers to work quite well with a little changes that comes after you know very well how access work. Nico told me I have to erase the first 39 chars and also every time that you call the GetChunk method, convert this string to a byte array prior to write to the file.

Unquote:

Has anyone tried the above code? Any luck? The write blob function appears to be what is needed.

I look forward to any thoughts or comments.

Thanks.