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:

[CODE=text]
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.
[/CODE]

[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.

[FishVal]

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.

Hello, ADezii.

Code logic is quite understandable - explanations not needed as soon as I'm developing the same for a couple of weeks and my code logic is the same with Access.Image control.

So far I have 3 forms with Access.Image, MSComctl.Image and ShDocVw.WebBrow ser controls respectively. So I'd like to feed a preliminary summary.

• Access.Image
  • (+) - most of common picture formats is supported
  • (+) - Access native control
  • (-) - slow, jpegs unpacking causes annoying progress bar
  • (-) - synchronization with form's events (particulary Current event) could be a very complicated issue
• MSComctl.Image
  • (+) - accepts IPictureDisp object which may be generated from BLOB without creating temporary disk file - so far I didn't have problems with "too fast browsing"
  • (+) - considerably fast
  • (-) - accepts relatively low quantity of image formats (actualy only those that could be used as source for IPictureDisp object)
• ShDocVw.WebBrow ser
  • (+/-) - accepts many picture formats (including animated GIFs), at the same time doesn't accept .wmf and *.tiff.
  • (+) - the fastest among these 3 controls
  • (+) - more flexible than previous too and may be better controlled by code (may be checked if busy, stopped, raises useful events etc)
  • (-) - coding is more complicated

Synchronization problem.

MSComCtl.Image seems to be ok.

For Access.Image I use a simple ugly stub.
The fault caused by ADODB.Stream.Sa veToFile when it runs while Access.Image is still retrieving picture is handled to make nothing (just like in your db). That causes the form to display picture from previous record when "browsing too fast". So the stub is really ugly - after Image.Picture is set the form Timer event is set to fire after 0.5 s to refresh Image.Picture content.

Even more weird situation is with WebBrowser control - it shows the same symptoms when moving too fast through records with large images.
The coding logic is the following though.

• Save BLOB to temporary file
• Create .html to navigate the control to
• Stop the control
• Navigate the control to .html

The effect is really nice - the control navigates to fresh copy of .html (picture dimensions change to the relevant values), temporary file contains a relevant image - the control displays the previous one LOL. Now, call .Refresh method and everything is fine.
Cure? The same as with Access.Image. LOL

So development is still in progress.

Regards,
Fish

[ADezii]

Hello, ADezii.

Code logic is quite understandable - explanations not needed as soon as I'm developing the same for a couple of weeks and my code logic is the same with Access.Image control.

So far I have 3 forms with Access.Image, MSComctl.Image and ShDocVw.WebBrow ser controls respectively. So I'd like to feed a preliminary summary.

• Access.Image
  • (+) - most of common picture formats is supported
  • (+) - Access native control
  • (-) - slow, jpegs unpacking causes annoying progress bar
  • (-) - synchronization with form's events (particulary Current event) could be a very complicated issue
• MSComctl.Image
  • (+) - accepts IPictureDisp object which may be generated from BLOB without creating temporary disk file - so far I didn't have problems with "too fast browsing"
  • (+) - considerably fast
  • (-) - accepts relatively low quantity of image formats (actualy only those that could be used as source for IPictureDisp object)
• ShDocVw.WebBrow ser
  • (+/-) - accepts many picture formats (including animated GIFs), at the same time doesn't accept .wmf and *.tiff.
  • (+) - the fastest among these 3 controls
  • (+) - more flexible than previous too and may be better controlled by code (may be checked if busy, stopped, raises useful events etc)
  • (-) - coding is more complicated

Synchronization problem.

MSComCtl.Image seems to be ok.

For Access.Image I use a simple ugly stub.
The fault caused by ADODB.Stream.Sa veToFile when it runs while Access.Image is still retrieving picture is handled to make nothing (just like in your db). That causes the form to display picture from previous record when "browsing too fast". So the stub is really ugly - after Image.Picture is set the form Timer event is set to fire after 0.5 s to refresh Image.Picture content.

Even more weird situation is with WebBrowser control - it shows the same symptoms when moving too fast through records with large images.
The coding logic is the following though.

• Save BLOB to temporary file
• Create .html to navigate the control to
• Stop the control
• Navigate the control to .html

The effect is really nice - the control navigates to fresh copy of .html (picture dimensions change to the relevant values), temporary file contains a relevant image - the control displays the previous one LOL. Now, call .Refresh method and everything is fine.
Cure? The same as with Access.Image. LOL

So development is still in progress.

Regards,
Fish

We're going to have to put you in a BLOB sequel, Fish! (LOL).

P.S. - I still like the idea of using a 'Native' Control, even with its drawbacks. *.jpgs can easily be converted to *.bmp in order to eliminate the admittedly annoying Progress Bar, but I understand that the synchronization problem will still exist along with possible significant increases in storage requirements for the *.bmps. Thanks for a very interesting and thorough Reply.

[zaidlig]

We're going to have to put you in a BLOB sequel, Fish! (LOL).

P.S. - I still like the idea of using a 'Native' Control, even with its drawbacks. *.jpgs can easily be converted to *.bmp in order to eliminate the admittedly annoying Progress Bar, but I understand that the synchronization problem will still exist along with possible significant increases in storage requirements for the *.bmps. Thanks for a very interesting and thorough Reply.

I realize I'm new here - but in 1999 we used Method 1 without any issues. We stored each OLE Object as a packaged icon. The Database growth was very minimal using jpg and gif images. Each record also had xls objects embedded. This was in a database that collected data and images to be used later in reports. During the automated report writing process the images were unpacked and rendered in the report. A packed database with 25 photos and 14 excel docs was less tha 6 MB and around 60 MB during the report render which resulted in a PDF. Afterwhich the database was compacted back to original size.

[ADezii]

I realize I'm new here - but in 1999 we used Method 1 without any issues. We stored each OLE Object as a packaged icon. The Database growth was very minimal using jpg and gif images. Each record also had xls objects embedded. This was in a database that collected data and images to be used later in reports. During the automated report writing process the images were unpacked and rendered in the report. A packed database with 25 photos and 14 excel docs was less tha 6 MB and around 60 MB during the report render which resulted in a PDF. Afterwhich the database was compacted back to original size.

Interesting point, zaidlig, but I can tell you that every single piece of documentation that I have come across lists Method #1 as the least desirable approach to storing Graphic Images in Access. This has also been supported by my own testing of the 3 Methods. Have you tried the Packaged Icon approach in later versions of Access? Let's be patient and see what other Moderators/Experts/Members have to say concerning this matter. Thanks for the interesting information.

[zaidlig]

Interesting point, zaidlig, but I can tell you that every single piece of documentation that I have come across lists Method #1 as the least desirable approach to storing Graphic Images in Access. This has also been supported by my own testing of the 3 Methods. Have you tried the Packaged Icon approach in later versions of Access? Let's be patient and see what other Moderators/Experts/Members have to say concerning this matter. Thanks for the interesting information.

The tool I built in 1999 is still in use today in North America. It was created in Access 97 and updated to 2000. I just tested it as 2002 no difference. Since most of my tools are for global use I can't move to 2003 yet.

So here is my table of sizes all values in KB

Original DB size - 860; added jpeg - 84; final size - 948; DB increase 88

Original DB size - 948; added jpeg - 72; final size - 1024; DB increase 76

I can send you example tables if you would like. The trick is to keep the image packed until you need it (like zipping) the beauty of using the package tool is it activates the OLE object without a pause like zipping would.