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:
-
From the Start menu, select All Programs, Microsoft Office,
Microsoft Office Access 2007.
-
In the first Access Window, click on the Blank Database icon.
-
-
When the Blank Database Icon is selected, a new section of the window
appears at the right as below
-
-
Click on the folder icon in the File Name box and open the folder c:\files
If c:\files does not exist,, create it.
-
Change the filename to Weather.accdb (in all previous versions of Access,
this extension was .mdb)
- Then click Create
- Select: Tables option, then double click on , Create table in
Design view.
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:
-
Click anywhere in the row of City field
-
In the Field Properties below, Change the Field Size to 20
-
Change the Indexed property by Clicking on the No, then on the down
arrow and select YES(Duplicates OK)
-
Close the table by clicking the Office Button
then Save.
-
Save the table with the name Tornadoes Table.
-
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:
-
Click on the Datasheet View icon
which should give you
the view below
-
- Enter the data in the record shown below, Use the TAB key
to move to the next field.
- The logical field Hail is checked with the Mouse or by pressing the <Space Bar>. The check is a Yes.
- The date may be entered as June 11, 2008 or 06/11/2008 but in either case
will be converted to the mm/dd/yy format.
- The DamageCost may be entered as 350000 and Access will format
the number with the $ sign, commas and decimal point
- Now
you make up two other records of some appropriate data for each
field.
- Edit any field by selecting the field and over typing the data in the field
box.
- 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
- To see the structure of the table again, Click on the Design View icon
.
-
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..
- 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
- 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.:
-
Open the AccessLab2007 database by double clicking on
AccessLab2007.accdb
-
In the left pane under Tables, click ONCE on the Customer
table
-
Under the Create main tab, and in the Forms group, click on the
Form icon
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.
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..- Use the steps outlined below
to make the form look like the
following
- Deselect all the controls by clicking anywhere on the form other than on one
of the controls. or double click on any control.
- 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
- Hold the <Shift> key down as you click on the fields to be selected OR
- Click on the form anywhere near the control and drag across the controls
to be selected
- 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.
- 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.
- Move the State and Zip text boxes as above by dragging the handles in the
left corner of each text box.
- Select and delete the State and Zip text prompts.
- Similarly, move the remaining fields to their new positions on the form.
- Under the Design tab, select the Rectangle icon and drag a rectangle around
the bottom 5 fields and their prompts
- Change the background color of the form by right clicking on the form and selecting Fill Back Color and select a color.
- To Change the color of the color of the prompt icon in the rectangle
- Hold the <Shift > key down and click on each prompt in the rectangle
- RIGHT click anywhere in the selected area
- Select Font/Fore color and click on a color
- Change the form heading
-
Double click on the heading text box and change it from Customer to Customer Entry Form
-
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
- 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.