In this chapter, you will learn how to: |
|
Not many people enjoy typing information in a database, so one of your goals when designing a relational database is to structure the tables in such a way that the same information never has to be entered more than once. If, for example, you are designing a database to track customer orders, you don’t want sales clerks to have to type the name of the customer in each order. So you need a customer table to hold all the pertinent information about each customer, and you can then simply reference a customer ID in each order. If information about a customer changes, you only have to update it in one place in the database: the customer table. In this way, the only item of customer information in the order records (the ID) remains accurate. An added benefit of this system is that you reduce the confusion that can result from typographical errors and from having the same information appear in different formats throughout the database.
Good database design saves keystrokes when you’re entering new information and maintaining the database, but even more time and effort can be saved in another way. As part of The Microsoft Office System, Microsoft Office Access 2003 can easily share information with the other programs in The Microsoft Office System. It also makes it easy to populate a database by importing information in numerous other formats. If the information that you intend to store in an Access database has already been entered into almost any other electronic document, it is quite likely that you can move it into Access without retyping it.
If your information is still being actively maintained in another program and you want to bring it into Access to analyze it, create reports, or easily export it to another format, you should consider linking your Access database to the existing information in its original program rather than importing the information. When you link to data in another program, you can view and edit it in both programs, and what you see in Access is always up to date.
Many companies that store accounting, manufacturing, marketing, sales, and other information on their computers have discovered the advantages of sharing this information within the company through an intranet, or with the rest of the world through the Internet. With Access, you can speed up this process by exporting the information stored in a database as Hypertext Markup Language (HTML) and Extensible Markup Language (XML) pages.
In this chapter, you’ll import information stored in various formats into the GardenCo database. You’ll also export some of their data to several standard formats. After all this importing and exporting, you will experiment with viewing and updating information in another program by linking to it.
See Also
Do you need only a quick refresher on the topics in this chapter? See the Quick Reference entries in Chapter 3 Getting Information Into and Out of a Database.
Important
Before you can use the practice files in this chapter, you need to install them from the book’s companion CD to their default location. See "Using the Book’s CD-ROM" for more information.
Access works well with Microsoft 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 quite right. 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 information about The Garden Company’s customers, which is stored in an Excel worksheet, into the Customers table in the GardenCo database.
BE SURE TO start Access before beginning this exercise.
USE the GardenCo database and the Customers worksheet in the practice file folder for this topic. These practice files are located in the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportExcel 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 Import.
In the Files of type list, click Microsoft Excel.
Navigate to the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportExcel folder, click Customers, and then click Import.
Access displays the first page of the Import Spreadsheet Wizard.
On this page, you can browse the contents of any worksheets or named ranges in the spreadsheet you just selected. You can scroll horizontally and vertically to view the worksheet’s columns and rows, which are displayed in the lower pane.
With Customers selected in the list of worksheets, click Next.
Select the First Row Contains Column Headings check box, and then click Next.
The background of the top row changes to gray, and when you scroll up and down, the top row no longer scrolls.
Click In an Existing Table, click Customers in the adjacent drop-down list, and then click Next.
Important
When importing 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 desired one. You can fix the problem in the ImportError table, and then copy and paste the record into the correct table.
Click Finish to import the file.
Access informs you that the file was imported.
Click OK to close the message box, and then double-click Customers to open the table and confirm that Access imported the customer list.
Close the Customers table.
CLOSE the GardenCo database.