| You can work in Access with data from other programs in two ways: You can import it or you can link it. When you import a table, as you just did, you are awarding it full Access citizenship. In other words, it becomes indistinguishable from any other table in your database. The source document, whether residing in another Access database or an external environment such as Excel, is left untouched and unaffected by any changes you make in your new Access table. By contrast, a linked table attains only expatriate status in Accessit remains a citizen of its native program. You can view and edit the data of the linked table and even create a query based on it. However, you can't alter how the table and its fields are defined in its home environment. It naturally follows that, although you can delete the linked table in Access, you have no control over its longevity in its native program. You can set certain field properties for a linked table, but these affect how the data is displayed, not its inherent traits. Specifically, you can change the field's format and give it a caption, but you can't change important properties such as Validation Rule and Required. Even a default value is prohibited. Importing data obviously offers you more flexibility than linking. It also speeds performance. Linking is useful, however, when you or other users are also updating the data in another program. Here's an exercise that links an Excel spreadsheet to an Access database. 1. | If you haven't already done so, copy EmployeesLink.xls from the download file to your hard drive.
| 2. | Open the file and briefly review its fields. Close the file and close Excel.
| 3. | In the Database window, choose File, Get External Data, Link Tables.
| 4. | In the Link dialog box, set File of Type to Microsoft Excel (*xls). Navigate to the folder where you downloaded EmployeesLink.xls and double-click the file.
The Link Spreadsheet Wizard opens.
| 5. | With the First Row Contains Column Headings option checked, click Next.
| 6. | Edit the table name to tblEmployees. Click Finish and click OK in the message.
| 7. | In the Database window, select tblEmployees.
Note that there is an arrow next to tblEmployees, indicating that the table is linked (see Figure 13.24).
Figure 13.24. Linked tables are prefixed by an arrow in the Database window. 
| 8. | Click Open. Review the fields in the table.
The table has the same data and headings it did in Excel.
| 9. | Click View to switch to Design view.
You get a warning message that, because the table is linked, some properties can't be modified.
| 10. | Click Yes. Click in the Birth Date row of the Field Name column.
The message in the lower pane tells you that the property (the field name) cannot be modified.
| 11. | Press Tab to move to the Data Type column.
The message at the right half of the lower pane tells you, "This property [i.e., the field type] cannot be modified in linked tables."
| 12. | In Field Properties in the lower pane, click Format.
The message at the right tells you that you can edit the property. Because Format affects only appearance, not the underlying data, there is no problem changing the setting.
| 13. | Using the down arrow key, move from property to property to see which properties can be modified and which cannot.
Format, Input Mask, and Caption can all be edited, whereas most other properties cannot.
| 14. | Click View to switch to Datasheet view. In ID 5, edit the Last Name from Wood to Woodbridge.
| 15. | Close the table. Open EmployeesLink.xls.
| 16. | The change you made in the table has also been made in the spreadsheet file.
| 17. | Close the Excel file and close Excel.
|
TIP You can easily convert a linked table to a regular Access table. Copy the linked table and paste it. In the Paste Table As dialog box, choose either Structure Only (Local Table) or Structure and Data (Local Table). Click OK.
|
