Importing Information from an Excel Worksheet

Access works well with Microsoft Office Excel. You can import entire worksheets or a named range from a worksheet into either a new table (one that is created during the import) or an existing table. You can also import specific fields from a worksheet or range.

Excel is a good intermediate format to use when importing information that isn't set up to import directly into Access. For example, if you want to add or remove fields, combine or split fields, or use complex mathematical functions to manipulate data before importing it into Access, Excel is a great place to do it.

In this exercise, you will import customer information from an Excel worksheet into an existing table in an Access database.

1.	On the External Data tab, in the Import group, click the Excel button. The Get External Data wizard starts.
2.	On the Select the source and destination of the data page, click Browse.
3.	In the File Open dialog box, navigate to your Documents\MSP\SBS_Access2007\Chapter03 folder, click the 02_Customers workbook, and then click Open.
4.	On the Select the source and destination of the data page, select the Append a copy of the records to the table option, click Customers in the list, and then click OK. The Import Spreadsheet wizard starts. [View full size image] Important When importing information into an existing table, all the field names and data types must match exactly; otherwise, Access can't import the file and displays an error. If the structure matches but data in a field is too large or has some other minor problem, Access might import the record containing the field into an ImportError table, rather than into the intended table. You can fix the problem in the ImportError table, and then copy and paste the record into the correct table. On the first page of the wizard, you can browse the contents of any worksheets or named ranges in the selected worksheet. Sample data from the selected worksheet or named range appears at the bottom of the page.
5.	With Customers selected in the Show Worksheets list, click Next. [View full size image]
6.	Select the First Row Contains Column Headings check box if it is not already selected.
7.	Click Next, and then click Finish. Access imports the worksheet into the Customers table.
8.	On the Save Import Steps page, click Close.
9.	Open the Customers table to confirm that Access imported the customer list.

Linking to Information 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. You can usually link to information in any application from which you can import information. The only difference in the process is the option you select on the Select The Source And Destination Of The Data page of the Get External Data wizard. Rather than the Import or Append option, select the Link To The Data Source By Creating A Linked Table option. 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. Access indicates a linked table by an arrow to the left of the table icon.