By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,053 Members | 1,615 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

The BLOB (not the movie!)

ADezii
Expert 5K+
P: 8,627
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
Jan 4 '08 #1
Share this Article
Share on Google+
55 Comments


missinglinq
Expert 2.5K+
P: 3,532
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)>
Jan 4 '08 #2

ADezii
Expert 5K+
P: 8,627
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.
Jan 4 '08 #3

ADezii
Expert 5K+
P: 8,627
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:

Expand|Select|Wrap|Line Numbers
  1. DB Size      Graphic      DB Growth      End DB Size
  2. 500 Kb       100 Kb        200 Kb           700 Kb
  3. 700 Kb        50 Kb        100 Kb           800 Kb
  4. 800 Kb       300 Kb        600 Kb         1,400 Kb
  5. etc.
  6.  
Jan 5 '08 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 8 '08 #5

ADezii
Expert 5K+
P: 8,627
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).
Jan 8 '08 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 8 '08 #7

FishVal
Expert 2.5K+
P: 2,653
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.
Jan 8 '08 #8

ADezii
Expert 5K+
P: 8,627
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.
Jan 9 '08 #9

ADezii
Expert 5K+
P: 8,627
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.
Jan 9 '08 #10

FishVal
Expert 2.5K+
P: 2,653
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.WebBrowser 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.WebBrowser
    • (+/-) - 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.SaveToFile 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
Jan 9 '08 #11

ADezii
Expert 5K+
P: 8,627
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.WebBrowser 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.WebBrowser
    • (+/-) - 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.SaveToFile 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.
Jan 10 '08 #12

P: 45
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.
Jan 12 '08 #13

ADezii
Expert 5K+
P: 8,627
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.
Jan 13 '08 #14

P: 45
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.
Jan 13 '08 #15

ADezii
Expert 5K+
P: 8,627
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.
Thanks for the added information.
  1. How exactly is this Packing/Unpacking process implemented?
  2. Is this process applicable to *.gifs and *.jpgs only?
  3. You really sparked my interest, and i'm sure others would also be interested in your solution, also.
Jan 13 '08 #16

FishVal
Expert 2.5K+
P: 2,653
Hi, zaidlig.

Do you mean Package object?
If so, then whether it has any advantage over BLOB but no code needed?
I mean - is there any form control which can display contents of package?

Regards,
Fish
Jan 13 '08 #17

P: 45
Hi, zaidlig.

Do you mean Package object?
If so, then whether it has any advantage over BLOB but no code needed?
I mean - is there any form control which can display contents of package?

Regards,
Fish
Answers:

1a) Packing: to add an image right click the control > insert object > create new > object type - package. Now you can add your object lots of ways. I browse to my file drag to contents window and file > update and exit.

1b) To unpack just active the object by double clicking or with code.

2) I believe anything can be packaged. I've just used it on image objects, including PPS.

Yes Package object is a small exe that is from like Windows 3.0. Yes no code needed and less expansion then BLOB.

The package object is an OLE object and has only one display method - OLE activate - which activates whatever is packed.

You get the same effect if your zipped your images before storing as an OLE object. However zip.exe files adds more overhead - expanding the DB.

Also a neat thing about the Package OLE object as with other true OLE objects it can be activated directly from a table view.
Jan 13 '08 #18

FishVal
Expert 2.5K+
P: 2,653
Answers:

1a) Packing: to add an image right click the control > insert object > create new > object type - package. Now you can add your object lots of ways. I browse to my file drag to contents window and file > update and exit.

1b) To unpack just active the object by double clicking or with code.
Well. I quite aware of that. Just wanted to make evidence we are mentioning the same.

2) I believe anything can be packaged. I've just used it on image objects, including PPS.
Sure every file may be packaged. Just like every file may be "blobbed".

The package object is an OLE object and has only one display method - OLE activate - which activates whatever is packed.

You get the same effect if your zipped your images before storing as an OLE object. However zip.exe files adds more overhead - expanding the DB.

Also a neat thing about the Package OLE object as with other true OLE objects it can be activated directly from a table view.
All that could be done with BLOBs via just a trivial coding (but operations from a table view of course).

