Categories
blob images MS Access

Reading Images from BLOBS

My first post discussed how to store images into a BLOB (OLEObject) field in a access database. After several months I have decided it time to show how to read those out.

Firstly just remind you that the images are stored as is in the database table. You use a OLE Object field type, even though you are not stored a OLE Object in there.

What we use is this code which utilises some definition from the Edanamo library.


' Global Memory Flags
Const GMEM_MOVEABLE = &H2
Const GMEM_ZEROINIT = &H40
Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)

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 Function Array2Picture(aBytes() As Byte) As StdPicture
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 Array2Picture = New StdPicture ' Create a new empty picture object
Set oIPS = Array2Picture ' Get the IPersistStream interface
lSize = UBound(aBytes) - LBound(aBytes) + 1 ' Calculate the array size
hGlobal = GlobalAlloc(GHND, lSize + Len(Hdr)) ' Allocate global memory
If hGlobal Then
lPtr = GlobalLock(hGlobal) ' Get a pointer to the memory
Hdr.Magic = PictureID ' Initialize the header
Hdr.Size = lSize
CopyMemory ByVal lPtr, Hdr, Len(Hdr) ' Write the header
CopyMemory ByVal lPtr + Len(Hdr), aBytes(0), lSize ' Copy the byte array to the global memory
GlobalUnlock hGlobal ' Release the pointer
Set oStream = CreateStreamOnHGlobal(hGlobal, True) ' Create a IStream object with the global memory
oIPS.Load oStream ' Load the picture from the stream
Set oStream = Nothing ' Release the IStream object
End If
End Function

Notice that there is no usage of an intermediate file, as in other solutions. This looks more complex, and it is, but it is soo much faster.

Here is a sample code to read this from the table


dim ImgData as Variant
dim BLOB() as Byte
Dim oPic as StdPicture

set rs = CurrentDB.OpenRecordset("select img from image where id=100")

ImgData = rs(0).FieldSize 'the size of the bitmap
BLOB = rs(0).GetChunk(0,ImgData)
set oPic = Array2Picture(BLOB())

Once you has the StdPicture object you can then put into a ImageList or form control.

And that’s all there is to it. In a future post I will show how GDI+ can be used to good effect.

Categories
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


Const GMEM_MOVEABLE = &H2
Const GMEM_ZEROINIT = &H40
Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)

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.AddNew
oadoRS(0) = 100 'a sample image ID
oadoRS(1).AppendChunk bytedata
oadoRS.Update
oadoRS.Close

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!

Categories
Access Edanamo icons images MS Access olelib.tlb

Putting an icon on Access application

Access can put an icon onto the top left corner of a database which you build. This is done through the Tools | Startup dialog. In there you specify the path to the icon file. This can be a pain if you are distributing the database to multiple users as their path could (it most definitely will) vary from user to user.

One way to get around this issue to to modify the icon path in the startup macro autoexec. In this macro add a RunCode item and set it to run a function eg: setIconPath(). In this function you would add something like this:


Const Main_Icon_name As String = "myicon"

Public Function SetStartupOptions(propertyname As String, _
propertytype As Variant, propertyvalue As Variant) _
As Boolean
Dim dbs As Object
Dim prp As Object
Set dbs = Application.CurrentDb
On Error Resume Next
dbs.Properties(propertyname) = propertyvalue
If Err.Number = 3270 Then
Set prp = dbs.CreateProperty(propertyname, _
propertytype, propertyvalue)
dbs.Properties.Append prp
Application.RefreshTitleBar
Else
SetStartupOptions = False
End If
Set dbs = Nothing
Set prp = Nothing
End Function

Public Function setIconPath()
Dim fs As New FileSystemObject
Dim sNewFilePath As String


sNewFilePath = fs.BuildPath(CurrentProject.PATH, Main_Icon_name & ".ico")

SetStartupOptions "AppIcon", dbText, sNewFilePath
SetStartupOptions "UseAppIconForFrmRpt", dbBoolean, vbTrue
Application.RefreshTitleBar
End Function

This function obtains the path where Access is running then adds on the icon name and sets the options to enable the icon for reports and forms. Lastly it refreshes the application window so the icon will be visible. Simple enough.

But what if you want to distribute the application without its icon file, or the icon will be different depending on security, if you have a really advanced applications? Then this code can be modified to select the icon as necessary. (credit http://www.access-programmers.co.uk/forums/).


'place this sub in each forms "Load" event
Private Sub Form_Load()

SetFormIcon Me.hWnd, Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & “\myicon.ico"
‘if the icon file is stored in the same directory as the db
‘or
SetFormIcon Me.hWnd, "C:\Icons\Icon1.ico" 'Location of icon file

End Sub

‘copy below code in a new public module


Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

Private Const WM_SETICON = &H80
Private Const IMAGE_ICON = 1
Private Const LR_LOADFROMFILE = &H10
Private Const SM_CXSMICON As Long = 49
Private Const SM_CYSMICON As Long = 50

Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As Long, ByVal lpsz As String, ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, ByVal un2 As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, LParam As Any) As Long

Public Function SetFormIcon(hWnd As Long, strIconPath As String) As Boolean
Dim lIcon As Long
Dim lResult As Long
Dim X As Long, Y As Long

X = GetSystemMetrics(SM_CXSMICON)
Y = GetSystemMetrics(SM_CYSMICON)
lIcon = LoadImage(0, strIconPath, 1, X, Y, LR_LOADFROMFILE)
lResult = SendMessage(hWnd, WM_SETICON, 0, ByVal lIcon)
End Function

You can even get fancier than this by storing the icons in the database and extracting them our
when you need to. How do you that you ask? You’ll need a couple of bits from the internet, but its all pretty easy.

First download Eduardo Morcillio’s OLE Lib TLB file from here http://www.mvps.org/emorcillo/en/code/vb6/index.shtml which is a popular library to play some Windows features. This will need to installed using the regtlib.exe utility. If you are lucky this will already be on your machine.

To extract the file out from the table code similar to this is needed:


Const GMEM_MOVEABLE = &H2
Const GMEM_ZEROINIT = &H40
Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)

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 Function Array2Picture(aBytes() As Byte) As StdPicture
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 Array2Picture = New StdPicture ' Create a new empty picture object
Set oIPS = Array2Picture ' Get the IPersistStream interface
lSize = UBound(aBytes) - LBound(aBytes) + 1 ' Calculate the array size
hGlobal = GlobalAlloc(GHND, lSize + Len(Hdr)) ' Allocate global memory
If hGlobal Then
lPtr = GlobalLock(hGlobal) ' Get a pointer to the memory
Hdr.Magic = PictureID ' Initialize the header
Hdr.Size = lSize
CopyMemory ByVal lPtr, Hdr, Len(Hdr) ' Write the header
CopyMemory ByVal lPtr + Len(Hdr), aBytes(0), lSize ' Copy the byte array to the global memory
GlobalUnlock hGlobal ' Release the pointer
Set oStream = CreateStreamOnHGlobal(hGlobal, True) ' Create a IStream object with the global memory
oIPS.Load oStream ' Load the picture from the stream
Set oStream = Nothing ' Release the IStream object
End If
End Function

Public Sub Get_Icon_into_File(n_id As Integer, sfile_base As String)
Dim ADO_RS As New ADODB.Recordset
Dim oPicture As New StdPicture
Dim bytedata() As Byte
Dim vvalue As Variant
Dim chunk As Long

ADO_RS.Open "select IMG_BLOB from ICONS where ID=" & n_id, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
ADO_RS.MoveFirst
vvalue = ADO_RS(0).Value
chunk = LenB(vvalue)
bytedata() = ADO_RS(0).GetChunk(chunk)
Set oPicture = Array2Picture(bytedata)

SavePicture oPicture, CurrentProject.PATH & "\" & sfile_base & ".ico"

End Sub

Whew! So what’s going on here. First we read the data out of the ICONS table in the IMG_BLOB column which is a OLEObject type. You notice that we are not stored a OLE object in it however. This saves us a lot of space. We then have to convert this to a picture object (Array2Picture) and then save it to the file.

In the next blog I’ll get into how we get the icons into the table in the first place!