Jan's Working with Databases

Step-by-Step: Table Datasheet View

---

What you will learn:

to open an existing database from Recent list to open a table in Datasheet view to add records to a datasheet to save a record to navigate from record to record and field to field

---

Start with: , Access open but no database open, mytrips-Lastname-Firstname.accdb from previous lesson

Open an Existing Database from Recent Databases

1. Open the list of Recent Databases:

   Access 2007: Office button > list is on the right
   
   Access 2010: File tab > Recent > Recent Databases
   
   Access 2013, 2016: File > Open > Recent
   

2. Click on mytrips-Lastname-Firstname.accdb, which will be the first document in the lisit if you have just finished the previous lesson.

   The database opens with one object showing in the Navigation Pane, the Trips table.
    
   Problem: Do not see mytrips-Lastname-Firstname.accdb in the list:
   You either have not done the previous lesson or you have opened enough databases since then to move the database off of the list.
   Solution: Either create the database - previous lesson
   OR, Choose Open on the menu instead, and navigate to where you saved mytrips-Lastname-Firstname.accdb and open it.

   Problem: Security Warning appears
   Solution: Click on the button Enable Content.
   

---

Open a Table

1. Double-click the table Trips in the Navigation Pane.
   The table displays in Datasheet view.

   Your field names show as column headings.

   Now you can enter some records. Don't get too excited! This is a tedious part of working with databases.

---

Add a Record

Now you can put in your first record. It's not much fun working directly in the datasheet, but it can be done! The TAB key is your friend here! It will move you to the next field to the right.

1. Try to type the number 1 in the first field, TripID, which is selected when the table opens.
   You should hear the computer's error sound and a message shows on the Status Bar.

   

   Fields with the data type AutoNumber automatically put in a number, but only after you have finished entering data for that record.

2. Press the TAB key. The cursor moves from the TripID field to the next field to the right, TripName.
   
3. Type the data below into the matching field. Use the TAB key to move on to the next column.
   Note: The datasheet columns cannot show all of the text in the memo fields. That is OK! It's still there. Just keep typing. Later we will create a form that will make this easier to see.

   Your datasheet window is probably not wide enough to show all of the fields at once.
   

   TripName	Kauai
   DateStarted	2/26/1990
   DateEnded	3/5/1990
   Description	Convention. Flew TWA from Nashville to Honolulu, then Hawaiian Air to the island of Kauai. Stayed at the Westin Kauai.
   Cost	$950.00
   Travel Agent	Arranged through company sponsoring the convention.
   Diary	Just us two. Beautiful weather. Rented car and drove around the island, well… as far as you can get on a road! Lovely canyon park - Grand Canyon of Hawaii. Smelled and looked just like the Great Smoky Mountains. Who knew! Flew over to the Big Island and and took a helicopter tour of the volcano. It was erupting with smooth lava flow that fell into the sea! Such heat and steam!!

4. When you finish entering the Diary field text, press the TAB key to move to the next record.

   

   The TripID is now showing 1 for this first record.

---

Add Another Record

A table with only one record is not of much use. You need to add some more trips!

1. Press the TAB key to move to the second field, TripName.
   
2. Type the following in the matching fields to create a new record:
    

   TripName	Alaska cruise
   DateStarted	8/7/2005
   DateEnded	8/14/2005
   Description	Norwegian Star, port Seattle. Flew in on Southwest through Dallas.
   Cost	$5,500.00
   Travel Agent	Jorge Martinez, World Travel Inc, jmartinez@worldtravelinc.net
   Diary	Just us two. Rainy to start but the Inside Passage was smooth and beautiful. Saw Ketchikan, Juneau, and Skagway. Jumping salmon right by the ship. Mendenhall Glacier in Juneau was so cool! We took a boat out to look for whales and saw three different kinds.

Some columns are too narrow to show all of the text. Some are wider than needed. You will work on that in the next lesson.

---

Save Records

Your new records are saved as you go!

1. Save the changes by moving to another record.
   You can TAB or use arrow keys or just click in another record.

   When you leave a record, all of your data changes are automatically saved!!

   The Status bar has the message Calculating... while it is saving the record. Did you see it? For small tables the process can finish too fast to show the message.

Commerical databases may not save automatically! A database programmer can change this behavior so that records are not automatically saved. Know your database!!

To save data changes without leaving the record:
        SHIFT + ENTER

---

Add Another Record

1. Type the following in the matching fields to create a new record:
    

   TripName	Disney World
   DateStarted	6-4-03
   DateEnded	6-11-03
   Description	Drove to Orlando. Stayed at Disney Polynesian Resort.
   Cost	$5,000.00
   Travel Agent	none
   Diary	Left 2 hours late due to a flat tire. Arrived in Orlando in a rain storm. Reservations were not correct, but the desk clerk was very cooperative and straightened it out. No yelling required! Kids slept like logs but woke up at first light. So excited!!

Did you notice? When you type the date as 6-4-03, it is automatically reformatted to the style mm/dd/yyyy.

Trips table after entering three trip records

Problem: Date did not reformat as mm/dd/yyyy (like 6/4/2003)
You did not assign the Date/Time data type to the field.

The illustration shows the effect of assigning Text/Short Text data type to the Date Ended field instead of Date/Time. The numbers remain exactly the way they were typed because they are treated as text.

Solution: Return to Table Design view and make corrections to the data type. You may see warning messages. Changing a data type after records are created can be tricky. You may have to re-enter the dates that were not correctly formatted.

---

Navigating Records

There are several methods for navigating between fields and between records. Now that you have some records in your table, you can do a little practice.

1. Practice moving from field to field with the TAB key and the other key methods listed at the top of this page.
2. Practice moving from record to record with the navigation buttons at the bottom of the Datasheet window.
3. Close the table by clicking its Close button at the far right of the datasheet tab, but do not close the database.
4. Close the database without closing Access.
   (Office button or File > Close)