DK2014 DK2014 - 3 months ago 22
Vb.net Question

Save Image to Access Database as "Bitmap Image"

I am able to save Images to Access database table successfully using memory Stream. The Column in which I am saving the Images has datatype "OLEObject". When I open the table I see "Long binary data" value in this Image Column. Problem is that when I am creating any Report in this database, I am not able to see the image.

Whereas when I opened the Image file in MSPaint and Ctrl+A and then Ctrl+C to copy all the Graphics and then pasted it in the Access Database column, then it was pasted successfully. and Now shows value as "Bitmap image". When I double click on the image column value , it opens the image in MSPaint.And also works in Report. I would like to know how to make the image save directly as type "Bitmap image" ? I am using VB .Net.

Image added thru VB .Net

Manually pasted from MSPaint

Answer

The MS Access OLE field structure is not documented in any literature I could find.

The class AccessOLEBitmapConverter shown below is the result of a forensic hack based on an article referenced (How To Retrieve Bitmap from Access and Display It in Web Page ) in the source code of the System.Drawing.ImageConverter Class for the method GetBitmapStream.

Based on a bit of hacking I discovered that the field comprises two structures: 1) a header structure, and 2) an EmbeddedObject structure. After a bit more hacking, I discovered that the Presentation field of the EmbeddedObject structure can be replaced with a sequence of 12 bytes as long as the name field in the header structure is populated with a sequence of six bytes ({33, 0, 255, 255, 255, 255}). Please refer to the code for further information.

