small jpg insert to OLE field induce significant enlarge of the access file ????

[Martin]

My table contains several field which is OLE.

This Access file is now around 200k. However, when I insert one .jpg(around
250k) in one of the field, this access files enlarge to 1.8 M at once!

I found that, when a small picture was inserted into an OLE, the access
files will enlarge quite a lot ! I have used the "press database" function
to press this access file, but also no use.

How to solve this problem?

Thanks!

Martin

 

[JohnB]

Martin.

Im no Access expert but Ive been playing around with Images recently and
might be able to help here. When I 'insert' a picture, I choose the Link
option, rather than imbed the image in the database. I find that saves a lot
of space.

Hope that helps. JohnB

 

[Larry Linson]

My table contains several field which is OLE.

Likely that is your first problem...

This Access file is now around 200k.
However, when I insert one .jpg(around
250k) in one of the field, this access file
enlarge to 1.8 M at once!

The sample imaging databases at http://accdevel.tripod.com illustrate three
approaches to handling images in Access, and the download includes an
article discussing considerations in choosing an approach. Two of the
approaches do not use OLE Objects and, thus, avoid the database bloat, and
some other problems, associated with images in OLE Objects.

If you are printing the images in reports, to avoid memory leakage, you
should also see MVP Stephen Lebans' http://www.lebans.com/printfailures.htm.
PrintFailure.zip is an Access97 MDB containing a report that fails during
the Access formatting process prior to being spooled to the Printer Driver.
This MDB also contains code showing how to convert the contents of the Image
control to a Bitmap file prior to printing. This helps alleviate the "Out of
Memory" error that can popup when printing image intensive reports.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

. . . When I 'insert' a picture, I choose
the Link option, rather than imbed the
image in the database. I find that saves
a lot of space.

You might want to take a look at my response to the original poster. Even
using Link with OLE Objects, you still get the bitmap thumbnail generated
and it can be much, much larger than your original image file if the
original is in a compressed format such as .JPG or .GIF.

Larry Linson
Microsoft Access MVP

 

[Guest]

Hi Larry.

Sorry to confuse. Of course you are correct. Ive just tried adding some
images to my db, compacting before and after and I find that I add around 2mb
for each thumbnail.

You previously had pointed me toward http://accdevel.tripod.com and I have
now had a good look at it. I feel like I should incorporate the 'External
File' option in my mdb but I'm a bit lost as to where to start. I have a
Student form, with each record currently showing a thumbnail. How would I
change the form to incorporate the new process? Would I have a command button
to somehow call up a form (frmImageFileSummaryList?) to allow me to allocate
an image to that particular Student record? How would this be linked to my
current tblStudents table? There would only be one image per student so could
the image path be stored in a field in tblStudents as opposed to a separate
table as in the demo mdb? Are we talking about significant changes to the
code in the demo database? Presumably I could not have a thumbnail showing in
each Student record - that would seem to defeat the object of the exercise.
Also, Im using Access 2002 on XP - will the demo code be compatible?

I would appreciate any advice you could give - just to give me an overall
picture of how to link things up. Although I may have to ask for more help
later - only fair to warn you!

Thanks, JohnB

 

[Larry Linson]

"JohnB" wrote

I find that I add around 2mb
for each thumbnail.

IIRC, my "rule of thumb" was "approximately 1 MB of thumbnail for each 25K
of original compressed JPG".

The external file approach works nicely, but does leave your images
"exposed" in files on the hard drive; the "BLOB" approach makes for a
somewhat larger database, but doesn't expose your image files to "external
influences". It is surprising, sometimes, that users may go in and delete a
lot of files because they don't know what they are!

These two methods do not work with a Continuous Forms view, but only in a
Single Record view. If you use the Zoom property on the Image Control, the
image will be resized to fit the control, maintaining the aspect ratio. If
you like, you could include a command button, or just a double click event,
to open a separate form with a larger Image Control. (That form doesn't even
have to be bound to the table -- you can pass it the path and filename in
the OpenArgs event.)

I don't think I've ever done a real-life Access application where it was
useful for the users to have a page of thumbnails from which they chose one
to enlarge, so this has never been a problem for me.

One caveat: to display the compressed files, you need to have the graphics
filters installed. They are included with Microsoft Office Professional, or
standalone Microsoft Word, but not with standalone Access. AND we are NOT
licensed to redistribute them with a database with runtime support.

Thus, these approaches are not "for everyone" but if you are creating an
application for your own use, or for distribution to users who all have
Office Pro installed, or even just Microsoft Word installed, they are very
useful.

There are various third-party controls that can be licensed for use in those
other cases -- from the free one at Stephen Lebans' site,
http://www.lebans.com, to some very, very expensive ones available from
vendors (as I don't particularly recommend the very, very expensive ones, I
won't name them). Pegasus Software used to have a moderately inexpensive
control called ImageN that got good reports from people who tried it (but I
haven't used it myself), and think it may have been superceded by another
product in their line.

Assuming your student form is in single record view, you can examine the
code in the OnCurrent event in the sample -- it is a simple matter of
setting the Picture property of your Image control. (Assuming you now use a
Bound Object Frame, you'll first replace it with an Image control.)

As far as I know, the demo code should run in Access 2000 on Windows XP. I
don't have a machine so configured, but I haven't heard any complaints that
it does not run. (Access 2000 is not one of my favorites, and the only copy
I have installed is on an old Windows 98 machine, which I don't use very
often.)

Larry Linson
Microsoft Access MVP

 

[JohnB]

Thanks for this Larry.

Ive now used the sample to add some Images to my mdb using the External File
Option. Ive managed to get the Images to link to my Student records by using
one of the sample forms as a subform - its a real 'cut and paste' job but it
does work, although it allows users to link more than one photo to each
student which I dont want. I'll hope to find a way of allowing them to
create only one Image record per Student later.

Anyway, as per your advice, Ive added an Image control to my students form
and put the following in the Student forms On Current event (I also added a
field txtImagePathAndFile to the form, which I'll make invisable)

Me![imgTheImage].Picture = Me![txtImagePathAndFile]

This works fine. As I open each student record, the relevant Image loads.
The problem is, when I open a record which has no Image saved for it, I get
a RunTime 13 error - Type Mismatch and pointing to my On Current code. Is
there a way of stopping this error message showing?

Thanks for all the help. JohnB

 

[Larry Linson]

I suspect, if you give some thought to the matter, you can relatively easily
prevent the user from adding multiple pictures per record -- one way might
be for the image table have a foreign key to the student record, which you
also use as the primary key of the picture table (e.g., no duplicates
allowed).

The problem is, when I open a record
which has no Image saved for it, I get
a RunTime 13 error - Type Mismatch
and pointing to my On Current code. Is
there a way of stopping this error
message showing?

Yes, create your own image for "no picture" and seed each record with the
path and file for that, then overlay it with the path and file of the actual
picture when available. Some like to use a one-pixel by one-pixel white (or
background color) image, but others prefer an image with text that says "No
Picture".

Larry Linson
Microsoft Access MVP

 

[Guest]

Hi Larry.

Thanks for this and all your other help. I think I'll leave you in peace for
now. I'll repost, perhaps with your name in the header, if I need further
help.

I appreciate your help. JohnB