Image BLOBs Please Help!

[Mikael]

I am using Access 2003. I would like to use BLOB to display an image on a form. I have referred to the kb article: http://support.microsoft.com/default...EN-US;Q103257&

But the code is not compiling. Could someone give me some help? I haven't found any references for helping with BLOB.

[MMcCarthy]

I am using Access 2003. I would like to use BLOB to display an image on a form. I have referred to the kb article: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q103257&

But the code is not compiling. Could someone give me some help? I haven't found any references for helping with BLOB.

Hi Mikael

I know we have a couple of experts with knowledge on this I'm just posting here to move this back to the top of the list.

[PEB]

Where was the error?

'**************************************************************
      ' FUNCTION: ReadBLOB()
      '
      ' PURPOSE:
      '   Reads a BLOB from a disk file and stores the contents in the
      '   specified table and field.
      '
      ' PREREQUISITES:
      '   The specified table with the OLE object field to contain the
      '   binary data must be opened in Visual Basic code (Access Basic
      '   code in Microsoft Access 2.0 and earlier) and the correct record
      '   navigated to prior to calling the ReadBLOB() function.
      '
      ' ARGUMENTS:
      '   Source - The path and filename of the binary information
      '            to be read and stored.
      '   T      - The table object to store the data in.
      '   Field  - The OLE object field in table T to store the data in.
      '
      ' RETURN:
      '   The number of bytes read from the Source file.
      '**************************************************************
      Function ReadBLOB(Source As String, T As Recordset, _
      sField As String)
          Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
          Dim FileLength As Long, LeftOver As Long
          Dim FileData As String
          Dim RetVal As Variant
 
          On Error GoTo Err_ReadBLOB
 
          ' Open the source file.
          SourceFile = FreeFile
          Open Source For Binary Access Read As SourceFile
 
          ' Get the length of the file.
          FileLength = LOF(SourceFile)
          If FileLength = 0 Then
              ReadBLOB = 0
              Exit Function
          End If
 
          ' Calculate the number of blocks to read and leftover bytes.
          NumBlocks = FileLength \ BlockSize
          LeftOver = FileLength Mod BlockSize
 
          ' SysCmd is used to manipulate status bar meter.
          RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
                   FileLength \ 1000)
 
          ' Put the record in edit mode.
          T.Edit
 
          ' Read the leftover data, writing it to the table.
          FileData = String$(LeftOver, 32)
          Get SourceFile, , FileData
          T(sField).AppendChunk (FileData)
 
          RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
 
          ' Read the remaining blocks of data, writing them to the table.
          FileData = String$(BlockSize, 32)
          For i = 1 To NumBlocks
              Get SourceFile, , FileData
              T(sField).AppendChunk (FileData)
 
              RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i / 1000)
          Next i
 
          ' Update the record and terminate function.
          T.Update
          RetVal = SysCmd(acSysCmdRemoveMeter)
          Close SourceFile
          ReadBLOB = FileLength
          Exit Function
 
      Err_ReadBLOB:
          ReadBLOB = -Err
          Exit Function
 
      End Function
 
      '**************************************************************
      ' FUNCTION: WriteBLOB()
      '
      ' PURPOSE:
      '   Writes BLOB information stored in the specified table and field
      '   to the specified disk file.
      '
      ' PREREQUISITES:
      '   The specified table with the OLE object field containing the
      '   binary data must be opened in Visual Basic code (Access Basic
      '   code in Microsoft Access 2.0 or earlier) and the correct
      '   record navigated to prior to calling the WriteBLOB() function.
      '
      ' ARGUMENTS:
      '   T           - The table object containing the binary information.
      '   sField      - The OLE object field in table T containing the
      '                 binary information to write.
      '   Destination - The path and filename to write the binary
      '                 information to.
      '
      ' RETURN:
      '   The number of bytes written to the destination file.
      '**************************************************************
      Function WriteBLOB(T As Recordset, sField As String, _
      Destination As String)
          Dim NumBlocks As Integer, DestFile As Integer, i As Integer
          Dim FileLength As Long, LeftOver As Long
          Dim FileData As String
          Dim RetVal As Variant
 
          On Error GoTo Err_WriteBLOB
 
          ' Get the size of the field.
          FileLength = T(sField).FieldSize()
          If FileLength = 0 Then
              WriteBLOB = 0
              Exit Function
          End If
 
          ' Calculate number of blocks to write and leftover bytes.
          NumBlocks = FileLength \ BlockSize
          LeftOver = FileLength Mod BlockSize
 
          ' Remove any existing destination file.
          DestFile = FreeFile
          Open Destination For Output As DestFile
          Close DestFile
 
          ' Open the destination file.
          Open Destination For Binary As DestFile
 
          ' SysCmd is used to manipulate the status bar meter.
          RetVal = SysCmd(acSysCmdInitMeter, _
          "Writing BLOB", FileLength / 1000)
 
          ' Write the leftover data to the output file.
          FileData = T(sField).GetChunk(0, LeftOver)
          Put DestFile, , FileData
 
          ' Update the status bar meter.
          RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
 
          ' Write the remaining blocks of data to the output file.
          For i = 1 To NumBlocks
              ' Reads a chunk and writes it to output file.
              FileData = T(sField).GetChunk((i - 1) * BlockSize _
                 + LeftOver, BlockSize)
              Put DestFile, , FileData
 
              RetVal = SysCmd(acSysCmdUpdateMeter, _
              ((i - 1) * BlockSize + LeftOver) / 1000)
          Next i
 
          ' Terminates function
          RetVal = SysCmd(acSysCmdRemoveMeter)
          Close DestFile
          WriteBLOB = FileLength
          Exit Function
 
      Err_WriteBLOB:
          WriteBLOB = -Err
          Exit Function
 
      End Function
 
      '**************************************************************
      ' SUB: CopyFile
      '
      ' PURPOSE:
      '   Demonstrates how to use ReadBLOB() and WriteBLOB().
      '
      ' PREREQUISITES:
      '   A table called BLOB that contains an OLE Object field called
      '   Blob.
      '
      ' ARGUMENTS:
      '   Source - The path and filename of the information to copy.
      '   Destination - The path and filename of the file to write
      '                 the binary information to.
      '
      ' EXAMPLE:
      '   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
      '**************************************************************
      Sub CopyFile(Source As String, Destination As String)
          Dim BytesRead As Variant, BytesWritten As Variant
          Dim Msg As String
          Dim db As Database
          Dim T As Recordset
 
          ' Open the BLOB table.
          Set db = CurrentDb()
          Set T = db.OpenRecordset("BLOB", dbOpenTable)
 
          ' Create a new record and move to it.
          T.AddNew
          T.Update
          T.MoveLast
 
          BytesRead = ReadBLOB(Source, T, "Blob")
 
          Msg = "Finished reading """ & Source & """"
          Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
          MsgBox Msg, 64, "Copy File"
 
          BytesWritten = WriteBLOB(T, "Blob", Destination)
 
          Msg = "Finished writing """ & Destination & """"
          Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
          MsgBox Msg, 64, "Copy File"
      End Sub
 

[Mikael]

The error is this line:

Set T = db.OpenRecordset("BLOB", dbOpenTable)

I tried using the constants on top, and replacing dbOpenTable with what its supposed to actual be, but still no luck.

[PEB]

What is the name of the table that you should create for the blobs?
If different than Blob you need to change there!

[Mikael]

I just recreated the incident. I followed the article exactly, and then when I type the:

CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"

I get an error (type mismatch) on this line:

Set T = db.OpenRecordset("BLOB", dbOpenTable)

Is Access 2003, 2.0? I tried uncommenting the lines the initializations at the top, incase it is, but the same error occurs. My table is named BLOB, my module is named BLOB, and i took out all the underscores. Not sure whats wrong.

[PEB]

Ok in this line change: "BLOB"
to
"SELECT * FROM BLOB;"

[PEB]

Remove DBOPENTABLE!

[Mikael]

Remove DBOPENTABLE!

What do you mean here? Remove it from where, the constants above?

[PEB]

Test the line like this:

Set T = db.OpenRecordset("SELECT * FROM BLOB;")

[Mikael]

Error, Type Mismatch, same line.

[PEB]

Ok I will test this code on my computer :) In few minutes i'll tell you what are the results

[Mikael]

Thank you so much, I have gotten no help on this subject for weeks!

[PEB]

Interesting in my case there isn't problems....

Have you compiled your modules before running the sub?

This is from Debug - > Compile

[Mikael]

Never tried. I changed the code back to the original article, and hit debug -> compile test. Now I get a method or data member not found compile error on this line:

T.Edit

[PEB]

Are you sure that you have those lines in your sub:


Dim db As Database
Dim T As Recordset

Is this an mdb file or Adp what you use?

[PEB]

Do you have those constants?

Option Explicit
Const BlockSize = 32768

[Mikael]

this is an mdb. I'm just not sure what is going on. I am doing exactly what the article says. Do you have Access 2003?

[PEB]

See if you don't have other global variables in your system named T

Maybe in others modules!

This can do this kind of conflicts!

[PEB]

Yeap this is just on which i've tested it! It works well here!

this is an mdb. I'm just not sure what is going on. I am doing exactly what the article says. Do you have Access 2003?

[PEB]

Try to insert this code in empty mdb file just created...

With only table BLOB and this module...

[MMcCarthy]

Based on the code it seems to be using DAO for the recordsets. Check the following:

Change

Dim T As Recordset

to

Dim T As DAO.Recordset

and

Check that there is a version of the Microsoft DAO library selected in your references list.

[Mikael]

Hello all,

I apologize for my extremely late response. Thank you for all the help, but its still not working. I tried setting T as DAO.Recordset, but now its complaining about:

FileLength = T(sField).FieldSize()

Also, I created a blank mdb and put in just the BLOB stuff, and it seemed to work. Whats up with that? I noticed when I compiled the new database it says Compile: db2.mdb (the name of the database), but when I compile the database I'm working in, it says Compile TEST. This is not the name of anything in my database.

[NeoPa]

Hello all,

I apologize for my extremely late response. Thank you for all the help, but its still not working. I tried setting T as DAO.Recordset, but now its complaining about:

FileLength = T(sField).FieldSize()

Also, I created a blank mdb and put in just the BLOB stuff, and it seemed to work. Whats up with that? I noticed when I compiled the new database it says Compile: db2.mdb (the name of the database), but when I compile the database I'm working in, it says Compile TEST. This is not the name of anything in my database.

The Compile command changes to reflect the name of your project.
If you hit Ctrl-R to open or switch to your Project Window and select the very top (root) of the tree, you will see the project name. It will very likely be called Test.

[Mikael]

The Compile command changes to reflect the name of your project.
If you hit Ctrl-R to open or switch to your Project Window and select the very top (root) of the tree, you will see the project name. It will very likely be called Test.

You are correct. Thank you. I must have went with a test copy i was working with, and never realized it.

But back to the matter at hand. I created a completely blank database, and the original kb article works fine. BUT, my current db will not work. The error is still the most recent.

[Mikael]

Just wanted to move this article back to the top. If nobody has a solution, how easily can I create a new database, and import all the tables, forms, etc, from my old database?

[NeoPa]

Just wanted to move this article back to the top. If nobody has a solution, how easily can I create a new database, and import all the tables, forms, etc, from my old database?

Using File / Get External Data / Import... you can get all the main objects across quite easily (including Relationships).
Some modules will not copy across this way but that can be done by copy/pasting the code in text form.
Don't forget to duplicate the References too in the VBA window.
Lastly, check Database options and Startup data is duplicated manually.
Guess what - I've had to do that before ;).

[Mikael]

How could I appropriately use the kb article:

http://support.microsoft.com/defaul...;EN-US;Q103257&

I would like to have a field that pulls in jpeg pictures.

[G04]

Hi there,


I also try to save OLE objects in a table and encountered exactly the same errors as Mickael !

In an empty db the KB code works fine.
In the db I am working on was a reference to both DAO 3.6 and also to ADO.
I removed the ADO reference and it worked (I only use DAO).

The error "Type mismatch error 13" lies in the variable T, because of the missing DAO reference or not correctly declaration of variables.

Instead of :

Dim db as database
Dim T as recordset
 

Use:

Dim db as DAO.database
Dim T as DAO.Recordset
 

For saving images, I intend to use a separate database and link the table to the main database for storage purposes. A good idea?

Brgds,

[NeoPa]

For saving images, I intend to use a separate database and link the table to the main database for storage purposes. A good idea?

The DAO point is a good one, but already covered in post #22 I think.
The last is a good idea to my mind. I'm no image expert, but if I had to store images in a database I'd definitely try to store them separately from the Front-End db.

[Eddy Jawed]

When reading/writing blobs into tables there's much simpler code:

Public Sub setBLOB(RS As ADODB.Recordset, Field As String, Source As String)
 
'' Places file into database
'setBLOB myRecordSet, "FileField", "c:\FileToAdd.rtf"
 
    Dim fileBytes() As Byte
    Dim intFileHandle As Integer
 
    intFileHandle = FreeFile
 
    Open Source For Binary As intFileHandle
        fileBytes = InputB(LOF(intFileHandle) - 1, intFileHandle)
        RS(Field).AppendChunk fileBytes
    Close intFileHandle
End Sub
 
 
Public Sub getBLOB(RS As ADODB.Recordset, Field As String, Des As String)
 
'' Save file to disk
'getBLOB myRecordSet, "FileField", "c:\FileToSaveto.rtf"
 
    Dim lngFieldSize As Long
    Dim fileBytes() As Byte
    Dim intFileHandle As Integer
 
    intFileHandle = FreeFile
 
    lngFieldSize = RS(Field).ActualSize
    If lngFieldSize > 0 Then
        fileBytes = RS(Field).GetChunk(lngFieldSize)
        Open Des For Binary As intFileHandle
            Put intFileHandle, , fileBytes
        Close intFileHandle
    End If
End Sub
 
 
 
Sub TestBlob()
 
Dim RS As New ADODB.Recordset
 
RS.Open "SELECT * FROM tblBlob", CurrentProject.Connection, adOpenStatic, adLockOptimistic
 
RS.AddNew
setBLOB RS, "BlobFileField", filePath & "TestIn.rtf"
RS.Update
RS.Close
 
getBLOB RS, "BlobFileField", FilePath & "testOut.rtf"
 
End Sub