Access Basics:
Manage a Table

Step-by-Step: Manage a Table

---

What you will learn:

to open an existing database from the task pane to resize columns with Best Fit to resize column to specific width to rearrange columns to resize rows to edit an existing record to save a changed record to delete a record to add a new field to enter data in a new field to change field properties

---

Start with: Access open but no database open, mytrips.mdb from previous lesson

Open an Existing Database from Task Pane

Your recently viewed files are listed in one of the task panes:
    New File pane 
       Getting Started pane

1. If necessary, select from the menu  View | Task Pane .
    
2. Open the list of panes and select:
   New File
       Getting Started.
    
3. Click on mytrips.mdb.
   The selected database opens.

   Problem: You do not see mytrips.mdb in the list:
   You either have not done the previous lessons that created this database or you have opened enough databases since then to move mytrips.mdb off of the list.

          Solution: Either create the database - previous lesson
   OR, choose Open on the File menu instead, and navigate to where you saved mytrips.mdb and open it.
    

   

4. Double-click the Trips table to open it.

Datasheet view is using the default layout.
Trips table has 3 records

---

Datasheet Layout: Resize Columns to Best Fit

Some columns are wider than needed and some are far too narrow to show everything. Memo fields like Description and Diary are a problem in Datasheet view. They usually contain far more text than the datasheet can display. You can tinker with the columns widths and (in the next section) the row heights to show more text. A better solution is to use a form, which you will do in a later lesson.

The Undo feature does not keep track of layout changes. If you make a mistake or change your mind about a layout change, you will have to recreate the layout yourself.

1. Move your mouse pointer over the right edge of the column heading TripID until the pointer shape changes to the Resize Column shape .
    
2. Double-click. The column resizes to fit the widest item in the column, in this case the heading itself.
    
3. Repeat for the columns TripName, DateStarted, DateEnded, and Cost.
    

   

   Your window will almost certainly be bigger than the illustration!
    

---

Datasheet Layout: Resize Column with Dialog

The Column Width dialog lets you set the width to a specific size, or to choose the Standard width (15.6667 characters, which is 1 inch for the default font) , or to choose Best Fit.

Be careful when choosing Best Fit. The column will widen enough to show all of the widest item in the column. A text or memo field may suddenly be wider than the screen!
 

1. Move your mouse over the column selector Description. The pointer changes to the Select Column shape.
    
2. Click on the column selector. The whole column is highlighted, which shows that it is selected.
    
3. Right click on the selected column. A menu appears.
    
4. Select  Column Width...
   The Column Width dialog appears.
    
5. Change the Column Width to 25 and click on OK.
   
6. Click on the Save button to save your layout changes.
    

Looking at the new layout, it is clear that you are not going to be able to size all of the memo fields wide enough in Datasheet view to see all of the text. Perhaps working with the row height would help.

---

Datasheet: Rearrange Columns

Initially, a datasheet shows the columns in the order of the fields in the table's Design View. You can drag columns to rearrange them. This does not change the order in the Design View.

1. If neccessary, select the Description column again.
    
2. Move the mouse over the selected column until it shows the Select shape .
    
3. Drag to the right until a black bar appears at the right edge of the Cost column and drop.
   
   The whole column moves to the new position, to the right of the Cost column.
    
4. Switch back to the Table Design View and verify that the fields remain in the original order. Changing the position in the datasheet does not change the positions in the Design View.
    
5. Drag the Description column back to the left of the Cost column.

---

Datasheet Layout: Resize Rows by Dragging

Making each row taller will let you see more text in each field since the text will automatically wrap to the cell size. You can drag with the mouse or use the Row Height dialog.

Remember, the Undo feature does not apply to layout changes.

1. Move your mouse pointer over the bottom edge of the row heading until the pointer changes to the Resize Row shape.
    
2. Drag downward and release the mouse button.
   All rows get taller. More of the memo fields can now be seen.

   

   Still not enough to show all of the text in the Description field ! The Diary text is even longer.
   We will manage this better by creating a form in a later lesson.
    

3. Move the mouse pointer over the bottom edge of the row heading again until it changes to the Resize Row shape.
    
4. Double-click. Nothing happens! There is no Best Fit for rows and your resizing is not in the Undo list.
    
5. Drag the bottom edge of the row header upward until 2 lines of text is showing in each field and release the mouse. All rows change height at the same time.
    
6. Move the mouse pointer over the first row heading until the pointer changes to the Select Row shape.
    
7. Right click. The row is selected and a popup menu appears.
    
8. Select  Row Height...  A small dialog window appears.
    
9. Click in the box Standard Height. The height number changes to 12.75 pts.
    
10. Click on OK. All rows resize to be 12.75 pts. tall.
     
    Your table should be back to the original layout but if it is not, don't worry. The next step will take care of it!
     
11. Click on the Close button in the Title bar of the datasheet. 
    A message box appears asking if you want to save the changes in the layout of the table.
     
12. Click on NO to discard the changes and continue to close the table. Do not close the database.
     

---

Datasheet Data: Edit Existing Record

Mistakes happen! Happily, it is easy to edit database records. Many of your usual editing methods will work, such as Backspace and Delete keys, Copy and Paste.

Later you will create a form based on this table. We will wait until then to edit those Memo type fields.

Sometimes the hard part is selecting what you want to change.

Retype the whole cell:

1. Move your mouse pointer to the left edge of the cell DateStarted for the Alaska cruise until the pointer changes to the white cross , the Select Cell shape.
    

