CSCI 107 Creating a Database in MS ACCESS
Updated 10/20/2008

We are a nation and a world of data. We must be able to manage all this data in some way. The usual method is by use of some Database Management System (DBMS) like Oracle, Sybase, Foxbase, dBASE, or Access. We will be using Microsoft's Access.  An Access database file can contain many different other objects, such as forms, tables, reports and queries.  It may seem odd at first to be saving only one file when you have created several different objects in the one database file. In this lab you will learn techniques for creating and updating a database table and creating a data entry/display form.


Before you begin this lab, delete AccessLab2007.accdb and Weather.mdb  in  C:\files if they exist.

I Creating New Database and a Table Object

We will create a database that could be used by a research group studying tornado activity. From the data entered, one could make various queries to help in predicting tornado touch downs. Like Word, Access also has predefined templates for commonly used databases. However, we will not use the Wizards or the templates and will create our own from scratch. Begin the creation of the new database and the first table:

  1. From the Start menu,  select All Programs, Microsoft Office, Microsoft Office Access 2007.
  2. In the first Access Window, click on the Blank Database icon.
  3.  When the Blank Database Icon is selected, a new section of the window appears at the right as below
  4. Click on the folder icon in the File Name box and open the folder c:\files If c:\files does not exist,, create it.
  5. Change the filename to Weather.accdb (in all previous versions of Access, this extension was .mdb)
  6. Then click Create
  7. Select: Tables option, then double click on , Create table in Design view.

  8. Damage
Similar to Mathematics where we use variables like X, Y and Z to represent numbers,  in database tables we use more descriptive variable names like First Name,  Address and City to represent data. City could be stored with particular cities like Asheville or Atlanta.

Unlike Mathematics where all the variables represent numbers, databases have several variable types such as: Text, Number, Currency, Date/Time, or Yes/No.

Using the example below: Create the structure of the database by typing in the field names, selecting the data type for each field from the drop down list, and typing in the description of each field. The field names are unique names which must begin with a letter followed by a combination of letters, numbers, spaces or punctuation. Text fields have a default width of 255 character, but ACCESS stores only the data you enter in the field

Indexes are used to display the data in order by a particular field. Some indexes allow  duplicates while others do not. To create an  index: on the City field:

  1. Click anywhere in the row of City field
  2. In the Field Properties below, Change the Field Size to 20

  3. Change the Indexed property by Clicking on the No, then on the down arrow and select YES(Duplicates OK)
  4. Close the table by clicking the Office Button   then Save.
  5. Save the table with the name Tornadoes Table.

  6.  
  7. When OK is clicked, click Yes to create the primary key. The primary key is used to uniquely identify each record in the table. We used the AutoNumber which is automatically indexed, but if we had not specified that as one of the fields you would have had to create a unique index on a selected field to create the primary key. This primary key is used by Access for searching and defining relationships between tables
II Enter Data: All we have done at this point is create the structure, but we have not entered any data. To enter data:
  1. Click on the Datasheet View icon which should give you the view below
  2. Enter the data in the record shown below, Use the TAB key to move to the next field.
  3.  Now  you make up two other  records of some appropriate data for each field.
  4. Edit any field by selecting the field and over typing the data in the field box.
  5. To delete a record, select the whole record by clicking in the small column to the left of the first field, then press the DELETE key
  6. To see the structure of the table again, Click on the Design View icon .
     
  7. In the Datasheet View, you can change the display order of the fields (columns) by pointing the to field name and dragging it to a new location. Drag the DamageCost field over the Windspeed field and release the button to move DamageCost before Windspeed..
  8. You can display the records (rows) in any order, by clicking any row in the field name to be sorted, then selecting one of the two sort icons  under the Home tab. These are only temporary sorts, unlike the indexes created above which are saved with the table. Sort on the DamageCost field
  9. Close the table by clicking on the X box in the table window or File, Close in the main menu
CHECK POINT: Open your Tornadoes table in the Design View and show your instructor your Design View and your Datasheet View
    After your instructor has checked your work, Right click close the database by selecting File, Close
III Creating a simple data entry/display form

Whereas you can enter and view data in the list form above, it is not very practical when you have many more fields than can be shown at once on the screen. So Access has a utility to create a form to display data in a much more "user friendly" way. We will use the Wizard to create a form for the Customer table and then customize the form. To save time you will download a database file, by RIGHT clicking on the file at the right AccessLab2007.accdb  and select Save Target as. Be sure to save the file to your C:Files directory. If the file already exits in C: \files, then overwrite it with your copy.
To eliminate a security problem caused by an update of the operating system on the CS server, you must select Explore in the Start Menu and right click  on the file AccessLab2007.accdb, select Properties, then  check the Unblock option. In the same file Explore window, you should be able to open the database as follows.:

  1. Open the AccessLab2007 database by double clicking on  AccessLab2007.accdb
  2. In the left pane under Tables, click ONCE on the Customer table
  3. Under the Create main tab, and in the Forms group, click on the Form icon 
  4. Access automatically creates one of the two forms below. If the Access window was NOT maximized, the first form will be created. If  the Access window was maximized, the second form is created.


     

    At the bottom of the forms are the navigational buttons to move from record to record. The buttons (in order) are First Record, Previous Record, Next Record, Last Record and New Record. 
      play with this control by clicking on the next and previous icons.

  5. Whereas the form generator creates a quick form, we want to "fix it up" by changing the widths to something more appropriate, move objects around and group related data. To do this we must change to the Design view by clicking the Design View Icon under the Home tab., then click on the Arrange tab and finally the  Remove icon . This last icon click removes the automatic options created by access and will allow you to move the objects (also called controls) around on the form..

  6. Use the steps outlined below to make the form look like the following

  7. Deselect all the controls by clicking anywhere on the form other than on one of the controls. or double click on any control.
  8. We don't need the CustomerID in this form, so select the its text prompt and its data field and then click the <Delete> key. You can select multiple objects on the form by either
    1. Hold the <Shift> key down as you click on the fields to be selected OR
    2. Click on the form anywhere near the control and drag across the controls to be selected
  9. Change the City text prompt by making two separate clicks on the City prompt (NOT the quick double click!) and then type City/St/Zip.
  10. Select the data text box City and grab the right middle handle and drag the field to a smaller width. Do a similar procedure on the State and Zip text fields as shown above.
  11. Move the State and Zip text boxes as above by dragging the handles in the left corner of each text box.
  12. Select and delete the State and Zip text prompts.
  13. Similarly, move the remaining fields to their new positions on the form.
  14. Under the Design tab, select the Rectangle icon and drag a rectangle around the bottom  5 fields and their prompts
  15. Change the background color of the form by right clicking on the form and selecting Fill Back Color and select a color.
  16. To Change the color of the color of the prompt icon in the rectangle
    1. Hold the <Shift > key down and click on each prompt in the rectangle
    2. RIGHT click anywhere in the selected area
    3. Select Font/Fore color and click on a color
  17. Change the form heading
    1. Double click on the heading text box and change it from Customer to Customer Entry Form
    2. Change the size of the font by RIGHT clicking in the heading text box, selecting Properties, then scroll down to change the Font Size to 18
  18. Finally to View the form, select the   icon.  Browse the records by using the navigation buttons  at the bottom of the form.

    CHECK POINT: Let your instructor view your completed Customer Entry Form.

    IV Close and save the form by RIGHT clicking on the CUSTOMER tab of the form and selecting Close and changing the form name to be   CustomerForm. Leave the database open and continue by clicking on this link Database Part 2.