Displaying Images in Access 2003

[cbender]

I have been doing some tests with the database demo downloaded from
http://accdevel.tripod.com discussed by Larry Linson in his August 12
2006 post concerning this topic. Larry mentioned in his post that "Two
of the approaches [illustrated in the demo database] do not use OLE
Objects and, thus, avoid the database bloat, and some other problems,
associated with images in OLE Objects."

With regard to bloat, I tried some tests, but found the opposite to be
true with regard to BLOBs. Here are my data;

[Note: In between these uploads I deleted any previous image and
compacted and repaired the database, then closed and reopened the
database. I reversed the order uploading to verify this behavior.]

Size of empty Images in A2K.mdb 340 kb
Size of jpg image 61kb
Size of database after uploading image as OLE--412 kb
Size of database after uploading image as BLOB--472 kb

I'm no expert on why this is occurring, I would just like to better
understand this problem because I must deal with it on a fairly routine
basis. I came to the group because like many I am having trouble
getting bound bmp objects to display the picture instead of the icon.
I understand this may be because Microsoft Picture Manager does not
support OLE automation (??why), but this behavior persists even after
changing the file association for bmp to Paintbrush object and
restarting the computer. (This also would not provide a feasible
solution as I cannot control how my user's computers might be
configured)

Appreciate any light anyone can shed on either issue.
Thanks
Cheryl

 

[bob]

From the figures it looks like the BLOB storage example uses code that converts the binary data to a string,
which occupies double the size of the original binary (file) data. Binary data correctly stored as a (binary)
blob is the most compact way to store an image in the database, and will occupy the same space as the original
file, give or take a percent or two of granularity/allocation-table overhead (just like the file-system).


The figures also suggest that your OLE Embedding test stored the image as a 'Package'. This is a very
thin wrapper around the blob data, so the growth is similar to the original file size (and just slightly
larger than a correctly stored BLOB would be), but packages can't be displayed directly as an image on
a form or report, so this is often not what is required. Furthermore, the results are entirely dependant
on the presence or absence of OLE Server applications on any systems where images are inserted, so this
is very difficult to control on anything but a handful of systems.

Access creates a 'Package' when no suitable OLE Server application is registered. If an OLE Server were
available then you'd see much larger growth when using OLE Embedding. This usually leads to 2 uncompressed
'Preview' bitmap copies being stored in the field, as well as the image data stored in the format of the
OLE Server's choosing. In the case of MS Photo Editor that format is also an uncompressed bitmap, so 3
uncompressed copies are stored in the field. Since JPEG typically achieves between 10:1 and 50:1 (or even
higher) compression, the effect of storing 3 uncompressed copies in a field is that the database grows
by between 30 and 150 times the size of the file that was inserted.


Note that there is granularity in the growth of both binary fields and the mdb file itself, so it's best
to test with more than just one file to get accurate (real-word) results.

--
_______________________________________________________
The Access Image FAQ
http://tinyurl.com/y7hwfc

I have been doing some tests with the database demo downloaded from
http://accdevel.tripod.com discussed by Larry Linson in his August 12
2006 post concerning this topic. Larry mentioned in his post that "Two
of the approaches [illustrated in the demo database] do not use OLE
Objects and, thus, avoid the database bloat, and some other problems,
associated with images in OLE Objects."

With regard to bloat, I tried some tests, but found the opposite to be
true with regard to BLOBs. Here are my data;

[Note: In between these uploads I deleted any previous image and
compacted and repaired the database, then closed and reopened the
database. I reversed the order uploading to verify this behavior.]

Size of empty Images in A2K.mdb 340 kb
Size of jpg image 61kb
Size of database after uploading image as OLE--412 kb
Size of database after uploading image as BLOB--472 kb

I'm no expert on why this is occurring, I would just like to better
understand this problem because I must deal with it on a fairly routine
basis. I came to the group because like many I am having trouble
getting bound bmp objects to display the picture instead of the icon.
I understand this may be because Microsoft Picture Manager does not
support OLE automation (??why), but this behavior persists even after
changing the file association for bmp to Paintbrush object and
restarting the computer. (This also would not provide a feasible
solution as I cannot control how my user's computers might be
configured)

Appreciate any light anyone can shed on either issue.
Thanks
Cheryl

 

[cbender]

Thank you Bob for your explanation--this helps tremendously. The code
used in the example was from a Microsoft KB article (
http://support.microsoft.com/kb/103257 ) and does in fact as you
mention use a string to store the data. I was wondering though if
there is another way to correctly store Binary data as a BLOB in
Access, either in a memo or OLE field?

Cheryl

From the figures it looks like the BLOB storage example uses code that converts the binary data to a string,
which occupies double the size of the original binary (file) data. Binary data correctly stored as a (binary)
blob is the most compact way to store an image in the database, and will occupy the same space as the original
file, give or take a percent or two of granularity/allocation-table overhead (just like the file-system).


The figures also suggest that your OLE Embedding test stored the image as a 'Package'. This is a very
thin wrapper around the blob data, so the growth is similar to the original file size (and just slightly
larger than a correctly stored BLOB would be), but packages can't be displayed directly as an image on
a form or report, so this is often not what is required. Furthermore, the results are entirely dependant
on the presence or absence of OLE Server applications on any systems where images are inserted, so this
is very difficult to control on anything but a handful of systems.

Access creates a 'Package' when no suitable OLE Server application is registered. If an OLE Server were
available then you'd see much larger growth when using OLE Embedding. This usually leads to 2 uncompressed
'Preview' bitmap copies being stored in the field, as well as the image data stored in the format of the
OLE Server's choosing. In the case of MS Photo Editor that format is also an uncompressed bitmap, so 3
uncompressed copies are stored in the field. Since JPEG typically achieves between 10:1 and 50:1 (or even
higher) compression, the effect of storing 3 uncompressed copies in a field is that the database grows
by between 30 and 150 times the size of the file that was inserted.


Note that there is granularity in the growth of both binary fields and the mdb file itself, so it's best
to test with more than just one file to get accurate (real-word) results.

--
_______________________________________________________
The Access Image FAQ
http://tinyurl.com/y7hwfc

I have been doing some tests with the database demo downloaded from
http://accdevel.tripod.com discussed by Larry Linson in his August 12
2006 post concerning this topic. Larry mentioned in his post that "Two
of the approaches [illustrated in the demo database] do not use OLE
Objects and, thus, avoid the database bloat, and some other problems,
associated with images in OLE Objects."

With regard to bloat, I tried some tests, but found the opposite to be
true with regard to BLOBs. Here are my data;

[Note: In between these uploads I deleted any previous image and
compacted and repaired the database, then closed and reopened the
database. I reversed the order uploading to verify this behavior.]

Size of empty Images in A2K.mdb 340 kb
Size of jpg image 61kb
Size of database after uploading image as OLE--412 kb
Size of database after uploading image as BLOB--472 kb

I'm no expert on why this is occurring, I would just like to better
understand this problem because I must deal with it on a fairly routine
basis. I came to the group because like many I am having trouble
getting bound bmp objects to display the picture instead of the icon.
I understand this may be because Microsoft Picture Manager does not
support OLE automation (??why), but this behavior persists even after
changing the file association for bmp to Paintbrush object and
restarting the computer. (This also would not provide a feasible
solution as I cannot control how my user's computers might be
configured)

Appreciate any light anyone can shed on either issue.
Thanks
Cheryl