Actually I would like to split the whole issue into two logical parts:
  • storing/retrieving files - easily implemented with OLE packager and a bit more complicated but still easily with BLOBs
    So far the only advantage of OLE Packager over neat BLOBs is somewhat simpler implementation.
  • representing files on form - somewhat more challenging task
    OLE Packager is too programmer friendly. No code needed as well no automation supported. Sure storing in BLOB doesn't help much here but at least doesn't disturb to retrieve neat file stored.

Regards,
Fish

P.S. Thanks anyway for taking part in the discussion.
Jan 13 '08 #19

P: 45
The reason I used the package was the minimal size increase when storing images. Which is the biggest advantage over BLOBing. As indicated above a BLOB added 2 X the original file size while a package added 4KB plus the original file size.

That's huge if you have lots of images. Of if your users fail to resize images to reasonable size < 300 KB.
Jan 13 '08 #20

FishVal
Expert 2.5K+
P: 2,653
The reason I used the package was the minimal size increase when storing images. Which is the biggest advantage over BLOBing. As indicated above a BLOB added 2 X the original file size while a package added 4KB plus the original file size.

That's huge if you have lots of images. Of if your users fail to resize images to reasonable size < 300 KB.
That is obviously a bug. There is no theoretical or practical reason for that. Moreover I have normal space growing with BLOBs in my application mentioned above.

Size: 1316k
Added JPG: 2854k
Result: 4192k
Added PNG: 71k
Result: 4264k

BTW its working more or less stable so far and I'll be glad to provide a sample as soon as I'll finish all makeups.

Regards,
Fish
Jan 13 '08 #21

P: 45
That is obviously a bug. There is no theoretical or practical reason for that. Moreover I have normal space growing with BLOBs in my application mentioned above.

Size: 1316k
Added JPG: 2854k
Result: 4192k
Added PNG: 71k
Result: 4264k

BTW its working more or less stable so far and I'll be glad to provide a sample as soon as I'll finish all makeups.

Regards,
Fish
I must have missed where you indicated the values in posting 4 above, were incorrect.

I only entered this discussion because in my experience the tip of the week was not true in all solutions. True the solution I created 9 years ago may not be the best solution for your particular situation (where flipping through images is critical). However I read somewhere that the packager functions were also built into IE which would feed your Web Browser control.

I will be updating the old solution during 2008. Will the BLOB solution allow editing the images after they have been stored in the native application? That is critical for my solution since after photos are stored they are often edited, trimmed, pointers added etc. before the report render is complete.
Jan 13 '08 #22

ADezii
Expert 5K+
P: 8,627
I must have missed where you indicated the values in posting 4 above, were incorrect.

I only entered this discussion because in my experience the tip of the week was not true in all solutions. True the solution I created 9 years ago may not be the best solution for your particular situation (where flipping through images is critical). However I read somewhere that the packager functions were also built into IE which would feed your Web Browser control.

I will be updating the old solution during 2008. Will the BLOB solution allow editing the images after they have been stored in the native application? That is critical for my solution since after photos are stored they are often edited, trimmed, pointers added etc. before the report render is complete.
Will the BLOB solution allow editing the images after they have been stored in the native application? That is critical for my solution since after photos are stored they are often edited, trimmed, pointers added etc. before the report render is complete.
Yes, but that functionality was not incorporated into the Tip. I'm going on Vacation until Thursdy, when I return I'll Post that solution as soon as possible.
Jan 13 '08 #23

P: 45
Yes, but that functionality was not incorporated into the Tip. I'm going on Vacation until Thursdy, when I return I'll Post that solution as soon as possible.
FishVal,

I just played with the packager solution and found that by simply copy and pasting the package object to a folder; it extracted the packed object which could then be viewed in the web Browser control. So it appears the packager can be automated for viewing as easily as BLOB.

ADezii,

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.
I say if you copy the package to a temp folder they can be displayed in the Web Browser control. No database blot if the are packaged.
Jan 13 '08 #24

FishVal
Expert 2.5K+
P: 2,653
FishVal,

