How-to: Extract Long Raw binary data to a text file.

The VBA routine below can be adapted to extract RAW binary data from an attached table e.g. Oracle Long Raw to a Text File (one file per record).

If the binary data contains a photo image or a Microsoft Office document or whatever, then you will also need to set an appropriate file extension.

Public Sub ExtractLRaw()
Dim strSQL As Variant
Dim ByteData() As Byte 'This will hold the document file.
Dim DestFileNum As Integer
Dim strDiskFile As String
Dim strStaffID As String
Dim db As Database
Dim rst As Recordset
Dim strFilename As String

On Error Resume Next

strSQL = "SELECT STAFF_ID, RAW_DOC, AUTHOR, FILENAME
FROM main_table WHERE (STAFF_ID Is Not Null)
ORDER BY STAFF_ID;"

' RAW_DOC is a long Raw column containing a document file.

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

'Check the recordset contains >0 rows
If Not (rst.EOF And rst.BOF) Then
    rst.MoveFirst
    Do Until rst.EOF = True
        strFilename = rst("FILENAME")
        strStaffID = Nz(rst("STAFF_ID"), 0)
        If strStaffID = 0 Then GoTo err_nonum
        
        ' Create destination folder
        MkDir "E:\export\" & strStaffID

        ' Adjust the file extension as needed (.doc, .pdf, .jpg, .mp3 etc)
        strDiskFile = "E:\export\" & strStaffID & "\" & strFilename & "PDF.DOCX"

        ' Delete the target if it already exists
        If Len(Dir$(strDiskFile)) > 0 Then Kill strDiskFile
        
        'open file
        DestFileNum = FreeFile
        Open strDiskFile For Binary As DestFileNum
        ByteData() = rst("RAW_DOC")
        
        ' Save file
        Put DestFileNum, , ByteData()
        Close DestFileNum
        
        'Move to the next record.
        rst.MoveNext
    Loop
    rst.Close
End If
End Sub

"A man, to read, must read alone. He may make extracts, he may work at books in company; but to read, to absorb, he must be solitary” ~ Richard Jefferies

Related

DAO and ADO - Database Access via VBA.