Microsoft Office Tutorials and References
In Depth Information
4. Click the Next button to open the next Link Spreadsheet Wizard dialog box, in
which you choose a name for the linked table. You’ll use the default table name
5. Click the Finish button. A message box informs you that you’ve created a linking
table to the Excel worksheet file.
6. Click the OK button to close the message box and complete the linking steps. The
tblProduct table now appears in the Navigation Pane. The icon to the left of the
identifies the table as a linked table.
You can open and view the tblProduct table and use fields from the linked table in
queries, forms, and reports, but you can’t update the products data using the Panorama
database. You can update the products data only from the Excel worksheet.
Next, you’ll show Sarah how the worksheet and the linked table interact.
To update the Excel worksheet and view the data in the linked table:
1. Open Windows Explorer, navigate to and open the Level.02\Tutorial folder, right-
click Products , click Open With on the shortcut menu, click Microsoft Office
Excel , and then click the OK button. The Products workbook opens and displays
the tblProduct worksheet.
2. Click the Microsoft Office Access program button on the taskbar to switch to the
Panorama database, and then open the tblProduct datasheet. The fields and
records in the tblProduct table display the same data as the Excel worksheet.
3. Select Yellow in the first record’s Color column, and then type G . A warning sound
chimes, the message “This Recordset is not updateable” is displayed on the status
bar, and the value is not changed.
4. Click the Microsoft Excel program button on the taskbar to switch to the Products
workbook, select Yellow in cell F2, type Green , and then press the Enter key. The
value in cell F2 changes from Yellow to Green.
5. Click the Microsoft Office Access program button on the taskbar to switch to the
Panorama database. The first row’s Color field value is now Green, because this is
the newly changed value in the Excel worksheet.
You’ve completed your work for Sarah and her staff.
6. Close the table.
7. Click the Microsoft Excel program button on the taskbar to switch to the Products
workbook, save your worksheet change, and then exit Excel.
8. Close the Panorama database, make a backup copy of the database, open the
Panorama database, compact and repair the database, close the database, and
then exit Access.
If Excel has the worksheet
open at the same time
Access is displaying the
linked file’s data, you must
close the table in Access
before closing the work-
sheet in Excel, or Access
will display error values in
the linked file.
Knowing how to create charts, PivotTables, and PivotCharts and how to link to data
maintained by other programs will make it easier for Sarah and her staff to anlayze their
operations and to work efficiently in managing data.