Access Basics:
Manage a Table

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

As you work with the tables that you create, you will find that you need to make changes. You may want to change the order of the columns. You may need to increase the field size for a text field like LastName or for a number field like NumberOnHand. You will certainly want to add new records and remove records from time to time.

So we have three categories of changes to make:

  • Layout  - Datasheet View: Change appearance of datasheet

  • Data - Datasheet View: Add/remove records; edit data

  • Fields - Design View: Add/remove fields; edit field names, data types, and properties

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access Objects Arrow: subtopic open
    Icon: StepTable: Design View
    Icon: StepTable: Datasheet View
    Icon: StepManage a Table
    Icon: StepSort & Filter a Table
    Icon: StepQuery Wizard
    Icon: StepAutoForm
    Icon: StepAutoReport
    Icon: StepData Access Page
        About Printing
    Icon: StepPrint Objects
    RelationshipsTo subtopics
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports


Changing Datasheet Layout

In the Datasheet View you can change the appearance of the datasheet (the layout) in a number of ways:

  • Row height
        All rows are the same height.
        Standard height is 12.75 points when the default font is used, Arial 10pts.
  • Column width
     Standard width is 1", which is 15.667 characters when the default font is used, Arial 10pts.
        Format | Column Width... | Best Fit widens the column to fit the widest item in the column.
  • Rearrange order of columns
        Does not change order of fields in Table Design view.
  • Font, font color, font size, borders, background

Rearranging rows: Unlike in a spreadsheet, you cannot rearrange the order of the rows by dragging or cut and paste. You must use a query to sort the rows into a different order.

Message: Do you want to save changes to the layout of table...?Save layout changes: You can save the datasheet at any time to save layout changes. If you try to close the table after making changes, you will be prompted as to whether you want to save your layout/design changes.

Dialog: Options | DatasheetChange default datasheet layout: You can change the defaults for all datasheets in the Options dialog on the Datasheet tab. (Tools | Options | Datasheet)
If a datasheet is open, you will have to close it and reopen it for your changes to be applied.

Icon Step-by-Step 

Step-by-Step: Manage a Table

 Icon Step-by-Step

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:
    Icon: Access 2002New File pane 
    Icon: Access 2003   Getting Started pane

  1. If necessary, select from the menu  View | Task Pane .
  2. Open the list of panes and select:
    Task Pane: Getting Started (2003) - mytrips.mdbTask Pane: New File (2002) - mytrips.mdbIcon: Access 2002 New File
    Icon: Access 2003    Getting Started.
  3. Click on mytrips.mdb.
    The selected database opens.

    Icon: TroubleProblem: 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.

    Database mytrips: Trips table selected

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

Trips table with 3 records

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.

TipThe 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. Datasheet: Resize ColumnMove your mouse pointer over the right edge of the column heading TripID until the pointer shape changes to the Resize Column shape Pointer: Resize column.
  2. Table Datasheet View: Column resizedDouble-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.

    View: Datasheet (Trips table) - columns resized

    Your window will almost certainly be bigger than the illustration!

Datasheet Layout: Resize Column with Dialog

Dialog: Column WidthThe 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. Mouse pointer changes to Select Column shape when over the column headingMove your mouse over the column selector Description. The pointer changes to Pointer: Select Column the Select Column shape.
  2. Column is selectedClick on the column selector. The whole column is highlighted, which shows that it is selected.
  3. Right Click Menu: Column WidthRight click on the selected column. A menu appears.
  4. Select  Column Width...
    The Column Width dialog appears.
  5. Dialog: Column Width - width is 25 pts.Change the Column Width to 25 and click on OK.
    Description column resized to 25 pts. wide
  6. Click on Button: Save 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. Selected column with pointer in Select shape, ready to moveMove the mouse over the selected column until it shows the Select shape Pointer: Select.
  3. Black bar at edge of column where the new location will be when you drop.Drag to the right until a black bar appears at the right edge of the Cost column and drop.
    Column moved.
    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 shapePointer: Resize Row.
  2. Drag downward and release the mouse button.
    All rows get taller. More of the memo fields can now be seen.

    Table Datasheet View: Rows resized

    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.
     Row height increased for all rows
  6. Right Click Menu: Row HeightMove the mouse pointer over the first row heading until the pointer changes to Pointer: Select Row the Select Row shape.
  7. Right click. The row is selected and a popup menu appears.
  8. Dialog: Row HeightSelect  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. Message: Do you want to save changes to the layout of table 'Trips'?Click on Button: Close 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. Access window with mytrips.mdb open, Trips table selectedIcon: Class diskClick 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. Pointer in white cross shapeMove your mouse pointer to the left edge of the cell DateStarted for the Alaska cruise until the pointer changes to the white cross Pointer: White Cross , the Select Cell shape.

  2. DateStarted selectedClick. The whole cell is selected.

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

Edit existing data:

  1. DateEnded selectedPress 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. DateStarted and DateEnded after editingPress 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... Status Bar: Message area = 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.

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

TipTo save data changes without leaving the record:

  • from the menu,  Records | Save Record

  • Icon: Keyboard 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.)

    Trips table with third record selected

  2. Message: You are about to delete 1 record(s)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!

Icon: TroubleProblem: 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. Frowning smiley face 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 Button: Design (2003) 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.

    View: Table Design - Trips

  2. Click in the Field Size box, where it says Long Integer in the lower section. An arrow Button: Down 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.

    Table Design: Field Size for AutoNumber

  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 Button: 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. Table Design View: inserted blank rowFrom the menu select  Insert |  Rows . A new blank row appears above DateStarted.
    TipIf 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
    Table Design View: Photolink

Datasheet: Add Data for New Field

  1. Message: You must first save the tableClick on Button: Views - Datasheet (2003) 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):
     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.
    Icon: TroubleProblem: Link fails
    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):

    Trips datasheet with PhotoLinks

    TipThe 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.

    Right Click Menu: Hyperlink | Display Text

  4. Photolinks now show as hyperlinksType 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.

LessonsWorking with Databases Previous Page Next Page

Teachers: Request permission to use this site with your class
Copyright 1997-2012 Jan Smith   <>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012