I just played with the packager solution and found that by simply copy and pasting the package object to a folder; it extracted the packed object which could then be viewed in the web Browser control. So it appears the packager can be automated for viewing as easily as BLOB.
That is what I actually staying on. Storing/retrieving files is a rather trivial task, may be achieved with Packager object or simply storing neat file data.
Packager has a substantial drawback as it may be easily operated via form control, while not allowing working via recordset or in-query invoked code.
Just imagine what a solution of batch processing will look like.

I will be updating the old solution during 2008. Will the BLOB solution allow editing the images after they have been stored in the native application? That is critical for my solution since after photos are stored they are often edited, trimmed, pointers added etc. before the report render is complete.
ROFL. Just today was thinking about this without any reason to even start thinking about this. Seems not a big deal. Though the obvious holes should be addressed.

Regards,
Fish
Jan 13 '08 #25

Expert 5K+
P: 8,434
I have a couple of questions about this thread...
  • In post #12, ADezii made reference to converting JPG images to BMP format to reduce load time, at the expense of a substantial size increase. Has anyone tried using JPG format with less compression? Perhaps the level of compression will significantly affect the decompression speed.
    The trade-off between compression rate and image quality can be well worthwhile. When I copy photos from a digital camera (or pretty much anywhere) I often use Paint Shop Pro to increase the compression level to 42 (the setting is between 1 and 99). Apart from being the ultimate answer of course, this almost always shrinks the file size considerably, without significantly impacting on image quality.
  • In post #19, FishVal said "OLE Packager is too programmer friendly". Could you please explain why this is a bad thing? You also mentioned no coding needed, which sounds ideal (though as a programmer I'm opposed to such a thing on philosophical grounds) but I think you also indicated that no "automation" is possible, which does sound like a problem.


P.S. Just for you trivia buffs, here are the details of a rather nice photo of a swimming duck...
  • 16 million-colour BMP: 818KB
  • JPG (level 1): 310KB (indistinguishable from BMP)
  • JPG (level 42): 46KB (difference from BMP only visible if you switch back and forth)
  • JPG (level 50): 32KB (slightly worse than at level 42)
  • JPG (level 99): 7KB (Image sucks! Though when viewed small enough, works quite well as a thumbnail.)
Jan 14 '08 #26

FishVal
Expert 2.5K+
P: 2,653
In post #19, FishVal said "OLE Packager is too programmer friendly". Could you please explain why this is a bad thing? You also mentioned no coding needed, which sounds ideal (though as a programmer I'm opposed to such a thing on philosophical grounds) but I think you also indicated that no "automation" is possible, which does sound like a problem.
I've meant - no coding needed for simple save/retrieve which on the one hand sounds good. On the other hand almost no additional functionality provided. At the same time working with neat stored file content becomes more difficult as it coffined in OLE wrapper. I don't see any other easy way to get it but via form with OLE control bound to a table field. So batch operations may become pretty resource consuming.

I think BLOB with some minor coding providing the same functionality as Packager is more suitable as it gives more options. Is that a too hard task to write some code? LOL.

Regards,
Fish
Jan 14 '08 #27

ADezii
Expert 5K+
P: 8,627
I must have missed where you indicated the values in posting 4 above, were incorrect.

I only entered this discussion because in my experience the tip of the week was not true in all solutions. True the solution I created 9 years ago may not be the best solution for your particular situation (where flipping through images is critical). However I read somewhere that the packager functions were also built into IE which would feed your Web Browser control.

I will be updating the old solution during 2008. Will the BLOB solution allow editing the images after they have been stored in the native application? That is critical for my solution since after photos are stored they are often edited, trimmed, pointers added etc. before the report render is complete.
Will the BLOB solution allow editing the images after they have been stored in the native application? That is critical for my solution since after photos are stored they are often edited, trimmed, pointers added etc. before the report render is complete.
I've added increased functionality to the original BLOB's Tip, in that it now allows editing of the BLOB Image itself in the Image Editor of your choice. Simply download BLOBs_2.zip to see how this may be accomplished. An initial explanation of the process appears when you Open the Database.
Jan 16 '08 #28

