Instead of importing data into an Access database from another program, you can leave the data in the other program and link to it. Although working with data that is stored in your own database is faster, safer, and more flexible, sometimes linking is preferable.
The most common reason for linking to data in another Access database or a different program is because you don’t own the data. Perhaps another department in your organization maintains the data in a SQL database, and they are willing to give you permission to read the tables and queries but not to change them. Other reasons are security and ease of data distribution.
Important
If you link to a file on a local area network (LAN), be sure to use a universal naming convention (UNC) path, rather than a mapped network drive. A UNC path includes the computer name as well as the drive letter and folder names, so it is less likely to change.
In this exercise, you will link a table in the GardenCo database to a table in another Access database and then link to a named range in an Excel worksheet.
USE the GardenCo and the LinkDatabase databases and the LinkWorksheet worksheet in the practice file folder for this topic. These practice files are located in the My DocumentsMicrosoft PressAccess 2003 SBSImportingLink 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 File menu, point to Get External Data, and then click Link Tables.
In the Link dialog box, navigate to the My DocumentsMicrosoft PressAccess 2003 SBSImportingLink folder.
Click the LinkDatabase file. Then click Link to display the Link Tables dialog box.
Click Shippers as the name of the table that you want to link to, and click OK.
The dialog box closes, and a table named Shippers1 is added to the database window. (Access adds 1 to the table name because the GardenCo database already contains a table named Shippers.) Notice that the table’s icon has an arrow to its left, indicating that its data is linked, rather than stored in the database.
Open the table to confirm that it contains a list of shipping companies, and then close it.
Repeat step 1 to open the Link dialog box again.
In the Files of type list, click Microsoft Excel.
Navigate to the My DocumentsMicrosoft PressAccess 2003 SBSImportingLink folder, click LinkWorksheet, and then click Link.
The first page of the Link Spreadsheet Wizard appears, in which you can browse through all the worksheets and named ranges in the selected spreadsheet.
Select the Show Named Ranges option, and in the list of ranges, click SpecialCustomers. Then click Next.
Leave the First Row Contains Column Headings check box cleared, because this particular named range doesn’t have column headings, and then click Next.
Leave the default table name, click Finish, and then click OK when the message box appears.
A new table, named SpecialCustomers, is added to the database window. The table’s icon has an arrow to its left to indicate that it is a linked table, but the icon itself has an Excel logo instead of an Access logo.
Click each linked table, click the Delete button, and then click Yes to confirm the deletion.
CLOSE the GardenCo database.