Pictures in an Access table

[Stephen White]

Am I right in thinking that it is not possible to format a field in an
Access table to hold a picture (say a jpg), so that the pictures will be
displayed when the table is displayed (or at least when the results of a
query based on the table are displayed).

I want to catalogue a set of pictures with fields for their properties
in such a way as to allow the sort of searches that Access queries
allow. I thought Access would allow me to do this, but on looking into
it I cannot see how.

I have Access 2007 running on a machine with Vista Home and Access 2000
on a machine with Windows XP Professional.

 

[Allen Browne]

There are several ways to do this.

Option 1. Link
==========
1.1 Create a text field in your table.

1.2 For each record, enter the full path to the graphic, e.g.:
C:\MyPath\MyPic.jpg

1.3 In form design view, click the Image control (Control chunk of the
Design tab of the ribbon.) Then select the text field from the Field List
(at right), and click on your form. (You don't have to choose a picture if
Access asks you to.) This adds an image control to your form, with its
Control Source set to read from the text field. Consequently it shows the
image in your form for each record (even in a continuous form.)

This option works only in Access 2007.

Option 2: OLE Object
===============
You can create a field of type OLE Object, and insert a file into each
record. The advantage is that the graphic is actually in the database file.
The disadvantage is that it bloats the size of the database.

Earlier versions of Access uncompressed the image to store it, and so
bloated really badly. Access 2007 can store the compressed image, so the
bloating is nowhere near as bad. Still, it is undesirable if the images are
large, or if you have many of them.

Option 3: Attachment
===============
In Access 2007, you can create an attachment field, and attach several
images to any record.

This is probably not what you want, as they don't show each time you move
record (though it would be possible to set up a subform to do that.)

The other issue is that the Attachment type is a Multi-Valued Field, and
causes other issues/limitations if you use it.

Option 1 would be my recommendation.

 

[Stephen White]

Many thanks for this.

It's stupid of me, but I find I was mistaken when I said I had Access
2007. I confused it with something else.

I would very much appreciate it, therefore, if you could confirm that
Option 2 is the only one that should work with Access 2000? With the
size of the pictures and the number and size of the records I have in
mind, I don't think the lack of compression would be so much of a
drawback.

 

[Allen Browne]

My recommendation for A2000 would still be option 1.

This won't work for a continuous form, but in Form View you can load the
image into an image control in the form's Current event.
http://www.mvps.org/access/forms/frm0030.htm

 

[Stephen White]

Thanks very much for this.

After a bit of trial and error, I have got it working.

 

[Larry Linson]

Am I right in thinking that it is not possible to format a field in an
Access table to hold a picture (say a jpg), so that the pictures will be
displayed when the table is displayed (or at least when the results of a
query based on the table are displayed).

No, you are incorrect in that thinking. Your Access database can handle
images in a number of different ways. 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.

And, Stephen has an ActiveX control available that works in cases where the
graphics filters are not available -- and works better, I've heard, than the
OLE Object and OLE Controls.

Please note that there are other approaches, e.g.,
Application.FollowHyperlink, but those, like OLE and Bound OLE Frames (which
from the symptoms you describe, are what you were / are using) still leave
you "at the mercy of the imaging software registered for the filetype").

Finally, Access 2007 has enhancements that, reputedly, eliminate the
database bloat long associated with OLE Objects and Bound OLE Frames. But,
it still leaves you relying on the software registered for the image type,
which may not be as "cooperative" as we'd like in creating our display. It
also has the capability of having a variable number of Attachments to a
Record, which may be helpful. But, as I have not done much with it yet, I
certainly wouldn't suggest you rush out and replace an earlier version --
not until the first Service Pack, at least.

Larry Linson
Microsoft Office Access MVP