ADezii
Expert 5K+
P: 8,627
I have a couple of questions about this thread...
  • In post #12, ADezii made reference to converting JPG images to BMP format to reduce load time, at the expense of a substantial size increase. Has anyone tried using JPG format with less compression? Perhaps the level of compression will significantly affect the decompression speed.
    The trade-off between compression rate and image quality can be well worthwhile. When I copy photos from a digital camera (or pretty much anywhere) I often use Paint Shop Pro to increase the compression level to 42 (the setting is between 1 and 99). Apart from being the ultimate answer of course, this almost always shrinks the file size considerably, without significantly impacting on image quality.
  • In post #19, FishVal said "OLE Packager is too programmer friendly". Could you please explain why this is a bad thing? You also mentioned no coding needed, which sounds ideal (though as a programmer I'm opposed to such a thing on philosophical grounds) but I think you also indicated that no "automation" is possible, which does sound like a problem.


P.S. Just for you trivia buffs, here are the details of a rather nice photo of a swimming duck...
  • 16 million-colour BMP: 818KB
  • JPG (level 1): 310KB (indistinguishable from BMP)
  • JPG (level 42): 46KB (difference from BMP only visible if you switch back and forth)
  • JPG (level 50): 32KB (slightly worse than at level 42)
  • JPG (level 99): 7KB (Image sucks! Though when viewed small enough, works quite well as a thumbnail.)
The trade-off between compression rate and image quality can be well worthwhile.
Good point, Killer but the main problem here is not Compression Rate vs Image Quality, but the Unpacking Progress Dialog that appears when the *.jpgs are dynamically loaded into the Image Control. Lowering the Compression Rate may/may not affect this to some degree.
Jan 16 '08 #29

Expert 5K+
P: 8,434
Good point, Killer but the main problem here is not Compression Rate vs Image Quality, but the Unpacking Progress Dialog that appears when the *.jpgs are dynamically loaded into the Image Control. Lowering the Compression Rate may/may not affect this to some degree.
True. My original thought (completely untested) was that a lower compression rate might also be faster to decompress, thus reducing the nuisance. Once I got onto the subject though, I think I got a bit carried away. :)

I've never seen the dialogue in question. Perhaps someone who has the opportunity could test the theory? Since we're talking about less compression, presumably the image quality won't be an issue.
Jan 16 '08 #30

ADezii
Expert 5K+
P: 8,627
True. My original thought (completely untested) was that a lower compression rate might also be faster to decompress, thus reducing the nuisance. Once I got onto the subject though, I think I got a bit carried away. :)

I've never seen the dialogue in question. Perhaps someone who has the opportunity could test the theory? Since we're talking about less compression, presumably the image quality won't be an issue.
If you ever get the opportunity, which you probably never get as Moderator (LOL), download the BLOBs Tip, then substitute *.jpgs for any *.bmps. Rapidly navigate through the Records and the point will become obvious, if not Lock the PC. I thank you for your input on this Topic and take care.
Jan 17 '08 #31

Expert 5K+
P: 8,434
If you ever get the opportunity, which you probably never get as Moderator (LOL), download the BLOBs Tip, then substitute *.jpgs for any *.bmps. Rapidly navigate through the Records and the point will become obvious, if not Lock the PC. I thank you for your input on this Topic and take care.
I'd love to, but don't know when I'll have the time - you're right about that.

I've actually been planning to check out the BLOB tips for quite a while, now.

Hm... I wonder why it's so slow to load JPGs compred to other viewers. Oh wait! It's an MS product - ok.
Jan 17 '08 #32

P: 30
This API may be helpful:

Suppress the "Loading Image" dialog
Jan 17 '08 #33

ADezii
Expert 5K+
P: 8,627
This API may be helpful:

Suppress the "Loading Image" dialog
Thanks, HiTechCoach! This is definitely one that I'll try out as soon as I get the chance, this Unpacking problem with the *.jpgs is a real nuisance.
Jan 17 '08 #34

FishVal
Expert 2.5K+
P: 2,653
I've added increased functionality to the original BLOB's Tip, in that it now allows editing of the BLOB Image itself in the Image Editor of your choice. Simply download BLOBs_2.zip to see how this may be accomplished. An initial explanation of the process appears when you Open the Database.
Hello, ADezii.

