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

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

Some programs are intelligent enough to pull the information out of an Access database all on their own. One example is 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 paperwork. When using this feature, you don’t need to perform any exporting—instead, you can just point Word to your Access database file.

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 by using an old Excel spreadsheet. Now, you need a way to get the information out of the Excel spreadsheet and into your database.

Import operations have two key challenges. The first is 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 up—in other words, its representation in the database doesn’t match its representation in the external document. This headache is more common than 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 on Updating Stock Numbers.) While the code approach is infinitely flexible, it can quickly become a nightmare to write and maintain, so avoid it if at all possible.

Access lets you copy a selection of rows or an entire table to another program, without going through the hassle of the Export wizard. Access copies these rows to the Clipboard as an intelligent object that can convert itself into a variety of software-friendly formats. You can paste them as Excel cells, HTML text (the formatting language of the Web), or RichText (a formatting standard created by Microsoft and supported by all major word processors). Since HTML and RichText are so widely supported, you’ll almost never have a problem copying your rows into another program when you use this technique.

Here’s how to try it out:

Figure 23-2. Using cut and paste, you can transform a database table into a table in a Word document (shown here). Once you’ve pasted the content, you may need to fiddle with column widths to make sure it all looks right.

You can copy information from Access into another program easily enough, but you probably don’t expect to be able to do the reverse. After all, a database is a strict, rigorously structured collection of information. If you try to copy a table from a word processing program, then you’ll lack vital information, like the data types of each column. For that reason, Access doesn’t allow it.

However, Access makes a special exception for everyone’s favorite spreadsheet program, Excel. You can copy a selection of cells in Excel, and then paste them into Access to create a new table. This procedure works because Excel does distinguish between different types of data (although it isn’t nearly as picky as Access). For example, Excel treats numbers, dates, text, and TRUE/FALSE values differently.

Here’s how to use this feature:

Most of the time, you’ll import data that’s in one of these four common formats:

Access also provides several other, more exotic import choices. Most of them don’t appear until you click the More button. They include:

No matter what type of data you want to import, you’ll go through the same basic steps. Here’s an overview:

The following sections walk you through the specifics for two common data formats that need a few extra steps: Excel workbooks and text files.

To import from an Excel file, your data should be organized in a basic table. Ideally, you have column headings that match the fields in your database. You should trim out any data that you don’t want to import (like other cells under the table that aren’t a part of the table). You may also decide to remove values that are calculated using Excel formulas. If possible, it’s better to recalculate this information whenever you need it by using an expression in a query (Calculated Fields). That way, there’s no danger that your calculated values will become inconsistent or out of date.

Once you have a cleaned-up table of data in an Excel file, you’re ready to start the import process:

You’ll find some potential stumbling blocks when importing data from Excel. Blank values and fields, the commonest problems, occur when the Import wizard assumes there’s data in a part of your worksheet that doesn’t contain any information. (This could happen if there’s a cell with just a space somewhere on your worksheet, or even if you have a cell that used to contain data but that has since been deleted.) The best approach is to prevent as many problems as possible by tidying up your worksheet (for example, removing extra cells and unneeded columns) before you perform an import operation. But if some issues slip through, you may need to clean up the imported table by deleting empty fields and records.

Text files are the lowest common denominator for data exchange. If you’re using a program that creates files Access can’t import, then plain text may be your only avenue.

Once again, you start by choosing your file, and then choosing how you want to add the information to your database. Then, the Import wizard takes you through a few more steps:

Finally, in the last step, you need to enter the name of the table you want to create or add to. You can then click Finish (and, optionally, choose to save your import steps for later reuse).

Just as you can import information from other files and pop it into your database, you can also take the existing information and ship it out to another format. You’ll most often undertake this step to let some other person or program use your information without needing to go through Access.

When exporting your data, you can use all the same formats that you can use in an import operation, plus a few more. Here’s a rundown of the most popular choices:

To perform an export operation, follow these steps:

In some situations, you’ll find you need to perform regular import or export operations. You may need to dump the data from an Excel spreadsheet into your database once a week. Or maybe you need to produce a monthly PDF report with a sales summary. In these cases, it’s quite time-consuming to go through the entire wizard. This is especially true if you’re performing an import, because you might need to choose which columns you want to import, set the appropriate data types, and then adjust other settings the exact way you did the first time you performed the operation.

