We’re often asked how to store picture along with the records of an Access database application. The short and simple answer is DON’T. Storing images as part of the database recordset can lead to massive bloating of the database tables, as all pictures that are stored within the tables are converted to bitmap format for storage, and then duplicated when being processed by the database. There are regular posts on the MS newsgroups from people that have taken this approach, and then post saying that their 1 megabyte of data tables are now 50, or even 100 megabytes now they’ve added a few small .jpg images.
The way to solve this problem is to have the pictures stored in a separate folder, and have a field in the database that stores the path to the picture, and then have some sort of event that displays the picture on the form. Whilst this approach is a much better solution for database size and performance, it is a little more onerous in that all the pictures must be stored in a pre-selected directory before the database application is started, and should the pictures, and the database be moved at anytime then the links will need to be re-established. That said this is still the preferred method among Access database designers for picture storage and linking.
The Database
In this tutorial we will show you how to design a form that can handle picture input and display it on the form. The database will be used to store the details of various flowers, and we will design the database from scratch, it is then just a simple process of adapting the technique used for your own situation. This tutorial assumes you know how to perform basic Access tasks such as creating tables, forms, controls etc, and also how to change their properties. It also assumes you are conversant with the Visual Basic Editor, as we will use this extensively to add functionality to the form.
Step 1 – Create a new database and table.
- Open a new blank Access database, and in design view create a new table.
- Create three fields for this table with the following names, FlowerName, Colour, PicPath. All fields should be set as Text fields, and should be long enough to accommodate the information, 50 Characters should be enough for the first two, but you should extend the PicPath field to allow for more as full filenames may be quite lengthy.
- Exit design mode, and save this table with the name tblFlowerIndex.
Step 2 – Create the form for data entry
Using either the form design wizard, or purely in design mode, create a form based on the above table to look something like this
On the form you will need to create three text boxes that are linked to the three fields in the table. Also included is a picture object, which needs to be ‘unbound’. To achieve this you will need to place the picture object onto the form, and then temporarily assign a picture to this object. Once you have done this you will have to use the properties dialog to remove the picture name from the object. You will also need to give this control a name, call it ImgControl
Once the form is designed be sure to save it before we proceed to the next part of this tutorial where we will add some VBA code to the controls on the form to give it some functionality.
Click Here to proceed to the second part of the tutorial