The BLOB (not the movie!)

[ADezii]

Of all the questions asked here at TheScripts, the one which appears with the most frequency relates to the storing of Graphic Images within Access. There are basically three techniques available to manipulate Image Files in Access and they are:

1. Store the Image in an OLE Object Field and display it in a Bound Object Frame. The Image can either be Linked or Embedded.
2. Store the Path to the Image in a Text Field and use an Image Control to dynamically display it.
3. Store the Image as a Binary Large Object (BLOB) in an OLE Field, extract the Image when required, and use an Image Control to display the Image.

The first technique is the most problematic primarily because of Server Errors (the Application launched to display the Image) and horrendous Database bloat because of the manner in which Access internally stores Images.

The second approach, although relatively easy to implement, has its own set of inherent problems. This technique cannot be used with Continuous Forms, and a fully qualified UNC Path is a strict requirement. If you move the Images, if they reside on a Server and the Folder containing them is not shared, if the Server is turned off, if you do not have Read Permissions to the PC, I think you get the idea.

After all is said and done, the third technique quickly becomes the obvious choice for Image storage. Storing Pictures as BLOBs in the Database is the most efficient way on containing Image data because the Picture data is stored byte for byte, exact size, with the Images reflecting their true sizes.

When BLOBs are stored in the Database with other data, BLOB and tabular data are backed up and recovered together and are synchronized, there are no File Paths to contend with, and no resultant Database bloating. Data consistency is ensured because INSERT, UPDATE, and DELETE operations occur in the same transaction. Separate security measures need not be created since BLOB and regular data coexist.

The minimal requirement to implement the BLOB technique is two Functions, one to put files into the Database, and the other to take them out. You can use either DAO or ADO to grab the Image File and read it into an OLE Field (1st Function). To display the Image we have to extract it from the Database (2nd Function) into a Temporary File then use an Image Control to display it by setting the Control's Picture property to the Path of the Temp File.

Some of the subject matter that appears in this Tip, as well as code that exists in the Demonstration Database were taken from Alan Warren's web site. I thought that he provided an excellent insight into this Topic and his code provided a very good example of how to implement this functionality. I have changed the entire Theme of the Database, made structural changes to the Tables, cosmetic changes to the Form, and have also added, deleted, and modified Mr. Warren's code. The critical code sections were essentially kept in tact.


Required References:
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects X.X Library

[missinglinq]

Loved The Blob (the movie, not the Access thingie!) so I changed the status of this thread to "Stuck" so it wouldn't get lost in the shuffle!

Linq ;0)>

[ADezii]

Loved The Blob (the movie, not the Access thingie!) so I changed the status of this thread to "Stuck" so it wouldn't get lost in the shuffle!

Linq ;0)>

Thanks linq, I'd do it myself but don't have the access.

[ADezii]

Just an interesting side note on this Topic of storing Graphic Images as BLOBs. In some elementary testing, I found that with each Graphic insertion, the overall size of the Database grew by almost exactly 2X the size of the Graphic Image. This seems to be independent of the actual Graphic Type (*.bmp, *.jpg, *.ico). A simple Table will demonstrate my findings:

DB Size      Graphic      DB Growth      End DB Size
500 Kb       100 Kb        200 Kb           700 Kb
700 Kb        50 Kb        100 Kb           800 Kb
800 Kb       300 Kb        600 Kb         1,400 Kb
etc.
 

[MMcCarthy]

OK I've had the file size limit increased for zip files and attached the database. I deleted the last paragraph of your article accordingly. You may want to go back and add a couple of lines though.

Mary

[ADezii]

OK I've had the file size limit increased for zip files and attached the database. I deleted the last paragraph of your article accordingly. You may want to go back and add a couple of lines though.

Mary

Thanks Mary, if it's OK with you I'd like to keep this Tip running for 2 weeks since I honestly think that it will generate a lot of interest, and besides, I put a lot of time into it. (LOL).

[MMcCarthy]

Thanks Mary, if it's OK with you I'd like to keep this Tip running for 2 weeks since I honestly think that it will generate a lot of interest, and besides, I put a lot of time into it. (LOL).

