Building the Database

In this section, you will build the database in an Access file separately from the user interface. You learned in Chapter 11 that you can realize performance improvements by separating the user interface from the underlying data. The database containing the data tables can be placed on the same or a different computer than the one containing the user interface. If you are the only user, then having both files on the same computer is fine. If you have others with whom you must share the application, then putting the database with the data tables on a file server and the user interface on each user’s workstation makes the most sense. In either case, you can make the database location configurable, so you can change the setup as your needs scale. This chapter will not discuss this option, but it is easily implemented in VBA.

Try It Out-Building the ProjectTrackerDb Database

Let’s now build the database that will store the data tables.

1. Create a new database by selecting the Office Button New. From the list of available templates, select "Blank Database." Specify ProjectTrackerDb for the file name, and click the Create button.

2. The tables for this database are shown in Figures 13-7 through 13-11. Note that for each table, the field size is specified in the Description field for your convenience. You also need to add primary keys as indicated in each table by the key icon.

   
   Figure 13-7

   
   Figure 13-8

   
   Figure 13-9

   
   Figure 13-10

   
   Figure 13-11

3. Add the tblProjects table as shown in Figure 13-7. This table is the master project table for storing project information. Note that the fields in this table map to the fields on the prototype screen that are not on the tabs.

4. Add the tblContacts table as shown in Figure 13-8. This table maps to the fields on the Contacts prototype form.

5. Add the tblProjectsComments table as shown in Figure 13-9. This table is used to store the multiple comments that can be entered for a given project. This table maps to the first tab of the Project Tracker screen prototype. The reason a separate table has to be used for the tabs is that multiple entries can be included. You cannot simply put a field in the main table that holds multiple contacts. In the world of database design; you illustrate these relationships (one to many, many to many, and so on) in separate tables for various reasons.

6. Add the tblProjectsContacts table as shown in Figure 13-10. This table maps to the second tab of the Project Tracker screen prototype.

7. Add the tblProjectsFileAttachments table as shown in Figure 13-11. This table maps to the third tab of the Project Tracker screen prototype.

8. At this point, your ProjectTrackerDb database should look like the one shown in Figure 13-12.

Figure 13-12

How It Works

You created five tables that will store the data for the Project Tracker application. These tables were created in a separate database from the user interface database to provide better support for multiple users. The tblContacts table is used to store data about each individual contact. The tblProjects table stores data about each project record. The tblProjectsComments, tblProjectsContacts, and tblProjectsFileAttachments tables can store multiple records for a particular project record.