By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,053 Members | 1,595 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,053 IT Pros & Developers. It's quick & easy.

Image BLOBs Please Help!

P: 16
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.
Nov 16 '06 #1
Share this Question
Share on Google+
30 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 17 '06 #2

PEB
Expert 100+
P: 1,418
PEB
Where was the error?

Expand|Select|Wrap|Line Numbers
  1. '**************************************************************
  2.       ' FUNCTION: ReadBLOB()
  3.       '
  4.       ' PURPOSE:
  5.       '   Reads a BLOB from a disk file and stores the contents in the
  6.       '   specified table and field.
  7.       '
  8.       ' PREREQUISITES:
  9.       '   The specified table with the OLE object field to contain the
  10.       '   binary data must be opened in Visual Basic code (Access Basic
  11.       '   code in Microsoft Access 2.0 and earlier) and the correct record
  12.       '   navigated to prior to calling the ReadBLOB() function.
  13.       '
  14.       ' ARGUMENTS:
  15.       '   Source - The path and filename of the binary information
  16.       '            to be read and stored.
  17.       '   T      - The table object to store the data in.
  18.       '   Field  - The OLE object field in table T to store the data in.
  19.       '
  20.       ' RETURN:
  21.       '   The number of bytes read from the Source file.
  22.       '**************************************************************
  23.       Function ReadBLOB(Source As String, T As Recordset, _
  24.       sField As String)
  25.           Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
  26.           Dim FileLength As Long, LeftOver As Long
  27.           Dim FileData As String
  28.           Dim RetVal As Variant
  29.  
  30.           On Error GoTo Err_ReadBLOB
  31.  
  32.           ' Open the source file.
  33.           SourceFile = FreeFile
  34.           Open Source For Binary Access Read As SourceFile
  35.  
  36.           ' Get the length of the file.
  37.           FileLength = LOF(SourceFile)
  38.           If FileLength = 0 Then
  39.               ReadBLOB = 0
  40.               Exit Function
  41.           End If
  42.  
  43.           ' Calculate the number of blocks to read and leftover bytes.
  44.           NumBlocks = FileLength \ BlockSize
  45.           LeftOver = FileLength Mod BlockSize
  46.  
  47.           ' SysCmd is used to manipulate status bar meter.
  48.           RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
  49.                    FileLength \ 1000)
  50.  
  51.           ' Put the record in edit mode.
  52.           T.Edit
  53.  
  54.           ' Read the leftover data, writing it to the table.
  55.           FileData = String$(LeftOver, 32)
  56.           Get SourceFile, , FileData
  57.           T(sField).AppendChunk (FileData)
  58.  
  59.           RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
  60.  
  61.           ' Read the remaining blocks of data, writing them to the table.
  62.           FileData = String$(BlockSize, 32)
  63.           For i = 1 To NumBlocks
  64.               Get SourceFile, , FileData
  65.               T(sField).AppendChunk (FileData)
  66.  
  67.               RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i / 1000)
  68.           Next i
  69.  
  70.           ' Update the record and terminate function.
  71.           T.Update
  72.           RetVal = SysCmd(acSysCmdRemoveMeter)
  73.           Close SourceFile
  74.           ReadBLOB = FileLength
  75.           Exit Function
  76.  
  77.       Err_ReadBLOB:
  78.           ReadBLOB = -Err
  79.           Exit Function
  80.  
  81.       End Function
  82.  
  83.       '**************************************************************
  84.       ' FUNCTION: WriteBLOB()
  85.       '
  86.       ' PURPOSE:
  87.       '   Writes BLOB information stored in the specified table and field
  88.       '   to the specified disk file.
  89.       '
  90.       ' PREREQUISITES:
  91.       '   The specified table with the OLE object field containing the
  92.       '   binary data must be opened in Visual Basic code (Access Basic
  93.       '   code in Microsoft Access 2.0 or earlier) and the correct
  94.       '   record navigated to prior to calling the WriteBLOB() function.
  95.       '
  96.       ' ARGUMENTS:
  97.       '   T           - The table object containing the binary information.
  98.       '   sField      - The OLE object field in table T containing the
  99.       '                 binary information to write.
  100.       '   Destination - The path and filename to write the binary
  101.       '                 information to.
  102.       '
  103.       ' RETURN:
  104.       '   The number of bytes written to the destination file.
  105.       '**************************************************************
  106.       Function WriteBLOB(T As Recordset, sField As String, _
  107.       Destination As String)
  108.           Dim NumBlocks As Integer, DestFile As Integer, i As Integer
  109.           Dim FileLength As Long, LeftOver As Long
  110.           Dim FileData As String
  111.           Dim RetVal As Variant
  112.  
  113.           On Error GoTo Err_WriteBLOB
  114.  
  115.           ' Get the size of the field.
  116.           FileLength = T(sField).FieldSize()
  117.           If FileLength = 0 Then
  118.               WriteBLOB = 0
  119.               Exit Function
  120.           End If
  121.  
  122.           ' Calculate number of blocks to write and leftover bytes.
  123.           NumBlocks = FileLength \ BlockSize
  124.           LeftOver = FileLength Mod BlockSize
  125.  
  126.           ' Remove any existing destination file.
  127.           DestFile = FreeFile
  128.           Open Destination For Output As DestFile
  129.           Close DestFile
  130.  
  131.           ' Open the destination file.
  132.           Open Destination For Binary As DestFile
  133.  
  134.           ' SysCmd is used to manipulate the status bar meter.
  135.           RetVal = SysCmd(acSysCmdInitMeter, _
  136.           "Writing BLOB", FileLength / 1000)
  137.  
  138.           ' Write the leftover data to the output file.
  139.           FileData = T(sField).GetChunk(0, LeftOver)
  140.           Put DestFile, , FileData
  141.  
  142.           ' Update the status bar meter.
  143.           RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)
  144.  
  145.           ' Write the remaining blocks of data to the output file.
  146.           For i = 1 To NumBlocks
  147.               ' Reads a chunk and writes it to output file.
  148.               FileData = T(sField).GetChunk((i - 1) * BlockSize _
  149.                  + LeftOver, BlockSize)
  150.               Put DestFile, , FileData
  151.  
  152.               RetVal = SysCmd(acSysCmdUpdateMeter, _
  153.               ((i - 1) * BlockSize + LeftOver) / 1000)
  154.           Next i
  155.  
  156.           ' Terminates function
  157.           RetVal = SysCmd(acSysCmdRemoveMeter)
  158.           Close DestFile
  159.           WriteBLOB = FileLength
  160.           Exit Function
  161.  
  162.       Err_WriteBLOB:
  163.           WriteBLOB = -Err
  164.           Exit Function
  165.  
  166.       End Function
  167.  
  168.       '**************************************************************
  169.       ' SUB: CopyFile
  170.       '
  171.       ' PURPOSE:
  172.       '   Demonstrates how to use ReadBLOB() and WriteBLOB().
  173.       '
  174.       ' PREREQUISITES:
  175.       '   A table called BLOB that contains an OLE Object field called
  176.       '   Blob.
  177.       '
  178.       ' ARGUMENTS:
  179.       '   Source - The path and filename of the information to copy.
  180.       '   Destination - The path and filename of the file to write
  181.       '                 the binary information to.
  182.       '
  183.       ' EXAMPLE:
  184.       '   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
  185.       '**************************************************************
  186.       Sub CopyFile(Source As String, Destination As String)
  187.           Dim BytesRead As Variant, BytesWritten As Variant
  188.           Dim Msg As String
  189.           Dim db As Database
  190.           Dim T As Recordset
  191.  
  192.           ' Open the BLOB table.
  193.           Set db = CurrentDb()
  194.           Set T = db.OpenRecordset("BLOB", dbOpenTable)
  195.  
  196.           ' Create a new record and move to it.
  197.           T.AddNew
  198.           T.Update
  199.           T.MoveLast
  200.  
  201.           BytesRead = ReadBLOB(Source, T, "Blob")
  202.  
  203.           Msg = "Finished reading """ & Source & """"
  204.           Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
  205.           MsgBox Msg, 64, "Copy File"
  206.  
  207.           BytesWritten = WriteBLOB(T, "Blob", Destination)
  208.  
  209.           Msg = "Finished writing """ & Destination & """"
  210.           Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
  211.           MsgBox Msg, 64, "Copy File"
  212.       End Sub
  213.  
Nov 18 '06 #3

P: 16
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.
Nov 18 '06 #4

PEB
Expert 100+
P: 1,418
PEB
What is the name of the table that you should create for the blobs?
If different than Blob you need to change there!
Nov 18 '06 #5

P: 16
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.
Nov 18 '06 #6

PEB
Expert 100+
P: 1,418
PEB
Ok in this line change: "BLOB"
to
"SELECT * FROM BLOB;"
Nov 18 '06 #7

PEB
Expert 100+
P: 1,418
PEB
Remove DBOPENTABLE!
Nov 18 '06 #8

P: 16
Remove DBOPENTABLE!
What do you mean here? Remove it from where, the constants above?
Nov 18 '06 #9

PEB
Expert 100+
P: 1,418
PEB
Test the line like this:

Set T = db.OpenRecordset("SELECT * FROM BLOB;")
Nov 18 '06 #10

P: 16
Error, Type Mismatch, same line.
Nov 18 '06 #11

PEB
Expert 100+
P: 1,418
PEB
Ok I will test this code on my computer :) In few minutes i'll tell you what are the results
Nov 18 '06 #12

P: 16
Thank you so much, I have gotten no help on this subject for weeks!
Nov 18 '06 #13

PEB
Expert 100+
P: 1,418
PEB
Interesting in my case there isn't problems....

Have you compiled your modules before running the sub?

This is from Debug - > Compile
Nov 18 '06 #14

P: 16
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
Nov 18 '06 #15

PEB
Expert 100+
P: 1,418
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?
Nov 18 '06 #16

PEB
Expert 100+
P: 1,418
PEB
Do you have those constants?

Option Explicit
Const BlockSize = 32768
Nov 18 '06 #17

P: 16
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?
Nov 18 '06 #18

PEB
Expert 100+
P: 1,418
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!
Nov 18 '06 #19

PEB
Expert 100+
P: 1,418
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?
Nov 18 '06 #20

PEB
Expert 100+
P: 1,418
PEB
Try to insert this code in empty mdb file just created...

With only table BLOB and this module...
Nov 18 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 18 '06 #22

P: 16
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.
Dec 2 '06 #23

NeoPa
Expert Mod 15k+
P: 31,429
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.
Dec 3 '06 #24

P: 16
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.
Dec 4 '06 #25

P: 16
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?
Dec 12 '06 #26

NeoPa
Expert Mod 15k+
P: 31,429
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 ;).
Dec 12 '06 #27

P: 16
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.
Dec 19 '06 #28

P: 2
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim db as database
  2. Dim T as recordset
  3.  
Use:
Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.database
  2. Dim T as DAO.Recordset
  3.  
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,
Jan 16 '08 #29

NeoPa
Expert Mod 15k+
P: 31,429
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.
Jan 17 '08 #30

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub setBLOB(RS As ADODB.Recordset, Field As String, Source As String)
  2.  
  3. '' Places file into database
  4. 'setBLOB myRecordSet, "FileField", "c:\FileToAdd.rtf"
  5.  
  6.     Dim fileBytes() As Byte
  7.     Dim intFileHandle As Integer
  8.  
  9.     intFileHandle = FreeFile
  10.  
  11.     Open Source For Binary As intFileHandle
  12.         fileBytes = InputB(LOF(intFileHandle) - 1, intFileHandle)
  13.         RS(Field).AppendChunk fileBytes
  14.     Close intFileHandle
  15. End Sub
  16.  
  17.  
  18. Public Sub getBLOB(RS As ADODB.Recordset, Field As String, Des As String)
  19.  
  20. '' Save file to disk
  21. 'getBLOB myRecordSet, "FileField", "c:\FileToSaveto.rtf"
  22.  
  23.     Dim lngFieldSize As Long
  24.     Dim fileBytes() As Byte
  25.     Dim intFileHandle As Integer
  26.  
  27.     intFileHandle = FreeFile
  28.  
  29.     lngFieldSize = RS(Field).ActualSize
  30.     If lngFieldSize > 0 Then
  31.         fileBytes = RS(Field).GetChunk(lngFieldSize)
  32.         Open Des For Binary As intFileHandle
  33.             Put intFileHandle, , fileBytes
  34.         Close intFileHandle
  35.     End If
  36. End Sub
  37.  
  38.  
  39.  
  40. Sub TestBlob()
  41.  
  42. Dim RS As New ADODB.Recordset
  43.  
  44. RS.Open "SELECT * FROM tblBlob", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  45.  
  46. RS.AddNew
  47. setBLOB RS, "BlobFileField", filePath & "TestIn.rtf"
  48. RS.Update
  49. RS.Close
  50.  
  51. getBLOB RS, "BlobFileField", FilePath & "testOut.rtf"
  52.  
  53. End Sub
Feb 10 '12 #31

Post your reply

Sign in to post your reply or Sign up for a free account.