Fortunately, Access has a solution for times like these. You can save all the settings you chose in the Import or Export wizard and store them in your current database. Then, when you need to repeat the process, you can use these settings to do it with just a couple of clicks (with no brainpower needed).

To save your steps, just turn on the “Save import steps” or “Save export steps” checkbox at the end of the process the first time you import or export your data. You’ll need to choose a descriptive name for your settings, as shown in Figure 23-9, and then click Save Import.

Figure 23-9. Here, an import process is being saved for later use. You can fill in an optional description for this operation to help you remember what it’s all about. And if you’re using the popular Microsoft Outlook email program, then you can turn on Create Outlook Task to create an automatic reminder that tells you when it’s time to perform your import or export.

At some future point, you can rerun your import or export operation. If you want to repeat an import, then choose External Data→Import & Link→Saved Imports. To repeat an export, choose External Data→Export→Saved Exports. Either way, you get to the Manage Data Tasks window (Figure 23-10), at either the Saved Imports or Saved Exports tab. These tabs list the import and export operations you’ve saved for this database.

Here’s what you can do in the Manage Data Tasks window:

When you’re finished using the Manage Data Tasks window, click Close to get back to Access.

Figure 23-10. In this example, a single import operation has been saved. By clicking the file name, you can change it to another file, but you’ll have to type in the new path.

XML alone sounds pretty modest. People often describe it as a format for storing information. For example, instead of saving data in Word documents, Excel spreadsheets, or ordinary text files, you can save data in an XML file. This simplicity is deceiving, and two factors make XML really special:

Contrary to what many people believe, XML is not a data format (like HTML, the format used to create web pages). If XML were an ordinary data format, it wouldn’t be nearly as useful because, no matter how good a format is, it can’t suit everyone. For example, even though almost every company needs to create invoices, most companies wouldn’t be happy with a generic format for storing invoice information. One company may need to track customer names, while another might track customer ID numbers. The bottom line is that most companies need to store slightly different data in slightly different ways. That means a one-size-fits-all solution is pretty much always doomed to failure.

So if XML isn’t a data format, what is it? Technically, XML is a meta-language, which is a fancy way of saying that XML is a language for creating other languages. XML does this creating by setting out a few simple rules that let you build your own data format that’s just right for your data.

For example, Acme Company can build an XML format for invoices, and call it Acme-Invoice. Meanwhile, Budget Company can build its own XML invoice format and call it BudgetInvoice. Even though both these formats are designed to store invoice information, they can contain completely different kinds of data. XML’s flexibility is its strength.

At the same time, XML’s flexibility can create problems. Suppose a bank named Worldwide Green sets up a system to automatically process XML invoices in a specific format. The system works smoothly until Acme Corporation sends along its own homegrown invoice. Even though Acme’s invoice uses XML, it doesn’t conform to the XML that the bank expects, so it gums up the bank’s automated invoice-processing application. Suddenly, XML doesn’t look so useful.

The bottom line: XML holds the promise of universal data sharing—but if you don’t create some rules and follow them, then you’re left with a bunch of incompatible formats.

To get a better understanding of how to configure Access to handle XML, look at a simple example. Technically, you don’t need to know what XML looks like to use the XML features in Access, but the more you understand, the less confusing life will be. In this section, you’ll learn the three most important rules that shape all XML documents. If you already know a little about XML, feel free to skip ahead.

By the way, good news before you even start: XML is written in a text-based, human-readable format. So you can use a program like Notepad to crack open an existing XML file, and get a basic idea of its format and structure. You can even write an XML file from scratch by using Notepad. You can’t do the same with the average Access database, because it’s stored in a binary format that you can read only when you’re looking at the data in Access. (If you try to open a database in Notepad, you’ll see a jumble of indecipherable symbols.)

<?xml version="1.0" ?>

<Name>

</Name>

<Name>Patrick</Name>

<Name>Lisa Chen</Name>
<Age>19</Age>
<Name>Bill Harrison</Name>
<Age>48</Age>

<Person>
    <Name>Lisa Chen</Name>
    <Age>19</Age>
</Person>

