ado appendchunk images MS Access

Getting images and icons into a Access column

Last post I showed how to extract icons out of a table column. Sorting data in this way saves a lot of room as compared to storing a OLE object wrapping the image. But how do you get the image into the column in the first place?

That’s what I’m gonna show today.

First make sure you have Edwardo Morcillio’s essential olelib.tlb file. His site contains many useful tools and code. You might also find the ImageLoad with GDI+ useful.

First to load an image from the file you’ll need to do something like this:

Dim oPicture As New StdPicture

Set oPicture = LoadPicture(sfilePath) 'the path to the image file here (.ico, .bmp)

Now you have the image loaded you need to convert it into a stream of bytes to put into your column.

Dim bytedata() As Byte

Picture2Array oPicture, bytedata

Where Picture2Array is defined as


Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)

Const PictureID = &H746C&

Private Type PictureHeader
Magic As Long
Size As Long
End Type

Public Sub Picture2Array(ByVal oObj As StdPicture, aBytes() As Byte)
Dim oIPS As IPersistStream
Dim oStream As IStream
Dim hGlobal As Long
Dim lPtr As Long
Dim lSize As Long
Dim Hdr As PictureHeader

Set oIPS = oObj ' Get the IPersistStream interface
Set oStream = CreateStreamOnHGlobal(0, True) ' Create a IStream object on global memory
oIPS.Save oStream, True ' Save the picture in the stream
hGlobal = GetHGlobalFromStream(oStream) ' Get the global memory handle from the stream
lSize = GlobalSize(hGlobal) ' Get the memory size
lPtr = GlobalLock(hGlobal) ' Get a pointer to the memory
If lPtr Then
lSize = lSize - Len(Hdr)
ReDim aBytes(0 To lSize - 1)
CopyMemory aBytes(0), ByVal lPtr + Len(Hdr), lSize ' Copy the data to the array
End If
GlobalUnlock hGlobal ' Release the pointer
Set oStream = Nothing ' Release the IStream object
End Sub

That’s got the image in memory in a byte array. Now we need to put this into a OLEObject column in the database. Note that we are NOT putting a OLE object into that column. But Access won’t care, as long as we don’t put it on a form and try to access it that way.

The only way to put the byte array into the column is via the ADO recordset AppendChunk method. Because I am dealing with small images here I just put it in using a single AppendChunk call. If you’re dealing with larger images, say anything over 64K, you may want to break it up into 64K chunks. Look at How To Read and Write BLOBs Using GetChunk and AppendChunk at the Microsoft support site for more information on that.

For me it was a simple case of;

Dim oadoRS as New ADODB.Recordset

oadoRS.Open "select * from ImagesTable", CurrentProject.Connection
oadoRS(0) = 100 'a sample image ID
oadoRS(1).AppendChunk bytedata

There you have it very simple. This proves much easier to handle than if you had to supply the icon and image files and install them to the user’s machine. And its much faster!