none
Read Access Database Row Headers using C# RRS feed

  • Question

  • In MS Access Database - I have a column - DataType image

    In these columns, using VBA there are some office documents stored (xls, xlsx, doc, docx, pdf) - I found that Access Application adds the header automatically when you save the data using VBA; When you opens it - it detects automatically the File Extension and opens it with its respective application like Microsoft Excel/Word/PowerPoint.

    I would like to achieve the same using C#, I got this nice tools: Mime-Detectivehttps://github.com/Muraad/Mime-Detective

    As a POC, I had uploaded using FileUpload on WinForms and there it works. However when opening a file from the Access Database, it cannot find its extension.

    May be the header on the Access Database would be different or there are more offset ?

    Please help.

    Monday, December 18, 2017 3:03 PM

All replies

  • Sounds to me like you are talking about an "OLE Object" column type, where content is stored using the native application and OLE header information. It is possible to store content in this column type with or without these headers.

    .NET does not have native support for the OLE Object type, but you can stream content as binary data to/from this column type. Is this how you stored the data using the Windows Form app? If so, the lack of OLE headers would not enable Access to treat this as "OLE Object" content. Below is VBA code that would enable you to read/write the info if it has been stored as a binary stream:

    http://www.ammara.com/access_image_faq/read_write_blob.html

    GetChunk method:

    https://msdn.microsoft.com/en-us/library/office/ff822448.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, December 18, 2017 4:00 PM
  • Hi Paul, yes it is correct - the previous team who have developed the application using VB => MSACCESS have used OLE Object to store the content  - and it seems with header.

    Now there is a requirement to migrate these content (mostly office documents), using C# this time (WinForms or Web Based Technologies), the challenge is to get the exact File Extension.

    For test purpose, I have create a simple WinForm to Upload/Read the byte[] from a Test Database with same DataType using the Mime-Detective tools and it works perfectly.

    Using the same code, when I read the byte[] from the Access Database this time, I cannot be read.

    As you can see below the starting bytes doesn't look similar despite both are xlsx files.

    I am suspecting that OLE Object would add some extra Information in the header ?

    Currently for getting the FileType, the offset are set at 512 - is there any way to find the offset calculation from a byte[] object ?

    public readonly static FileType WORDX = new FileType(new byte?[0], 512, "docx", "application/vnd.openxmlformats-officedocument.wordprocessingml.document");
    
    public readonly static FileType EXCELX = new FileType(new byte?[0], 512, "xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    
    
    public readonly static FileType WORD = new FileType(new byte?[] { 0xEC, 0xA5, 0xC1, 0x00 }, 512, "doc", "application/msword");
            
    public readonly static FileType EXCEL = new FileType(new byte?[] { 0x09, 0x08, 0x10, 0x00, 0x00, 0x06, 0x05, 0x00 }, 512, "xls", "application/excel");

    Start Bytes from Access Database [xlsx]

    0x151C3A00020000001100150014002500FFFFFFFF4163726F62617420446F63756D656E74004163726F457863682E446F63756D656E742E3131000105000002000000150000004163726F457863682E446F63756D656E742E313100000000000000000000DE1700D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF0900060000000000000000000000180000000100000000000000001000000200000001000000FEFFFFFF000000000000000004000000BF000000C0000000C1000000C2000000C3000000C4000000C5000000C6000000C7000000C8000000C9000000CA000000CB000000CC000000CD000000CE000000CF000000D0000000D1000000D2000000D3000000D4000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

    Start Bytes from Test Sql Database [xlsx]

    0x504B03041400060008000000210034E1EA94ED0100002B0A0000130008025B436F6E74656E745F54797065735D2E786D6C20A2040228A00002000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

    Thanks.


    • Edited by fmourtaza Tuesday, December 19, 2017 9:00 AM
    Tuesday, December 19, 2017 8:55 AM
  • Another logic I am thinking is to extract the File Extension within Ms Access using VBA - could you help me with some good pointers ?
    Tuesday, December 19, 2017 1:07 PM
  • Another logic I am thinking is to extract the File Extension within Ms Access using VBA - could you help me with some good pointers ?
    Have you tried comparing the contents of the OLE Object field when adding a document through Access with that of the VB.NET code. I would suspect they are different, which is probably why Access cannot read the content. Can you post the VB.NET code so we can see how it was stored?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, December 19, 2017 2:18 PM
  • Here it is:

    Private Sub cmdOLE_Click()
    On Error Resume Next
        Me.Refresh
        If IsNull(Me.DokuID) Then Exit Sub
            newDocuID = Me.txtDokuID
            DoCmd.OpenForm "frmDokuOprate", , , "DokuID=" & Me.txtDokuID, , acDialog
            Me.Refresh
    End Sub
    
    
    Private Sub Form_Load()
    On Error Resume Next
        If IsNull(MyOLE) Then
            MyOLE.Action = acOLEInsertObjDlg
            DoCmd.Close acForm, Me.name
        Else
            MyOLE.Verb = acOLEVerbOpen   'Value=-2
            MyOLE.Action = acOLEActivate 'Value=7
        End If
    End Sub


    Is it possible/Is there any Function to find a description of all the OLE headers ?


    • Edited by fmourtaza Wednesday, December 20, 2017 12:21 PM
    Wednesday, December 20, 2017 11:57 AM
  • Hello F MMM,

    As far as I know, all files stored in OleObject field are stored in byte array format. So I think we need get the extension information from the byte array.

    However, Access does not provide any function to do this work and, according to my search result, it seems not possible. You could try to refer to below thread for more information.

    how get file information from its existing byte array

    Taking what is above-mentioned into account, I think it is not possible to get the file extension or other file properties from the object stored in Ole Object field.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 26, 2017 9:01 AM
  • Hello Terry,

    It is more complicated, have a look 

    http://www.tenouk.com/visualcplusmfc/visualcplusmfc27a.html 

    http://jvdveen.blogspot.de/2009/01/ole-and-accessing-files-embedded-in.html

    Regards.


    • Edited by fmourtaza Wednesday, December 27, 2017 9:15 AM
    Wednesday, December 27, 2017 9:13 AM
  • Hello Terry,

    It is more complicated, have a look 

    http://www.tenouk.com/visualcplusmfc/visualcplusmfc27a.html 

    http://jvdveen.blogspot.de/2009/01/ole-and-accessing-files-embedded-in.html

    Regards.



    The C# code will not be able to determine the file type from a document that was stored as OLE Object (with OLE header). It will only work if the file was stored as a native binary stream (without the OLE header). Attempting to strip the OLE header from the content is very difficult since it varies depending upon the file type. 

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, December 28, 2017 4:06 AM