<Person>
    <Name>Bill Harrison</Name>
    <Age>48</Age>
</Person>

<?xml version="1.0" ?>
<PeopleList>
    <Person>
        <Name>Lisa Chen</Name>
        <Age>19</Age>
    </Person>

    <Person>
        <Name>Bill Harrison</Name>
        <Age>48</Age>
    </Person>
</PeopleList>

<?xml version="1.0" ?>
<PeopleList><Person><Name>Lisa Chen</Name><Age>19</
Age></Person><Person><Name>Bill Harrison</Name><Age>48
</Age></Person></PeopleList>

As you’ve already learned, a file is one place you can store XML documents. But you can just as easily place XML documents in databases or other storage locations. In fact, sometimes XML data isn’t stored anywhere—instead, people just use it to send information between applications over the Internet. However, when you use XML with Access, you’re always using XML files (unless your company has created a custom solution by using the heavy-duty programming features in Access).

Most XML files have the extension .xml. For example, it makes perfect sense to take the person list document shown earlier and place it in a text file named PersonList.xml.

Another type of XML document is extremely important: XML schemas. Schemas are designed to solve a common problem—namely, defining the rules for a specific XML-based format. For example, a schema indicates the element names you can use, how you can arrange the elements, and the type of information each element can contain. An XML-friendly application can use the schema to verify that an XML document uses the right structure and contains the appropriate content. In an ideal world, every time a company created an XML format, they’d write an XML schema that defines it. (You probably won’t be surprised to learn this doesn’t always happen.)

To use a schema, you simply need to have a copy of it in a file. (Schemas themselves are complex and ugly and beyond the scope of what a typical office needs—or wants—to learn.) Usually, schema files have the extension .xsd.

XML is a great way to exchange data between different computer programs. But what does that have to do with Access, which already has its own perfectly good way of storing data? Here’s the deal: More and more companies today use XML to pass data back and forth. When companies exchange business orders, for instance, or news organizations post stories, or real estate firms list properties for sale, chances are they’re using an XML-based format. If you want to send your Access data to these systems, then you need a way to take it out of the specialized .accdb database format, and put it in clear-as-a-bell XML.

Unfortunately, the XML support in Access is still quite limited. The problem is that Access doesn’t let you pick the XML format you want. Instead, it creates a custom format that closely matches your table. Consider the table in Figure 23-11. (When exporting XML, you always export a complete table.)

Figure 23-11. Some sample data, ready for a new life in XML format.

When you export this table, Access creates an XML document that looks like this:

<dataroot>
    <Product>
        <ProductID>371</ProductID>
        <Name>Thin-Jam Hex Nut 7</Name>
        <ProductNumber>HJ-7161</ProductNumber>
        <SafetyStockLevel>1000</SafetyStockLevel>
        <ReorderPoint>750</ReorderPoint>
    </Product>
    <Product>
        <ProductID>372</ProductID>
        <Name>Thin-Jam Hex Nut 8</Name>
        <ProductNumber>HJ-7162</ProductNumber>
        <SafetyStockLevel>1000</SafetyStockLevel>
        <ReorderPoint>750</ReorderPoint>
    </Product>
    ...
</dataroot>

No matter what table you export, Access always follows the same rules:

There’s nothing particularly wrong with structuring XML in this way. However, since you can’t change the structure, you’ll run into trouble if you want to use another program that expects XML in a different format. For example, your program may expect the root element to be named <ProductRecords> instead of <dataroot>, or it may assume a slightly different nesting. Minor quibbles like these can completely derail an XML-processing application.

Sadly, there’s no way around this problem. To use Access XML, you must specifically design a program that recognizes this structure, or you must use another tool to convert the XML to the standard you really want. Access’s XML export feature is enough to get you started, but it doesn’t take your data all the way.

The same limitations appear when you import XML content. Access expects to find XML content in the rigid table-like format it expects. If you try to feed it a different type of XML, then you get an error.

Now that you’ve learned about XML and considered its limitations in Access, you’re ready to try it out for yourself. The following steps lead you through the process:

Access makes it just as easy to import XML data, provided it’s in the structure Access expects. To try it out, take the table you just exported, and then re-import it into a new database. Here’s how to do it:

If you want to repeat the import process another time, then choose “Save import steps.”