I have no problem with that.

[FishVal]

Thanks Mary, if it's OK with you I'd like to keep this Tip running for 2 weeks since I honestly think that it will generate a lot of interest, and besides, I put a lot of time into it. (LOL).

Hello, ADezii.

It's a very interesting issue and I'll be happy to discuss it. Moreover, just now I'm working on implementing BLOBs storage in Access db.
Storing/retrieving BLOBs is not a very challenging problem. The real trick is with form control suitable to represent the stored image.
Actually I don't like native Access Image control - it is slow and don't accept many picture formats. MSForms.Image is somewhat problematic too. So far I use WebBrowser control which is considerably fast and accepts a bunch of different image formats.
So far the code is quite unstable (endless issues of synchronizing Form events and ActiveX control), but I hope I'll work it out in a couple of days. I'll send you a sample if find it interesting.

Regards,
Fish

P.S. Note that your db still has Image control to form events synchronizing problem. It can be noticed with large jpegs and "too fast" browsing through the records. Though error is trapped, the picture for the previous record remains on the control while the form is focused on the next record.

[ADezii]

Hello, ADezii.

It's a very interesting issue and I'll be happy to discuss it. Moreover, just now I'm working on implementing BLOBs storage in Access db.
Storing/retrieving BLOBs is not a very challenging problem. The real trick is with form control suitable to represent the stored image.
Actually I don't like native Access Image control - it is slow and don't accept many picture formats. MSForms.Image is somewhat problematic too. So far I use WebBrowser control which is considerably fast and accepts a bunch of different image formats.
So far the code is quite unstable (endless issues of synchronizing Form events and ActiveX control), but I hope I'll work it out in a couple of days. I'll send you a sample if find it interesting.

Regards,
Fish

P.S. Note that your db still has Image control to form events synchronizing problem. It can be noticed with large jpegs and "too fast" browsing through the records. Though error is trapped, the picture for the previous record remains on the control while the form is focused on the next record.

Hello Fish, always good to hear from you. The synchronizing problem, in my opinion, is understandable given its context as far as what is involved in Record Navigation and large *.jpgs. For each movement to another Record:

1. A Recordset must be created to see if an associated Image actually exists for the Current Record.
2. Assuming the Image exists, the Binary Data representing the Image has to be extracted from the OLE Object Field and written to a Stream Object.
3. The data is then saved to a Temporary File in a Temporary Folder with the proper Extension.
4. Finally, the Image is loaded into the Picture Property of the Image Control.from the Absolute Path created in the previous step.

Given their much larger size, I've found much better success using Images saved in *.bmp Format as opposed to *.jpg when navigating through Records. It appears as though the larger size does not significantly impact the load time, and you never get that annoying progress Dialog when a *.jpg is loaded. I'm really not sure why this occurs, but I assume it has something to do with the compressed nature of *.jpgs.

[ADezii]

Hello, ADezii.

It's a very interesting issue and I'll be happy to discuss it. Moreover, just now I'm working on implementing BLOBs storage in Access db.
Storing/retrieving BLOBs is not a very challenging problem. The real trick is with form control suitable to represent the stored image.
Actually I don't like native Access Image control - it is slow and don't accept many picture formats. MSForms.Image is somewhat problematic too. So far I use WebBrowser control which is considerably fast and accepts a bunch of different image formats.
So far the code is quite unstable (endless issues of synchronizing Form events and ActiveX control), but I hope I'll work it out in a couple of days. I'll send you a sample if find it interesting.

Regards,
Fish

P.S. Note that your db still has Image control to form events synchronizing problem. It can be noticed with large jpegs and "too fast" browsing through the records. Though error is trapped, the picture for the previous record remains on the control while the form is focused on the next record.

Just as another side note, Fish, implementing BLOBs in DAO seems to be mpre complex since there appears to be no intermediate Stream Object involved. The Image File must be opened in Binary Mode, data Read from it, then written to the OLE Object Field storing the Image Data in Chunks.