Hands on: Access all images

Storing images in databases is getting more popular.

For example, I’m currently developing a database at Cambridge University which is storing digital images of Charles Darwin’s herbarium specimens.

We are using Access as the front end and SQL Server as the back end.

Ultimately these will be made available to anyone with a browser. And it’s not just academics, everyone is doing it.

People store ID pictures, images of products, the list is endless. So, what options do you have? There are several ways to store images in a database. I’ll illustrate them in Access but they are true (with variations) for a number of different engines.

Images in the database
Of course, just as text needs to be stored in a text field and dates in a date field, an image needs a special field type. In SQL Server it is called ‘Image’ but in other engines it can have other names including the glorious Blob (Binary Large Object) data type.

In Access images are stored in an OLE Object field. The pros and cons of this choice are very well summed up (for those who are interested in the detail) here.

One problem with saving images as OLE objects is that the Access database inflates faster than a fat cat’s salary. To quote from the article above: “However, this method can rapidly inflate the size of your database and cause it to run slowly. This is especially true if you store GIF and JPEG files because OLE creates an additional bitmap file that contains display information for each of your image files.

“Those additional files can be larger than your original image and thus bloat your database. Keep in mind that Access databases have a 2GB size limit. If you have a large number of images, you can reach that limit quickly.”

In other words, adding a 1MB image may well expand the database by considerably more. So, beware the bloat but, on the other hand, this is a very handy way of storing a limited number of images. And, of course, if you move the database, the images come too; which isn’t true in the next option.

Pointing to images
For this you still use the OLE Object data type but you choose the ‘link’ option when selecting the image (see the practical steps that follow). The image is not copied into the database; all that is inserted into the database is a link to the image.

This, again, has pros and cons. On the pro side, the database bloat is much reduced for obvious reasons. If you edit one of the image files that are stored on the disk, that change is visible if you look at the image from within the database; since the two are one and the same. This ‘feature’ can be either a pro or a con, depending on the functionality that you want.

However, if you move the Access file to another computer, the images don’t travel with it. It can also be difficult to update the link between the two since the pointer to the image (for example, C:\MyPics\Hols\ BrianBeingSilly.JPG), which must be stored somewhere in Access, is not readily available for editing.