2. Click. The whole cell is selected.
    

3. Retype the date as 8/7/2004.
    

Edit existing data:

1. Press the TAB key. The selection moves to the next cell to the right. This time the text is selected rather than the whole cell.
   (Yes, it is a subtle difference, but sometimes it is an important difference.)
    

2. Click between the 0 and the 5 in 2005. 
    

3. Press the Delete key to remove the 5 and then type a 4 and press ENTER.
   You have edited the DateEnded to 8/14/2004.
    

---

Datasheet: Save Changes to Record

To save the changes from your editing, all you have to do is move to another record. You can TAB or use arrow keys or just click in another record

1. Press the down arrow key. The selection moves into the next record below.
    
   When you leave a record, all of your data changes are automatically saved!!

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

A database programmer can change Access' behavior so that records are not automatically saved. You must know your database!!

To save data changes without leaving the record:

• from the menu,  Records | Save Record

• from the keyboard, SHIFT + ENTER.

---

Datasheet: Delete a Record

To remove a record from the database is easy enough. To restore a deleted record is not usually possible. Be sure you want to delete!

1. Select the third trip. (Click on the record selector.)

   
    

2. Press the DELETE key.
   A Confirmation message window appears, pointing out that you are about to delete a record and that you will not be able to undo this action!
    

3. Click on No. We need all the records we can get for this exercise!

Problem: Clicked Yes and deleted the record
Whoops indeed. You cannot get the deleted record back. You must create a new record with the same information. The TripID value will be 4 for the new record. AutoNumber fields do not reuse a number even though the record was deleted. The only way around this is to create a new table and copy the records into it. Awkward!

---

Table Design: Inspect Values

Table Design includes the fields with their data types and properties. After you create a table, you may find that you need an additional field. Or perhaps you need to increase the field size.

1. Click on the Views button on the toolbar to switch to Design view.
   The Table Design view opens to show the list of fields. The first field is selected, so its properties are listed in the bottom of the window.

   

2. Click in the Field Size box, where it says Long Integer in the lower section. An arrow appears at the right of the property.
    

3. Click on the new arrow. A list of possible values opens. At the right of the properties, there is a brief description of the selected property. An AutoNumber has only 2 choices.

   

4. Click in each property at the bottom in turn. If an arrow appears, click on it to see what the choices are.
    

5. Change to the next field, TripName, and continue to inspect the properties that appear and the choices for each property. Be sure to read the text at the right.
    
   A button with 3 dots indicates that there is a dialog or wizard for the property.

---

Table Design: Add Field

Many people are taking digital photos now and storing their vacation pictures on their computer or online. It would be useful to have the link to the pictures right here in the Trips table. Let's add a field for such a link.

1. Click in the field DateStarted.
    

2. From the menu select  Insert |  Rows . A new blank row appears above DateStarted.
    
   If you select a number of rows and then select  Insert |  Rows , you will get the same number of blank rows.
    

3. Type the field name PhotoLink as the Field Name.
    

4. Select the data type Hyperlink.  
    

5. Type as the Description: Link to photos of trip
   
    

---

Datasheet: Add Data for New Field

1. Click on the Views button on the toolbar to change to Datasheet view. The button shows the Datasheet icon while you are in Design view and vice versa. A message box appears telling you that you must save the table before you can switch to datasheet view.
    

2. Click on Yes to save the changes to the table's design. The datasheet opens. Its window is the same size as the design view window.
    

3. Drag the border of the window wider, if you like.
    

4. In the record of the Kauai trip, in the PhotoLink field, type (or copy and paste):
   http://jegsworks.com/Lessons/images/trips/kauai/kauai.htm
    Once again we have a field that is too narrow to show its contents.
    

5. Click on the link. (You must be connected to the Internet). A page of thumbnail photos should open in your browser.
    
   Problem: Link fails
   Solution: Check your typing. If you copied and pasted, right click on the link in the datasheet and select Edit Hyperlink from the popup menu. The Edit Hyperlink dialog appears. The hyperlink is in the text box at the bottom. Verify that it is correct or make any needed changes. Once it looks correct, close the dialog. Hover over the link. The popup tip shows the URL. Does it have a line break? If so, delete the link, and type it in. (This link break showed up when my daughter did the lessons on a freshly installed copy of Access. Did not happen for me. I have not found a reason for it!)
    

6. Press the down arrow on the keyboard twice. Your cursor should now be in the PhotoLink field for the Disney World trip.
    

7. Type (or copy and paste from here):
   http://jegsworks.com/Lessons/images/trips/disney/disney.htm

   

   The link will be blue if you have not visited the page and purple if you have opened it.
    

---

Table Design View: Change Properties of Hyperlink Field

Did you notice how long the hyperlink is and how small the column is? Happily there is a way to change what is showing in the PhotoLink column to make it more manageable.

1. Right click on the hyperlink for Kauai.
    
2. From the popup menu select  Hyperlink . The a submenu cascades into view.
   Notice the various handy commands here. It is hard to edit a hyperlink in a datasheet directly. The Edit Hyperlink dialog is the best place for changes.
    
3. Click on  Display Text . The URL in the text box is now selected.

   

4. Type Kauai in the Display Text box. Now the link will show in the datasheet as Kauai but clicking on it will still take you to correct URL.
    
5. Repeat for the other hyperlink, and use Disney as the text.