The improvements are noticeable, though "too fast browsing" bug still not addressed.

Concerning my db, I think it is sufficent stable to give it a public trial. Will try to attach the file.

Ok. Attached it, though I have been suspecting it is too latge to be attached.
The db contains 3 forms.
  • with Access.Image control
  • with MSForms.Image control
  • with WebBrowser control

The first two are for browsing only. The third implements more comprehensive interface.

Ladies and gentlemen, your feedback concerning bugs, faults and overall design problems would be greatly appreciated.

Regards,
Fish

P.S. He-he, resurrecting the article about image storage in Access coffined a couple of months ago.
Attached Files
File Type: zip ImageStoring.zip (121.7 KB, 351 views)
Jan 17 '08 #35

ADezii
Expert 5K+
P: 8,627
Hello, ADezii.

The improvements are noticeable, though "too fast browsing" bug still not addressed.

Concerning my db, I think it is sufficent stable to give it a public trial. Will try to attach the file.

Ok. Attached it, though I have been suspecting it is too latge to be attached.
The db contains 3 forms.
  • with Access.Image control
  • with MSForms.Image control
  • with WebBrowser control

The first two are for browsing only. The third implements more comprehensive interface.

Ladies and gentlemen, your feedback concerning bugs, faults and overall design problems would be greatly appreciated.

Regards,
Fish

P.S. He-he, resurrecting the article about image storage in Access coffined a couple of months ago.
Thanks Fish, I will definately download the Database and have a look at it as soon as I can. You were correct about the Attachment Limitation, it is normally restricted to 100K, but was lifted by Mary especially for this Tip. She's OK, that Mary is!
Jan 17 '08 #36

Expert 5K+
P: 8,434
This is definitely one that I'll try out as soon as I get the chance, this Unpacking problem with the *.jpgs is a real nuisance.
I suppose another, probably very ugly, alternative would be if you could somehow freeze the entire display just before the unpack, and "unfreeze" just after. (Perhaps using standard screensaver techniques.)

Or is there some way you could trick it into putting the popup off-screen? Anyone know how it's positioned? Is it just centred on the screen, or positioned relative to the window, or what?

Perhaps you could even set the window to "always on top" then back to normal?

Could you replace the filter with one that doesn't have the popup?

Sorry folks, I accidentally engaged my random idea generator.
Jan 18 '08 #37

ADezii
Expert 5K+
P: 8,627
MAJOR ANNOUNCEMENT!

Thanks to a Link supplied by HiTechCoach, the problem involving the Progress Dialog appearing when loading Graphics (*.jpgs) into an Image Control has been solved. I changed the following Registry Setting to No as suggested by the 1st Option in HiTechCoach's Link (Option A):
HKEY_LOCAL_MACHINE\SOFTWARE\Shared Tools\Graphics Filters\Import\JPEG\Options
Expand|Select|Wrap|Line Numbers
  1. Name                             Type                Data
  2. ShowProgressDialog               REG_SZ              No
  3.  
P.S. - The Link also indicates that for multiple Accounts, the Registry Key must be added to each separate Account or to HKEY_CURRENT_USER. See the Link supplied by HiTechCoach for specific details.
Jan 18 '08 #38

ADezii
Expert 5K+
P: 8,627
This API may be helpful:

Suppress the "Loading Image" dialog
I wanted to personally thank you for your excellent Tip/Link concerning the annoying Progress Dialog involving *.jpgs. I spent endless hours, even prior to this Tip, trying to find a solution that would eliminate that annoying Progress Dialog when loading *.jpgs. My solution was to convert *.jpgs to *.bmps which solved the problem, but which was hardly adequate. Thanks again, and please refer to Post #38.
Jan 18 '08 #39

P: 21
I would like to thank you all in advance as i got the idea and after completion i will agian thank you all for help
Feb 21 '08 #40

ADezii
Expert 5K+
P: 8,627
I would like to thank you all in advance as i got the idea and after completion i will agian thank you all for help
From all of us here at TheScripts, you are quite welcome,
Feb 21 '08 #41

