Section 19.1. Case for Importing and Exporting

19.1. Case for Importing and Exporting

If you haven't thought much about importing and exporting, it's probably because you don't need to use these featuresyet. Many databases are completely happy living a quiet, solitary life. However, importing and exporting might come in handy for a few reasons. Sooner or later, one of these reasons will apply to you.

19.1.1. Understanding Exports

Exporting 's the easier part of the equation. Exporting's simpler than importing, because it involves moving information from a stricter storage location (the database) to one with fewer rules (another type of document).

Note: Exporting's a way to transfer a copy of your information to another location. The original copy always remains in Access. There's no point in changing the exported copy. Instead, if you need changes, make them in the database, and then perform the export operation again.

Here are some of the most common reasons people decide to export information:

  • You want to email some information to a friend . You don't want to send the Access database because your friend doesn't have a copy Access, or you want him to see only somenot allof the data

  • You're creating a presentation in PowerPoint . The easiest way to dazzle and convince your peers is to show them some impressive information from your database.

    Tip: Access stores huge volumes of information, which is often more than other programs can handle. You'd never be able to copy a table into a PowerPoint presentationat most, a slide can fit a handful of records. However, you might choose to show the results of a totals query (Section 7.3) that uses grouping to boil down the results to a few subtotals.
  • You want to analyze the information in Excel . Access is great for storing and managing your data, but it doesn't give you the tools to help you figure out what it all means. If you want to crunch the numbers with heavy duty formulas and slick charting features, it makes sense to move it to Excel.

Some programs are intelligent enough to pull the information out of an Access database all on their own. One example's Word, which provides a mail merge feature that lets you take a list of names and addresses from a database, and then use them to create mailing labels, personalized forms, or any other sort of batch paper-work. When using this feature, you don't need to perform any exportinginstead, you can just point Word to your Access database file. (For more information about Word's mail merge feature, see Word 2007: The Missing Manual .)

19.1.2. Understanding Imports

You need importing whenever there's information outside your database that belongs inside it. Suppose you create a state-of-the-art e-commerce database for your buffalo farm. However, some of your sales associates still fill out forms using an old Excel spreadsheet. Now, you need a way to get the information out of the Excel spreadsheet and into your database.

Tip: Your sales staff has let you down. They really shouldn't enter data into a document for another program. Instead, they should use a form that's designed for logging sales, as described in Chapter 12.

Import operations have two key challenges. The first's making sure the data fits the database's strict requirements. As you learned in Chapter 1, databases are rule-crazy, and they rudely toss out any information that doesn't fit (for example, text in a date field). The second challenge is dealing with information that doesn't quite line upin other words, its representation in the database doesn't match its representation in the external document. This headache 's more common that you may think.

In your database, you might use status codes (like 4302), while the spreadsheet you want to import uses status names (like High Priority). Or, you may need to break the information you're importing into more than one linked table, even though it's stored together in a single document. The customer order spreadsheet for your buffalo farm could include customer information (which corresponds to the Customers table) and order information (for the Orders table). Sadly, you don't have any easy way to solve these problems. If the external data doesn't match the representation in the database exactly , you'll need to change it by hand before or after the import operation.

Experts occasionally try to solve problems like these by writing Visual Basic code that reads the data and creates the appropriate records. (To do this, you'd need to use the DAO objects described in Section 17.4.5.) While the code approach is infinitely flexible, it can quickly become a nightmare to write and maintain, so avoid it if at all possible.

SQL Server and SharePoint: Two Special Cases

You won't consider two programs in this chapter.

SQL Server's the high- powered server-side database product described in Chapter 20. If your Access database is growing exponentially, you may decide to move your data to SQL Server. However, you don't use the ordinary export feature to do it. Instead, Access has a specialized "upsizing" tool to help you out, and you can learn about it in Chapter 20.

SharePoint's another industrial-strength server product that stores large volumes of information. But unlike SQL Server, SharePoint's designed to help teams of people share information and collaborate over networks or the Web. If you want to move information into (or out of) a SharePoint list, you'll need to take a look at Chapter 21.

Access 2007[c] The Missing Manual
Access 2007[c] The Missing Manual
ISBN: 596527608
Year: 2007
Pages: 153 © 2008-2017.
If you may any questions please contact us: