TIP: Storing Images in Access

[ritchieroo]

While it is always preferrable not to store images in Access databases, there may be times when you might need to.

There seems to have been a bit of discussion about this topic recently so I thought I'd post my code for doing this.

Firstly, I should point out that I use BLOBs (Long Binary Data), and not OLE Objects. This means that a 50kB source file requires 50kB of storage (there will be a small overhead per image,
but nothing like what is needed to store images as OLE objects).

Of course, you lose some functionality: you can't launch the image by double-clicking on the field anymore, and changes can't be automatically linked to a source file (I never found a good use for any of this anyway). The important thing is that you can still see the image on a form (although a little bit of coding is needed).

Here is some example code that deals with loading a bitmap from a file, and saving data back to a file...

Option Explicit

Public Sub Test_FileToBLOB()
  Dim bBuff() As Byte
  Dim rs As Recordset
  
'An empty byte-array is passed to the LoadBufferFromFile function, which
'will be filled with binary data.  A DAO Recordset is opened for a table
'called TestBMP having an OLE object field called BMPImage.  The BMPImage
'field is filled with the contents of the buffer.

  LoadBufferFromFile "d:\test1.bmp", bBuff
    
  Set rs = CurrentDb.OpenRecordset("select BMPImage from TestBMP")
  With rs
    .AddNew
    !BMPImage.Value = bBuff
    .Update
    .Close
  End With

  Set rs = Nothing
End Sub

Public Sub Test_BLOBToFile()
  Dim bBuff() As Byte
  Dim rs As Recordset

'A DAO Recordset is opened for a table called TestBMP having an OLE object
'field called BMPImage. An empty byte-array is filled from the contents of
'the BMPImage field. The filled buffer is passed to the SaveBufferToFile
'function and saved.

  Set rs = CurrentDb.OpenRecordset("select BMPImage from TestBMP")
  bBuff = rs!BMPImage.Value
  
  SaveBufferToFile "d:\test2.bmp", bBuff
  
  Set rs = Nothing
End Sub

Public Sub LoadBufferFromFile(ByVal Filename As String, Buffer() As Byte)
  Dim hFile As Long
    
'The source file is opened to read binary data.  Buffer is sized to suit
'the number of bytes in the file.  The buffer is filled from the file and
'the file is closed.
    
  hFile = FreeFile
  Open Filename For Binary Access Read As #hFile
  
  ReDim Buffer(0 To LOF(hFile) - 1)
  Get #hFile, , Buffer
  
  Close #hFile
End Sub

Public Sub SaveBufferToFile(ByVal Filename As String, Buffer() As Byte)
  Dim hFile As Long

'The new file is opened to write binary data (the file is also locked to stop
'other read/write processes.  The contents of the byte-buffer is written to
'the file and the file closed.

  hFile = FreeFile
  Open Filename For Binary Access Write Lock Read Write As #hFile
  Put #hFile, , Buffer
  
  Close #hFile
End Sub

In order to display the image on a form I set up a form called 'Form1' with an image control 'Image1'. The forms RecordSource property is set to the 'TestBMP' table.

Then I added this code to the Form_Current event...

Private Sub Form_Current()
  Dim bBuff() As Byte
  Dim rs As Recordset
  
  Set rs = CurrentDb.OpenRecordset("select BMPImage from TestBMP")
  bBuff = rs!BMPImage.Value
  rs.Close
  
  SaveBufferToFile "d:\tmpfile.bin", bBuff
  Me.Painting = False
  Me.Image1.Picture = "d:\tmpfile.bin"
  Me.Painting = True
  
  Set rs = Nothing
End Sub

As the user navigates between records, the code saves the file to a temporary file and then loads that file into the image control.

 

[yeatmanj]

Can this be used for jpg files instead of bmp, by simply replacing all occurences of bmp with jpg? Or is there something more detailed that needs to be done?

Thanks,
yeatmanj

 

[ritchieroo]

Yes

In fact the basic functions can be used to read/write any files to BLOBs in the database... could be bitmaps, jpegs, word documents etc.

Because the files are BLOBs and not OLE objects they can't be launched directly. You need to write the file temporarily to the hard-drive before using it.

There's no reason why my example shouldn't work with jpegs as the image control supports them.

 

[Mario]

This is exactly what I was looking for. Thanks a lot.

 

[zmrcic]

I get error message on line
'Me.Image1.Picture = "d:\tmpfile.bin" '
....bin format is not suported....