Tables are the core database objects. Their purpose is to store information. The purpose of every other database object is to interact in some manner with one or more tables. An Access database can contain thousands of tables, and the number of records each table can contain is limited more by the space available on your hard disk than anything else.
For detailed information about Access specifications, such as the maximum size of a database or the maximum number of records in a table, click the Ask A Question box at the right end of the menu bar, type Access specifications, and press the key.
Every Access object has two or more views. For tables, the two most common views are Datasheet view, in which you can see and modify the table’s data, and Design view, in which you can see and modify the table’s structure. Clicking the View button toggles the view of the open table between Datasheet and Design views. You can also click the down arrow to the right of the View button and select a view from the drop-down list.
When you view a table in Datasheet view, you see the table’s data in columns (fields) and rows (records).
If two tables have one or more fields in common, you can embed the datasheet for one table in another. With the embedded datasheet, which is called a subdatasheet, you can see the information in more than one table at the same time. For example, you might want to embed an Orders datasheet in a Customers table so that you can see the orders each customer has placed.
In this exercise, you will open existing tables in the GardenCo database and explore their structure in different views.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSKnowAccessTables folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the Objects bar, click Tables.
On the toolbar at the top of the database window, click the Details button.
A description of each of the objects listed in the window is displayed to the right of its name.
Click the Maximize button in the upper-right corner of the database window.
The database window expands to fill the Access window. Note that the first three items in the Name column are not tables; they are shortcuts to three commands you can use to create a new table.
Click the Restore Down button.
The database window shrinks.
Click the Categories table, and then click the Open button at the top of the database window to open the table in Datasheet view.
This datasheet contains a list of the categories of products sold by The Garden Company. As you can see, there are fields for Category ID, Category Name, and Description.
Click the plus sign to the left of the record for the Bulbs category.
Clicking the plus sign expands an embedded subdatasheet. The category records from the Categories table and product records from the Products table are displayed simultaneously.
Click the minus sign to the left of the Bulbs record to collapse the subdatasheet.
On the File menu, click Close to close the Categories table. If you are prompted to save changes to the table layout, click Yes.
You can also close a window by clicking the Close button in the window’s upper-right corner. When an object window is maximized, this button is called the Close Window button to avoid confusion with the Close button at the right end of the Access window’s title bar. Be careful to click the correct button, or else you will quit Access.
Double-click Orders to open the table in Datasheet view.
The navigation area at the bottom of the window indicates that this table contains 87 records and that the active record is number 1.
Move the selection one record at a time by clicking the Next Record button several times.
The selection moves down the OrderID field, because that field contains the insertion point.
Move directly to record 40 by selecting the current record number, typing 40
, and pressing .
Close the Orders table, clicking No if you are prompted to save changes to the table’s layout.
Double-click Products in the list of tables to open it in Datasheet view.
Notice that this table contains 189 records.
On the toolbar, click the View button to switch the view of the Products table to Design view.
In Datasheet view, you see the data stored in the table, whereas in Design view, you see the underlying table structure.
Close the Products table by clicking its Close button. If prompted to save changes to the table layout, click No.
CLOSE the GardenCo database.