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,
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
- If necessary, select from the menu | .
- Open the list of panes and select:
- 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.
- 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
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.
your mouse pointer over the right edge of the column heading
TripID until the
pointer shape changes to the Resize Column shape
The column resizes to fit the widest item in the column, in this case the
- Repeat for the columns TripName,
DateEnded, and Cost.
Your window will almost certainly be bigger than the illustration!
Layout: Resize Column with Dialog
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
your mouse over the column selector Description.
The pointer changes to
Select Column shape.
on the column selector. The whole column is highlighted, which shows that
it is selected.
click on the selected column. A menu appears.
- Select The Column Width dialog appears.
the Column Width to 25 and click on OK.
- 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.
- If neccessary, select the Description column again.
the mouse over the selected column until it shows the Select shape
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
- 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.
- Drag the Description column back to the left of the Cost
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.
Move your mouse pointer over the bottom edge of the row heading until
the pointer changes to the Resize Row shape.
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
We will manage this better by creating a form in a later lesson.
- Move the mouse pointer over the bottom edge of the row heading
again until it changes to the Resize Row shape.
- Double-click. Nothing happens! There is no Best Fit for rows and
your resizing is not in the Undo list.
- 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.
the mouse pointer over the first row heading until the pointer changes to
the Select Row shape.
- Right click. The row is selected and a popup menu appears.
A small dialog window appears.
- Click in the box Standard Height. The height number changes
to 12.75 pts.
- 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!
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.
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
Retype the whole cell:
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
The whole cell is selected.
Retype the date as 8/7/2004.
Edit existing data:
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.)
Click between the 0 and the 5 in 2005.
the Delete key to remove the 5
and then type a 4 and press ENTER.
You have edited the DateEnded
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
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.
database programmer can change Access' behavior so that records are not
automatically saved. You must know your database!!
save data changes without leaving the record:
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
Select the third trip. (Click on the record
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!
Click on No. We need all the records
we can get for this exercise!
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.
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.
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.
Click in the Field Size box, where it
says Long Integer in the lower section. An arrow
appears at the right of the
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.
Click in each property at the bottom in turn.
If an arrow appears, click on it to see what the choices are.
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
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.
Click in the field
the menu select | . A new blank row appears above
If you select a number of rows and
then select | ,
you will get the same number of blank rows.
Type the field name PhotoLink as the Field
Select the data type
Type as the Description: Link to photos of trip
Datasheet: Add Data for New Field
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.
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.
Drag the border of the window wider, if you like.
In the record of the Kauai trip, in the PhotoLink field, type (or copy
Once again we have a field that is too narrow to show its
- 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!)
Press the down arrow on the keyboard twice. Your cursor should
now be in the PhotoLink field for the Disney World trip.
- Type (or copy and paste from here):
will be blue if you have not visited the page and purple if you have opened
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
- Right click on the hyperlink for Kauai.
- From the popup menu select . 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.
- Click on . The
URL in the text box is now selected.
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.
- Repeat for the other hyperlink, and use Disney as the text.