Creating a Table and a Form to Store Images

From NeoWiki

Suppose that our birder decided she or he wanted to also store pictures of particular birds in this database. It is possible for Base to store images, using the Image [LONGVARBINARY] field type. The tutorial below demostrates how to do that.

It should be noted, however, that storing large images and/or lots of images in an odb file can bloat the file to a very large size and create problems. Another approach is to store the locations of the image files in the database. That technique, however, goes beyond the scope of this tutorial--at least for the moment.

Contents 1 Create A New Table 2 Establishing Relationships 3 Create The Data Entry Form 4 Adding an Image Control 5 Entering Data 5.1 Test Data 6 Notes 7 External Links

Create A New Table

1. In the main database document window, click on the Tables icon in the left hand column.
2. Double click on the Create Table in Design View icon in the Tasks section.
3. In the Table Design window, create the following fields

   Pic_ID - Integer - Autovalue: Yes; Autoincrement Statement: IDENTITY

   DateTaken - Date - Entry Required: Yes; Format Example: as you desire I reccommend a YYYY format.

   BirdPictured - Text [VARCHAR] - Entry Required: Yes;

   Pic_Male - Yes/No [BOOLEAN] - Default Value: No; (this will reduce NULL issues)

   Pic_Female - Yes/No [BOOLEAN] - Default Value: No;

   Pic_Juvenile - Yes/No [BOOLEAN] - Default Value: No;

   Picture - Image [LONGVARBINARY] - Entry Required: Yes

   PicFieldTripID - Integer - AutoValue: No; Entry Required: No

   LinkToFile - Text [VARCHAR_IGNORECASE] - Entry required: No; Length: 500

   Note: The LinkToFile field is included on the assumption that the tutorial will eventually be expanded to cover turning a field's entry into a clickable hyperlink.

4. You may also enter field descriptions if you like
5. Control-click or right-click on the empy "cell" to the left of the Pic_ID field. Choose Primary Key from the contextual menu that appears.
6. Click on the Save button on the toolbar (the disk drive image) or hit Command-S. Name the table Bird_Pictures.
7. Close the Table Design window.

Note: Unlike other field types, images cannot be added directly to a table. Images can only be entered through a form.

Establishing Relationships

1. In the main database document window, you should still have Tables selected in the left hand column. If not, click it.
2. Go to the Tools menu and select Relationships.... The Relation Design window opens.
3. Click on the Add Tables button on the toolbar. (The blue table)
4. In the Add Tables window, select Bird_Pictures and click Add.
5. Close the Add Tables window by clicking on the red circle in the upper right hand corner of the window.
6. Resize the Bird_Pictures mini window so you can see all the fields.
7. Drag the PicFieldTripID field in the Bird_Pictures table to the FieldTrip_ID field in the Field Trips table.
8. Drag the EnglishName field in the AOU Birdlist table to the BirdPictured field in the Bird_Pictures.
9. If desired, drag the table windows around so the relationship lines are easier to see.
10. Click on the Save and close the Relation Design window.

Create The Data Entry Form

To create this form, we will invoke the wizard directly from the table. We are doing so here only to demonstrate the technique. The form can also be created in design view. (And, in point of fact, we will need to edit the form in order to use it to add images to the database).

1. In the main database document window, you should still have Tables selected in the left hand column. If not, click it.
2. Control-click or right-click on the Bird_Pictures table and choose Form Wizard from the contextual menu.
3. In the Field Selection pane of the Form Wizard window, click the >> button to move all the fields over to the Fields in the Form column.
4. Click Next>
5. In the Subform pane, don't change anything. Just click Next>
6. In the Arrange Controls section, choose an arrangement. I chose In Blocks-Labels Above, the option the furthest to the right.
7. Click Next>
8. Leave the Set data entry section settings as they are (This form is to display all data).
9. Click Next>
10. In the Apply Styles section, choose a color and field border style.
11. Click Next>
12. In the Set Name section, name the form Bird Pictures Data Entry and click on the Modify the form radio button
13. Click Finish

Adding an Image Control

1. If you clicked Modify the form at the end of the previous section, the Bird Pictures Data Entry form will have opened in design view. If not, you need to open it for editing by right-clicking on the name of the form in the main database document window.
2. You will note that there is not a field labeled Picture, despite the statement in the wizard that a image control would be created. This is a known OpenOffice.org bug. Fortunately the image control can be added in design view.
3. Make sure that the Form Design toolbar is on. If it is not, go to the View menu and choose Toolbars and then Form Design.
4. Click on the Form Navigator button on the Form Design toolbar. It is the usual form icon with a small Navigator (compass) symbol above its lower right hand corner. The Form Navigator window will open.
5. Click on Main Form in the Form Navigator window. You will see a list of fields

   Note that using the Form Navigator is one way of quickly seeing what types of controls you have on your form. You can replace control types and edit properties by control-clicking on a field listed in the Navigator.

6. Find the Add Field button on the Form Design toolbar, It looks like a light blue table one cell a darker blue. There is a plus sign in the upper right hand corner of the icon. The Add Field window will appear and you will see a list of fields. (Note that this list appears only if you have first selected the main form in the Navigator window)
7. Drag the Picture field from the Add Field window to your form, and place it where you want.
8. Adjust the size of the field as you like. You may need to Ungroup the label and the image control to reposition the label field relative to the image control field. Do so by going to the Format menu and choosing Group and finally Ungroup.
9. Adjust field placements, label text and so forth as you like. (You will notice, for example, that the color and font of the label for the Picture field does not match those created by the wizard).
10. You may wish to replace the Bird Pictured text box with a combo box linked to the EnglishName field in the AOU_Birdlist table. See Using Combo Boxes in a Form
11. Save your changes by clicking on the Save button of the toolbar or by pressing Command-S

Entering Data

Now it's time to test the form we have made.

1. Click on the Design Mode On/Off button to turn off design mode and shift to data entry mode
2. To test the data, enter the pictures below along with their accompanying data into the form.

   You will need to right-click or control-click on the images and choose the Save Image As or similar command to download them to your computer. The images are each less than 30 kb. They were taken by the original author of this article, and may be freely used for any non-commercial purpose.

3. To enter a picture, double click on the empty picture field in the form. An Open dialog appears. Navigate to the location of the picture, select it, and click Open

Test Data

1. Bird Pictured: House Finch

   

   Date: 01/01/1900

   Field Trip ID: 0

   Male

2. Bird Pictured: Rose-breasted Grosbeak

   

   Date: 06/13/1900

   FIeld Trip ID: leave blank

   Male

Notes

• Once you have imported an image with the form, that image will probably show up in design view, even though no other field content does. This does not affect the design or use of the form.
• You cannot view images in the tables, only in forms.
• As mentioned above, keeping lots of images--or even a few large image files--in your odb file can cause bloat and instability. If images are an needed in a database, make sure that the file size for each image is as small as possible. I used the free ImageWell to quickly resize my photos for this article. Doubtless there are other utilities that can do so as well.
• If you want to add file locations now, you may want to check out the following three scripts by Joe Weaks for getting the url of a file. For instructions for their use and more information, see this thread in the Accordance forums

  Using a file picker dialog

  A droplet application

  A Quicksilver plug-in

External Links

Information in these links was used to create this article. OpenOffice.org Forum thread on images in Base
Trinity thread on using images in Base

Other tutorials and links Sheepdog Guides tutorial