Public Class AccessOLEBitmapConverter
    Const nullByte As Byte = 0
    Const OLEVersion As Int32 = 1281
    Private Shared arrayInvalid As String = "Source array is invalid"

    Public Shared Function ToByteArray(sourceBitmap As Bitmap) As Byte()

        Dim ret As Byte() = Nothing
        Using ms As New IO.MemoryStream(5000)
            EmbeddedObjectHeader.Write(ms)
            EmbeddedObject.Write(ms, sourceBitmap)
            ret = ms.ToArray()
        End Using

        Return ret
    End Function

    Public Shared Function ToBitmap(bytes As Byte()) As Bitmap
        Dim ret As Bitmap = Nothing

        If bytes Is Nothing Then Throw New ArgumentNullException("Argument ""bytes"" cannot be null")
        If bytes.Length < 14 Then Throw New ArgumentOutOfRangeException(arrayInvalid)
        Using ms As New IO.MemoryStream(bytes, False),
                br As New IO.BinaryReader(ms, System.Text.Encoding.ASCII)
            Dim signature As Int16 = br.ReadInt16()
            If signature <> &H1C15 Then Throw New ArgumentOutOfRangeException(arrayInvalid)

            Dim headersize As Int16 = br.ReadInt16() ' 47
            If bytes.Length < headersize Then Throw New ArgumentOutOfRangeException(arrayInvalid)

            ms.Position = headersize

            ' Start ObjectHeader
            If ms.Position = bytes.Length Then Throw New ArgumentOutOfRangeException(arrayInvalid)
            Dim OLEVersion As UInt32 = br.ReadUInt32
            If ms.Position = bytes.Length Then Throw New ArgumentOutOfRangeException(arrayInvalid)
            Dim FormatID As UInt32 = br.ReadUInt32

            If ms.Position = bytes.Length Then Throw New ArgumentOutOfRangeException(arrayInvalid)
            Dim ClassName As String = LengthPrefixedAnsiString.Read(br)

            If ms.Position = bytes.Length Then Throw New ArgumentOutOfRangeException(arrayInvalid)
            Dim TopicName As String = LengthPrefixedAnsiString.Read(br)

            If ms.Position = bytes.Length Then Throw New ArgumentOutOfRangeException(arrayInvalid)
            Dim ItemName As String = LengthPrefixedAnsiString.Read(br)
            ' End ObjectHeader

            If ms.Position = bytes.Length Then Throw New ArgumentOutOfRangeException(arrayInvalid)
            Dim NativeDataSize As Int32 = br.ReadInt32

            If (ms.Position + NativeDataSize) > bytes.Length Then Throw New ArgumentOutOfRangeException(arrayInvalid)
            Dim NativeData As Byte() = br.ReadBytes(NativeDataSize)

            Dim msImage As New IO.MemoryStream(NativeData)

            ret = CType(Image.FromStream(msImage), Bitmap)
        End Using


        Return ret
    End Function

    Private Class EmbeddedObjectHeader
        ' ref: How To Retrieve Bitmap from Access and Display It in Web Page 
        ' https://support.microsoft.com/en-us/kb/175261

        Friend Shared Sub Write(ms As System.IO.MemoryStream)
            Const signature As Int16 = &H1C15
            Const headersize As Int16 = 47S
            Const objectType As Int16 = 2S
            Const nameLen As Int16 = 0S
            Const classLen As Int16 = 13S
            Const nameOffset As Int16 = 14S
            Const classOffset As Int16 = 20S
            Const classType As String = "Bitmap Image"
            Const hdrClassName As String = "Paint.Picture"

            Using bw As New IO.BinaryWriter(ms, System.Text.Encoding.ASCII, True)
                With bw
                    .Write(signature)
                    .Write(headersize)
                    .Write(objectType)
                    .Write(nameLen)
                    .Write(classLen)
                    .Write(nameOffset)
                    .Write(classOffset)

                    ' Even though this offset is declared as being for the 'name' field and
                    ' the 'name' field always has a zero length, these six bytes must be present
                    ' to allow the resultant byte array to be identified as a BitMap Image by Access
                    ms.Position = nameOffset
                    .Write(New Byte() {33, 0, 255, 255, 255, 255})

                    ms.Position = classOffset
                    .Write(classType.ToCharArray())
                    .Write(nullByte)
                    .Write(hdrClassName.ToCharArray())
                    .Write(nullByte)
                End With
            End Using
        End Sub
    End Class ' EmbeddedObjectHeader

    Private Class EmbeddedObject
        ' ref: https://msdn.microsoft.com/en-us/library/dd942053.aspx
        'Header (variable): This MUST be an ObjectHeader (section 2.2.4). 
        ' The FormatID field of the Header MUST be set to 0x00000002.

        'NativeDataSize (4 bytes): This MUST be set to the size of the NativeData field, in bytes.

        'NativeData (variable): This must be an array of bytes that contains the native data.

        'Presentation (variable): This MUST be a MetaFilePresentationObject (section 2.2.2.1), 
        ' a BitmapPresentationObject (section 2.2.2.2), a DIBPresentationObject (section 2.2.2.3), 
        ' a StandardClipboardFormatPresentationObject (section 2.2.3.2), or 
        ' a RegisteredClipboardFormatPresentationObject (section 2.2.3.3).

        Friend Shared Sub Write(ms As System.IO.Stream, sourceBitmap As Bitmap)

            Using bw As New IO.BinaryWriter(ms, System.Text.Encoding.ASCII, True)
                With bw
                    ObjectHeader.Write(ms)

                    ' Determine and write the NativeDataSize and NativeData fields
                    Using imgStream As New IO.MemoryStream(20000)
                        sourceBitmap.Save(imgStream, System.Drawing.Imaging.ImageFormat.Bmp)
                        sourceBitmap.Save("test.bmp", System.Drawing.Imaging.ImageFormat.Bmp)

                        Dim NativeDataSize As Int32 = CInt(imgStream.Length)
                        .Write(NativeDataSize)

                        .Write(imgStream.ToArray)
                    End Using

                    ' At this point the 'Presentation' variable should be written.
                    ' However, Bitmap files copied from Windows Explorer and pasted into
                    ' the MS Access OLE field have only 12 bytes written and this allows for
                    ' a much smaller size to be stored as the 'Presentation' variable appears to 
                    ' duplicate the NativeData field.  Adding the Bitmap via the 'Insert Object'
                    ' dialog creates a storage nearly twice that of this method.

                    ' The first 4 bytes correspond to the integer value for OLEVersion.
                    .Write(OLEVersion)
                    ' The next 4 bytes are always zero.
                    .Write(0I)
                    ' The next byte (position 8) appears variable and its value does not appear
                    ' to impact using the 'BitMap Image' in Access.  So write a zero.
                    .Write(nullByte)
                    ' The final three bytes appear to be constant {173, 5, 254}
                    .Write(New Byte() {173, 5, 254})
                    .Flush()
                End With
            End Using
        End Sub

    End Class 'EmbeddedObject

    Private Class ObjectHeader
        Friend Shared Sub Write(ms As System.IO.Stream)
            Const FormatID As Int32 = 2
            Const ClassName As String = "PBrush"
            Const TopicName As String = ""
            Const ItemName As String = ""

            Using bw As New IO.BinaryWriter(ms, System.Text.Encoding.ASCII, True)
                With bw
                    .Write(OLEVersion)
                    .Write(FormatID)
                    LengthPrefixedAnsiString.Write(bw, ClassName)
                    LengthPrefixedAnsiString.Write(bw, TopicName)
                    LengthPrefixedAnsiString.Write(bw, ItemName)
                End With
            End Using
        End Sub

    End Class ' ObjectHeader

    Private Class LengthPrefixedAnsiString
        ' ref : https://msdn.microsoft.com/en-us/library/dd942554.aspx
        ' This structure specifies a null-terminated American National Standards Institute (ANSI) character set string.
        ' Length (4 bytes): This MUST be set to the number of ANSI characters in the String field, 
        ' including the terminating null character. Length MUST be set to 0x00000000 to indicate an empty string.
        ' String (variable): This MUST be a null-terminated ANSI string. 

        Const nullChar As Byte = 0

        Friend Shared Function Read(br As IO.BinaryReader) As String
            Dim ret As String = String.Empty
            Dim length As Int32 = br.ReadInt32
            If length > 0 Then
                Dim chars As Char() = br.ReadChars(length)
                ret = New String(chars)
            End If
            Return ret
        End Function

        Friend Shared Sub Write(bw As IO.BinaryWriter, val As String)
            If val.Length = 0 Then
                bw.Write(0I)
            Else
                bw.Write(val.Length + 1)
                bw.Write(val.ToCharArray)
                bw.Write(nullChar)
            End If
        End Sub
    End Class 'LengthPrefixedAnsiString
End Class

Example usage:

' To obtain a Byte() to store a Bitmap to MS Access
Dim bm As Bitmap = CType(Image.FromFile("SomeBitmapFile.bmp"), Bitmap)
Dim bytesToStoreInAccess As Byte() = AccessOLEBitmapConverter.ToByteArray(bm)

' To retrieve a Bitmap from an Access Bitmap Image field.
Dim bytesFromBitmapImageField As Byte() ' set this equal to the field data
Dim bmRetrieved As Bitmap = AccessOLEBitmapConverter.ToBitmap(bytesFromBitmapImageField)

This code has been successfully tested using MS Access 2007 on a Windows 10 machine to store a Bitmap to an Access OLE field to yield a MS Access Bitmap Image that can be shown in an Access report.

Comments