Dave Heppenstall |  Contact

Lab 3 4 5 6 7 8 9 10 |  » 

Access

14 February 2008

This tuitorial is an update to the official Lab Six web page. It is also an update to the official supplemental Access Lab Notes page.

We'll need a file for today's lab; open the Course FTP Server (Access Labs folder) and download the student.accdb file to your USB drive.

On This Page:

• Create a New Relational Database
• Designing the Table
• Populating the Table with Data
• Creating Forms
• Importing External Data Tables
• Running Queries

Create a New Relational Database

Start Microsoft Access.

Click the main menu button and select New as follows. A new panel will appear in the main Access window as shown. Click on the Browse button as indicated and save the new blank database file in a folder on your USB drive. Finally, click on the Create button.

    

The following window will appear, the Datasheet View. This view shows us the data contents of the one table in our database. Note the icons on the bottom-right corner of the view, these icons switch between Datasheet View and Design View.

Designing the Table

Switch to Design View by clicking on the currently deselected icon as indicated in the diagram above. You will be asked if you want to save your table, click Yes:

The following window will appear, the Design View:

In this view, we can edit the design and properties of the table. First, we shall specify table parameters: field names and data types. In addition to the previously existing "ID" field, add the following fields: Last Name, PO Box, Residence and Telephone. The data types of the new fields will be automatically set to "Text" by default.

Use the General tab of the Field Properties panel to specify additional parameters. For each field, specify the Field Size as follows: Last name (15), PO Box (6), Residence (12), Telephone (8). In addition, set the Data Type of the ID field to Number instead of AutoNumber.

The primary key for the table, as indicated with this icon: "" is the main reference field which uniquely identifies a record (table row) from other records.

Populating the Table with Data

Switch back to the to Datasheet View. Once again you will be asked if you want to save your table, click Yes.

Add some data to your table. In this view, we can enter data into the database the same way we enter data into a spreadsheet. After entering some data, our table might look like this:

We can also insert and delete rows (records) the same way as we do in Excel:

Creating Forms

We use Forms to create a more attractive method for entering data. Open the Form Wizard by taking the following steps: Click the Create menu tab, click More Forms and select Form Wizard:

In the first Wizard panel, add all the fields in the table by clicking >>. Click Next.
In the second panel, choose Columnar. Click Next.

 

In the third panel, select any design style you like. Click Next.
In the final panel, choose "Open the form to view or enter information".

 

We can now use this form to enter new data. Click on the New (Blank) Record button as follows:

Enter some data into this form. After you have entered data into the fields, save this record by hitting CTRL-S. When you return to the Datasheet View, hit F5 to refresh the view of your table to include the new record we just entered.

Importing External Data Tables

Here is where we will need the file we downloaded earlier. Click on the External Data tab menu and select Access from within the Import group as follows:

In the dialog box which appears, click Browse to select student.mdb from your USB drive. Select the first option to, "Import tables, queries, forms, reports, macros, and modules into the current database."

The Import Objects dialog box will appear; showing you the contents of the external file. Highlight "Book" and click Ok to import this table.

The "Book" table has been added to your list of tables in your new database file.

Running Queries

Now we're going to use Access to run queries on the Book table we just imported. Click on the Create menu tab and select the Query Design option as follows:

The Show Table dialog box will appear. Select the "Book" table and click Add; the table will be automatically added in the background. Click Close.

A new tab, "Query1" is now visible in the main window as follows. The top pane contains the table we are performing a query on, the bottom pane contains fields for us to define our query.

First, select the fields we want to see in the result view:

In the "Book Number" column, enter "100" into the criteria field. Right-click on the "Query1" tab and select SQL View to take a look at the raw SQL query text instruction. Select Datasheet View to view the query results.

    

Some other criteria searches you could try are using wildcards, such as "Well*" or "*ell*". If you enter specifically, "[Enter a value]" into the criteria field, you will be prompted by Access to manually specify a value when you open the Datasheet view to look at results. Finally, you can use mathematical operations such as ">10" or ">02/02/96 and <01/01/98".

Lab 3 4 5 6 7 8 9 10 |  »