Saving files from OLE Objects (Access) to disc

Question

I have an MS SQL Database from a customer with an Access Application.

This application stores files within this MS SQL database.

I tried to just get the bytes from the database and just save them to the disk. With some of the files this works and with some it does not. (images don't work, zips work, wordfiles are to open but word has to recover them)

I found out that Access saves the files within an OLE Object.

So i need to get out the Original Files from the OLE Object!

It seems that this is quite difficult.

I tried to find a .NET library which can seperate the files from the OLE Object.. found nothing...

Now i am trying to get the files out with Access...

It seems that i neet a getChunk Function to do that... Found this page with a WriteBlob Code... it is said that it would to what i need..

https://support.microsoft.com/en-us/help/210486/acc2000-reading--storing--and-writing-binary-large-objects-blobs

Now i can write Files to the Harddisc... but the files are still not able to open!

Something's wrong here...

My complete VBA Code is this:

 Option Compare Database


 Const BlockSize = 32768


 Sub xxx()


 Dim id As Integer
   Debug.Print "****************************************************"
   Debug.Print "****************************************************"
   Debug.Print "****************************************************"
   Debug.Print "****************************************************"

   Dim unnoetig As Variant

   Dim dok() As Byte
   Dim sql As String
   sql = "select top 1 idCaseDetail, idCase, Dokument from dbo_law_tbl_CaseHistory where idCaseDetail = ""1"""

   Dim rst As DAO.Recordset

   Set rst = CurrentDb.OpenRecordset(sql)

   If Not rst.EOF Then
     Do While Not rst.EOF
       Debug.Print "idcasehistory: " & rst.Fields(0)
       Debug.Print "idcase: " & rst.Fields(1)

       If Not IsNull(rst.Fields(2).Value) Then
         dok = rst.Fields(2)
         unnoetig = WriteBLOB(rst, "Dokument", "c:\temp\ole.doc")
       End If
       rst.MoveNext


     Loop
   End If

 End Sub



 '**************************************************************
 ' FUNCTION: WriteBLOB()
 '
 ' PURPOSE:
 '   Writes BLOB information stored in the specified table and field
 '   to the specified disk file.
 '
 ' PREREQUISITES:
 '   The specified table with the OLE object field containing the
 '   binary data must be opened in Visual Basic code and the correct
 '   record navigated to prior to calling the WriteBLOB() function.
 '
 ' ARGUMENTS:
 '   T           - The table object containing the binary information.
 '   sField      - The OLE object field in table T containing the
 '                 binary information to write.
 '   Destination - The path and filename to write the binary
 '                 information to.
 '
 ' RETURN:
 '   The number of bytes written to the destination file.
 '**************************************************************
 Function WriteBLOB(T As DAO.Recordset, sField As String, _
 Destination As String)
     Dim NumBlocks As Integer, DestFile As Integer, i As Integer
     Dim FileLength As Long, LeftOver As Long
     Dim FileData As String
     Dim RetVal As Variant

     On Error GoTo Err_WriteBLOB

     ' Get the size of the field.
     FileLength = T(sField).FieldSize()
     If FileLength = 0 Then
         WriteBLOB = 0
         Exit Function
     End If

     ' Calculate number of blocks to write and leftover bytes.
     NumBlocks = FileLength \ BlockSize
     LeftOver = FileLength Mod BlockSize

     ' Remove any existing destination file.
     DestFile = FreeFile
     Open Destination For Output As DestFile
     Close DestFile

     ' Open the destination file.
     Open Destination For Binary As DestFile

     ' SysCmd is used to manipulate the status bar meter.
     RetVal = SysCmd(acSysCmdInitMeter, _
     "Writing BLOB", FileLength / 1000)

     ' Write the leftover data to the output file.
     FileData = T(sField).GetChunk(0, LeftOver)
     Put DestFile, , FileData

     ' Update the status bar meter.
     RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)

     ' Write the remaining blocks of data to the output file.
     For i = 1 To NumBlocks
         ' Reads a chunk and writes it to output file.
         FileData = T(sField).GetChunk((i - 1) * BlockSize _
            + LeftOver, BlockSize)
         Put DestFile, , FileData

         RetVal = SysCmd(acSysCmdUpdateMeter, _
         ((i - 1) * BlockSize + LeftOver) / 1000)
     Next i

     ' Terminates function
     RetVal = SysCmd(acSysCmdRemoveMeter)
     Close DestFile
     WriteBLOB = FileLength
     Exit Function

 Err_WriteBLOB:
     WriteBLOB = -Err
     Exit Function

 End Function

Do you have any suggestions? Important to say is: It is an MS SQL Database... not an Access Database.. there are some tools which maybe could word with access-Databases.. but not mit ms sql

Is there a .NET way or an VBA way to save the files to disc?

If the bytes stored in this topic field are an OLEObject, then you can process that information to extract the original file. I presented a way to work with OLEObjects that represent a Bitmap Image in [this answer](https://stackoverflow.com/a/38938659/2592875) and it is possible to use the technique presented there to extract other file types as well. — TnTinMn, Jan 20 '18 at 16:06

Erik A · Answer 1 · 2018-01-22T10:31:28.463

4

An easy alternative to using DAO for saving OLE objects, is to use the ADODB.Stream object:

Public Sub SaveOLEObject(OleObjectField As Field, Filelocation As String)
    Dim adoStream As Object 'ADODB.Stream
    Set adoStream = CreateObject("ADODB.Stream")
    adoStream.Type = 1 'adTypeBinary
    adoStream.Open
    adoStream.Write OleObjectField.Value
    adoStream.SaveToFile Filelocation, adSaveCreateOverWrite
    adoStream.Close
End Sub

Call it:

SaveOLEObject(rst.Fields("Dokument"), "c:\temp\ole.doc")

Note that, of course, your documents might just be corrupt, and that might explain the problem.

If your objects are stored in SQL Server, I'd prefer directly opening an ADO recordset containing the binary data from SQL server over creating a linked table and opening a DAO recordset from the linked table.

edited Jan 22 '18 at 10:31

answered Jan 19 '18 at 17:59

Erik A

31,639
12
42
67

Hello! in the row "adoStream.Type = adTypeBinary" i get an 3001: Arguments are of wrong type Do you have any idea how to get rid of this? – Entity Jan 22 '18 at 09:56
Yes, you can replace it with `1`, or add a reference to ActiveX Data Objects to your database. See the current edit – Erik A Jan 22 '18 at 10:31
Hello Erik! Thanks for the answer... the code is now compiling an running. Unfortunately the file, written to the harddisc is still the same! So with the OLE-Container! But i need the files within the container! Thanks nevertheless! – Entity Jan 23 '18 at 14:45
An OLE container is not a simple BLOB. The code required is far more complex. You can try using Lebans' [OLEToDisk](http://www.lebans.com/oletodisk.htm) for containers. – Erik A Jan 23 '18 at 14:47

score 0 · Answer 2 · answered Aug 19 '20 at 06:24

In Access, create a corresponding Access Form with all relevant fields. Use the VBA code provided in the link and you should be able to export some of the most common file types in an automated fashion. Good luck. https://medium.com/@haggenso/export-ole-fields-in-microsoft-access-c67d535c958d

Saving files from OLE Objects (Access) to disc

2 Answers2

Linked