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.
This lesson covers a lot of ground, but we are really only getting a taste for the various features that you can control for a table. You will do more in later lessons.
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
In the Datasheet View you can change the appearance of the datasheet (the layout) in a number of ways. Undo does not track these changes!
Save layout changes: Any time you save the table, layout changes are saved.
Dump all layout changes: If you try to close the table that has unsaved layout changes,
you will get a message asking if you want to save your layout. Choose No to get rid of all layout changes since the last time you saved.
Row height
All rows must be the same height.
Standard height is 14.25 points when the default font is
used, Calibri 11pts.
How to change: Drag divider between record selectors or right click a record selector at the left end of the row and select Row Height...
Column width
Standard width is 1", which is 11.75 characters when the default font is used, Calibri 11pts.
Columns do not have to be all the same width.
Best Fit widens the column to fit
the widest item currently visible in the column.
How to change: Drag right edge of column selector or right click column selector at the top of the column and select Field Width...
Best Fit: Double-click the right edge of a column selector to make the column width enough for the widest item in the column. Or choose Best Fit in the Field Width dialog.
Don't do this with fields that have lots of text!
Rearrange order of columns
Does not change order of fields in Table Design view.
How to change: Drag the column selector. A black bar appears between columns for the new location.
Cell formatting: Font, font color, font size, grid lines, background color for alternating rows
How to change: Home ribbon tab > Text Formatting tab group.
All text formatting applies to the whole datasheet.
Datasheet with default formatting (Access 2010)
Datasheet with new formatting for:
row height, column width, font, font size, color for alternating rows, column order
Hide/Show Field
Sometimes you don't really want to see all of the information in a table. You can hide fields and show them again later.
How to hide a field: Drag the right edge of the column to the left until the width of the column is zero OR right click the field and select Hide Fields from the context menu.
How to show a hidden field: Right click on any field and select Unhide Fields from the context menu. A list of all the fields appears with a check mark beside each one that is currently showing. Click the box for any hidden field you want to show and then click on OK.
Sort to rearrange rows: Unlike in a spreadsheet, you cannot rearrange
the order of the rows by dragging or cut and paste. You must sort the rows into a different order. In a table's datasheet view, you can click the column label to get a menu of sorting choices. But you can only sort based on one column. A query enables you to sort on multiple fields at once and also to combine fields together to create new values. We will cover queries in a later lesson.
Change default datasheet layout: You can change many defaults for
all data sheets in the Options dialog on the Datasheet tab.
If a datasheet is open, you will have to close it and reopen it for your
changes to be applied.
You have used two views for a table so far: Design and Datasheet. There are others. Different objects have different possible views. The table below shows the methods for a Table object.
Change Table Views: | ||
---|---|---|
Button on Home ribbon tab | Button on Status Bar | Context Menu: title tab |
Click the button icon to change to the view shown or click the arrow to open the list of all views for the current object. |
|
Right click on the object's title tab to open the context menu. |
![]() |
Step-by-Step: Manage a Table |
![]() |
What you will learn: | to open an existing database from the Open dialog to backup a database to resize columns with Best Fit to resize column with dialog to rearrange columns to change views to resize rows - drag and dialog to edit an existing record to undo changes to save a changed record to delete a record to add a new field to enter data in a new field: Hyperlink to change field properties: Hyperlink |
Start with :,
, Access open but no database open, mytrips-Lastname-Firstname.accdb from previous lesson
Backups: You should develop the habit of creating a Back Up for your database before making significant changes. Of course as your databases get larger, the backup process starts taking more and more time. But for our lessons, it won't take much time to create a backup. Having one can save you a LOT of time if you find you need to start the lesson over.
You can use a normal Open dialog to navigate to a database to open it. Your mytrips database is probably in the Recent list but you need to practice opening a database when it is not in the list.
With Access open, open the Open dialog.
![]() Office button > Open ![]() |
![]() File tab > Open ![]() |
![]() File tab > Open > Computer > Browse ![]() |
![]() File tab > Open > Browse ![]() |
Datasheet view is using the default layout because you did not save the changes in the last lesson.
The Trips table has 3 records.
To would certainly be handy to have a good copy of the current database to use if you need to start over. Access has an internal command to create a backup copy without disturbing the file you are currently using. Other programs like Word and Excel will let you use Save As to save a separate copy but then they load the new copy!
Back up this database now.
Pick a convenient location to save the Back Up. It does not have to be on your Class disk if you are short on space. Just be sure to remember where you put it!
Some of your columns are wider than needed and some are far too narrow to show everything.
Memo/Long Text fields like Description and Diary often contain far more text than the datasheet can display. Depending on where the text gets cut off in the display, you might not even realize that there is more text there.
You will
tinker with the columns widths and (in the next section) the row heights to
show more of the text in fields with Memo/Long Text data type. A better solution is to use a form, which you will do in a later lesson .
No Undo for Layout changes: If you make a mistake or change your mind about a layout change in Datasheet view, you will have
to recreate the layout yourself.
The
Column Width dialog lets you set the width of the selected column(s) to either a specific size in 'characters', the Standard width (which is 1 inch), or to use Best Fit to make each column wide enough to show the widest item in the column. One 'character' is the average width of characters in the current font and font size.
Be careful when choosing Best Fit. How wide is the widest item? A Text/Short Text or Memo/Long Text field may suddenly be very, very wide!
Select
The Column Width dialog appears.Point of Confusion: The menu command was Field Width but the dialog is titled Column Width.
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. Do you think changing the row height would help? You will find out shortly.
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.
The Pivot Table and Pivot Chart views are more advanced views. We will not work with these in Working with Databases. There is a lesson in Working with Numbers about Pivot Tables and Pivot Charts.
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.
Did your rows get tall 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 .
Drag the bottom edge of the row header upward to show exactly two lines of text and drop.
You may have to try several times.
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.
Click.
The clicked cell has a wide colored border, showing that it is the active cell.
The whole cell is highlighted to show that the whole cell is selected.
Problem: TripName is selected instead of DateStarted
The Select Cell shape was too far to the left.
Solution:
Try again, moving the mouse pointer to the dividing line on the right of the cell or the dividing line at the bottom of the cell.
Start typing the date as 8/7/2004.
Your typing replaces the existing cell contents.
Press the TAB key to enter the change.
The next cell to the right becomes the active cell.
There are several formatting changes going on here. Some depend on exactly what you just did, so yours may not quite match the illustration.
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.
The active cell is now the
Description cell for the Alaska trip.
Changes to records can be undone only until you change to a different record.
Click on Undo Current Field Record.
The data changes to the two cells are undone.
[Depending on exactly what you have done, you may be able to undo only the last cell changed instead of the whole record.]
The Redo button did not light up! You cannot redo data changes. Did you expect that?
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
With the active cell still Description in the Alaska trip row, watch the Status Bar while you 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? Maybe not. For small tables on a fast computer the process
could be too fast to show the message.
Warning: A
database programmer can change this behavior so that records are not automatically saved. You would likely have a special button to click or a key combo to use to deliberately save the record. You must know your database!!
To
save data changes without leaving the 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! For this lesson you will practice deleting, but please do not actually delete any of these lovely records.
Follow the directions below very carefully or you will have to re-enter data and, even then, the ID numbers will be off for the rest of the lessons. That is not a horrible problem, if you understand where the difference came from.
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!
Important: 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 or re-enter the records into it. Awkward!
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 or change its data type.
Click on the Design View button
on the
Status Bar 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.
In the lower pane, click in the Field Size box, where it says Long Integer.
An arrow
appears at the right of the
property.
Click on the new arrow.
A list of
possible values opens. An AutoNumber has only 2 choices.
At the right of the properties, there is a brief description of the selected property.
Click in each property at the bottom in turn.
If a down arrow
appears, click on it to see what the choices are. When a button with 3 dots
appears,
click on it to see the dialog for the property.
Property = Indexed: The more records in your database, the more important it is to index the fields that you use to sort or filter. This will greatly speed up those actions. It is really, really important that a field that is a primary key be indexed and not allow duplicates.
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. Adding the photos themselves would balloon the size of the database tremendously! Let's add a field for such a link.
Click on the field DateStarted.
On
the Table Tools: Design ribbon tab, click on the Insert Rows button.
A new blank row appears above DateStarted.
Inserting Multiple Rows: If you select a number of rows and
then click on the Insert Rows button,
you will get the same number of blank rows.
Type the field name PhotoLink as the Field Name.
Select the data type Hyperlink.
Type as the Description: Link to photos of trip .
Press the TAB key to move out of the cell.
The Options button appears just below your typing after you exit the property.
Note: If you select this property later, the Options button will not appear unless you have edited the text.
Click the button.
A list appears with options for the action you just performed. In this case, there are just two.
Click on 'Update Status Bar Text everywhere PhotoLink is used'.
You will see a message box, 'No objects need to be updated.' That's because you do not have any forms at all yet.
Apparently Access copies the Description to the form when the form has a control for this field. When you edit the Description property, Access kindly offers to update all of the forms that used it. Sweet!
A message box appears
telling you that you must save the
table before you can switch to datasheet view.
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, if you adjusted the window size.
Drag the border of the window wider or maximize the window, if you like.
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.
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):
http://jegsworks.com/Lessons/images/trips/disney/disney.htm
Link colors: The link
will be blue if you have not visited the page and purple if you have opened
it.
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.
From the context menu select
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 editing.
Click on .
The dialog Edit Hyperlink opens.
It shows the Address at the bottom of the dialog and the text that will show in the datasheet at the top, Text to display. These do NOT have to match!
Click the Save button
in the Quick Access Toolbar to save your table.
Your changes to records are saved immediately when you leave the record. Your changes to the table's layout
are saved only when you save the table.