P: 21
From all of us here at TheScripts, you are quite welcome,

As I am developing "Gate Pass Generation System" using VB 6.0 , Ms Access and a web cam through which I can capture images and store it to Ms access Tables

Now storing text is an easy task but how to store images if I captured it on Form using Picure1.picture = Clippboard.getdata
Now with all other text details , how can i move image to Ms Access tables


Please provide some guidelines or code for that

Thanks
Feb 22 '08 #42

ADezii
Expert 5K+
P: 8,627
As I am developing "Gate Pass Generation System" using VB 6.0 , Ms Access and a web cam through which I can capture images and store it to Ms access Tables

Now storing text is an easy task but how to store images if I captured it on Form using Picure1.picture = Clippboard.getdata
Now with all other text details , how can i move image to Ms Access tables


Please provide some guidelines or code for that

Thanks
I think that you are going to have a lot of problems with that one. The concept behind the BLOB approach is to take the contents of a Graphic File, load it into a Binary Stream, then save it/display it in an Image Control, to an OLE Object Field. For retrieval, the Binary Data is extracted to the same Graphic File Type in a dynamically created Folder/File, then displayed in an Image Control. Your approach would be to take the Picture Data itself, as embedded in a Picture Control as the Picture property, and store it directly in the Database. I'm not sure this can be done with BLOBs, but I'm sure you can bind the Images in the Picture Control to a Field in either a Local or Linked Table for storage. Reference one of the earliest Posts for this approach. (Post#1 - Item #1). Good luck!
Feb 22 '08 #43

FishVal
Expert 2.5K+
P: 2,653
Hi, noviceadmin.

So far I have no ready solution for that, but here is my 2 cents to the discussion.

I guess you get image from clipboard as IPictureDisp object. As far as I know you may put it to binary stream using WinAPI (almost sure an appropriate function may be found in ole32.dll).

Regards.
Fish
Feb 22 '08 #44

ADezii
Expert 5K+
P: 8,627
Hi, noviceadmin.

So far I have no ready solution for that, but here is my 2 cents to the discussion.

I guess you get image from clipboard as IPictureDisp object. As far as I know you may put it to binary stream using WinAPI (almost sure an appropriate function may be found in ole32.dll).

Regards.
Fish
Great idea, FishVal - never thought of that one.
Feb 22 '08 #45

P: 21
Dear A Dezii
I am getting your point but how can i achieve that
Like texts as......fields(1)= text1.text

is there any technique for pictures if they are available on forms like texts
Feb 25 '08 #46

Expert 5K+
P: 8,434
...I am getting your point but how can ...
I think you really ought to start a discussion thread in the appropriate forum for this, rather than continuing it in the HowTo comments.
Feb 25 '08 #47

P: 21
Dear A Dezzi

I have got all the help from this website ...like i am now able to capture the image from webcam and displaying it on the FORM in a Picture box.....But after that I have to store that image to access database( I am Using VB 6 and MS access for development).
You all told me to use BLOB technique
but no one told me How I can pick that image for BLOB treatment if the image is in a picture box on the form

Getting my point or not

plese tell me the solution

This website really helped me

Thanks to all
Mar 5 '08 #48

ADezii
Expert 5K+
P: 8,627
but no one told me How I can pick that image for BLOB treatment if the image is in a picture box on the form
This is not the typical approach for storing BLOBs and I'm not really sure of the answer. Since the Picture Box is a native Visual Basic Control, and not supported by Access, it may be a good idea to Post this question in the Visual Basic Forum also. I too, have a VB background, and I'll do a little research myself to see if I can come up with anything to solve your problem.
Mar 6 '08 #49

P: 21
This is not the typical approach for storing BLOBs and I'm not really sure of the answer. Since the Picture Box is a native Visual Basic Control, and not supported by Access, it may be a good idea to Post this question in the Visual Basic Forum also. I too, have a VB background, and I'll do a little research myself to see if I can come up with anything to solve your problem.

No one is there in this world who can solve my problem ,,,My Dear Programmers.

Please provide some solution to this problem

Thank you
Mar 8 '08 #50

55 Comments