Exporting Data Using the Interface | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

Exporting Access objects using the interface is a fairly simple process. You can choose from two methods. The first is very easy: Just select the item you want to export, specify your export options, and save the file. The second method is an automated process called Office Links that exports objects to Word or Excel for specific uses. This section introduces you to both methods.

tip - Export a single object quickly

For a really quick export of a single database object to another Access database, open both databases to their Database windows, and simply drag the database object from one Database window to the other. A copy of the object is created in the target database.

Access lets you export any type of database object. Each type of database object has its own selection of export formats (as listed in Table 17-1).

InsideOut

Unfortunately, you can export some database objects to formats that don’t work very well. In most cases, if you think about it, you’ll realize why. For example, you can export a form to an Excel worksheet, but Excel doesn’t have any built-in capabilities for displaying form controls, so you can’t expect to see your form with controls, color scheme, and all in the new worksheet. What you’ll see instead is a strangely formatted worksheet, perhaps with overly high rows and a dark background color that makes the text difficult to read.

The main consideration when you export data from Access to another format is whether you intend to export just the data or export a formatted interface object such as a form or a report. If you’re exporting data (by far the most common situation), you select a table or query to export and then select a data format such as another database program, a spreadsheet program, or a delimited text file. (The comma-delimited text file format is a least-common-denominator export file type because it can be imported by almost every type of program.)

If you intend to export a form, report, or data access page to another Access database, you’ll have no problems, but exporting any of these objects to other file types can yield surprising—and sometimes unacceptable—results. Data access pages can be exported only to other Access databases or saved as data access pages files. Forms have several export options available in the Export dialog box, but the Excel, HTML, Text File, and Rich Text options don’t preserve any of the form’s appearance—they simply export the data to a columnar format.

Reports do better than forms for HTML and Rich Text exports (but about the same for Excel and Text File exports—you get only the data). A report exported to HTML format emerges as a set of separate HTM files, one per page, and without the report’s formatting, although it does preserve the report layout to some extent. When a report is exported to Rich Text Format (RTF), most of its formatting is preserved, but there are some exceptions, as noted in the section "Using Office Links for Common Exporting Tasks."

For an alternative method of exporting a report that preserves its appearance well, see the sidebar "Exporting a Report to Adobe PDF Format."

If you want to export data using the basic menu selection sequence, follow these steps:

In the Database window, click the object type you want to export—for example, Tables or Queries. (These objects work best when exporting data.)
On the right side of the Database window, select the name of the table or query that contains the data you want to export.
Choose File, Export. The Export dialog box will appear. (See Figure 17-1.)
Figure 17-1. You specify export options in the Export dialog box.

note

The name of the Export dialog box will vary, depending on the type of database object you’ve selected to export.
Navigate to the folder in which you want to save the exported file.
In the Save As Type drop-down list, select the file type to which you want to export the element. Table 17-1 provides an overview of the export file types supported by Access.
In the File Name text box, type the name of the file.
If you want your data to have the same font when it’s exported, select the Save Formatted check box. This check box is available only for selected file formats. The AutoStart check box will become available; click it if you want the newly created file to launch automatically after you export it.

note

The Save Formatted check box that’s available for some export types preserves the font used in Access. For example, if you’re exporting from a query in Datasheet view and the datasheet is formatted as MS Sans Serif 8 pt, if you export to Excel and have selected the Save Formatted check box, the same font will be used when the data is inserted into the Excel worksheet, rather than the default Excel font. This option doesn’t preserve any other formatting.
Click Export. Access will save the object in the export format you selected.

Table 17-1. Export choices available through the interface

Data type	Extension(s)
Access databases	MDB, MDA, MDE
Access projects	ADP, ADE
dBASE 5	DBF
dBASE III	DBF
dBASE IV	DBF
HTML documents	HTML, HTM
IIS 1-2	HTX, IDC
Lotus 1-2-3 WJ2	WJ2
Lotus 1-2-3 WK1	WK1
Lotus 1-2-3 WK3	WK3
Microsoft Active Server Pages	ASP
Microsoft Excel 3	XLS
Microsoft Excel 4	XLS
Microsoft Excel 5–7	XLS
Microsoft Excel 97–2002	XLS
Microsoft IIS 1–2	HTM
Microsoft Snapshot	SNP
ODBC databases()	[Select database in the Select Data Source dialog box]
Paradox 3	DB
Paradox 4	DB
Paradox 5	DB
Paradox 7-8	DB
Rich Text Format	RTF
Text files	TXT, CSV, TAB, ASC
Word Merge	TXT
XML documents	XML

note

You won t see all of these selections in the Export dialog box at once each object type has a selection of the export formats listed in Table 17-1.

Generally, you’re better off exporting data from a table or query. Forms and reports don’t fare well when exported to most formats. There’s no export format that preserves the formatting of a form, but there are two export choices that preserve report formatting quite well: RTF preserves most report formatting, and RTF files can be opened in most word processors. Snapshot format preserves all the report’s formatting, but the report can’t be viewed except by users who have Access (or the Snapshot Viewer) installed.

To export a report in the RTF format, follow these steps:

Display the Database window, if necessary.
Click the Reports object icon.
On the right side of the Database window, select the name of the report you want to export.
Choose File, Export. The Export dialog box will appear.
Navigate to the folder in which you want to save the exported report.
In the Save As Type drop-down list, select the Rich Text file type.
In the File Name text box, type the name of the file.
Click Export. Access will save the object in the RTF format.

tip

If you’re in a hurry and want to export data quickly, try using the Office Links feature, which is described in the next section. Office Links automates the export process so that you can merge, publish, or analyze data in Word or Excel.

Using Office Links for Common Exporting Tasks

Office Links is an Access feature that lets you easily export your Access data to other Office applications. Office Links can help you with three common export tasks:

Merging Access data into a Word document
Publishing Access data as a Word document
Analyzing Access data in an Excel spreadsheet

You can display the Office Links options in one of two ways: by choosing Tools, Office Links or by clicking the Office Links button on the Database toolbar (as shown in Figure 17-2).

figure 17-2. you click the office links button on the database toolbar to see the office links choices.

Figure 17-2. You click the Office Links button on the Database toolbar to see the Office Links choices.

Each of the three Office Links choices performs a different task, as follows:

Merge It With Microsoft Word. Starts the Word Mail Merge Wizard, which guides you through the process of merging Access data to a Word document.
Publish It With Microsoft Word. Creates a Word RTF document from your Access data.
Analyze It With Microsoft Excel. Creates an Excel worksheet from your Access data.

note

Merge It With Microsoft Word is available only for tables and queries. You can use the other two Office Links choices with tables, queries, forms, and reports.

Exporting a Report to Adobe PDF Format

Although PDF format is not available as an option in the Export dialog box, you can still export a report to the widely used Adobe PDF format—at least if you have the full version of Adobe Acrobat (not just the free Reader). When you install Adobe Acrobat, it creates a new printer selection called Adobe Distiller. To export a report to PDF format, simply open the report, and print it to the Adobe Distiller printer selection. This creates a PDF file that you can then distribute to users.

The PDF version of the report is an exact representation of the report’s appearance in Print Preview, and this format has some advantages over the Snapshot format, since the Adobe Reader is widely distributed, and most users are likely to have it (which is not always the case with the Snapshot Viewer). The illustration below shows aPivotTable report saved in PDF format using the Adobe Distiller printer installed by Adobe Acrobat 5.0, open in Adobe Reader 5.0. As you can see from the figure, a report exported in PDF format looks exactly like the report in Access.

Merging Access Data with a Word Document

If you want to merge Access data with a Word 2002 document and your needs are simple (you don’t need filtering or a custom Word template, for example), using Merge It With Microsoft Word is the quickest, easiest approach.

To merge a table of Access contacts to a new, blank Word letter, follow these steps:

In the Database window, select a table that contains name and address data.
Click the Office Links button, and click Merge It With Microsoft Word. The Microsoft Word Mail Merge Wizard will launch, as shown in Figure 17-3.
Figure 17-3. The Microsoft Word Mail Merge Wizard starts you on the process of merging Access data with a Word document.
On the first page of the wizard, select the Create A New Document And Then Link The Data To It option, and then click OK. A new, blank Word document will open, and the wizard will switch from standard wizard pages to steps in the Word task pane.
Click the Letters option to create a new letter, scroll down if necessary, and then click Next: Starting Document at the bottom of the task pane, as shown in Figure 17-4.
Figure 17-4. In the Word task pane, you specify a document type.
Leave the default option selected (Use The Current Document), and click Next: Select Recipients at the bottom of the task pane.
Again, leave the default option selected (Use An Existing List), and click Next: Write Your Letter.
Compose the text of the letter.
You can insert various data components as you go along by clicking in the task pane the item you want to insert (such as Address Block or Greeting Line). You can display additional merge fields by clicking More Items. Figure 17-5 shows a letter with an address block and a first name inserted. Click Next: Preview Your Letters after you finish writing the letter. You might need to scroll down to see where to click.
Preview the letter for the first recipient in the data source, as shown in Figure 17-6. Click Next: Complete The Merge.
Figure 17-5. You can insert various data components as you compose your letter.

Figure 17-6. You should preview the Word mail merge letter for the first recipient to verify that all of the components appear correctly.
In this final step of the wizard, you choose your output. You can click Print to display the Print dialog box and select your print options, or you can click Edit Individual Letters to modify the letters in a Word document before printing.

note

The Mail Merge Wizard in Word 2002 has been significantly enhanced from the version in Word 2000, bringing all your choices together in an easy-to-follow process that lets you create new data files and documents or work with existing files from various programs. To find out more about how you can streamline your mail merge tasks with Word 2002, see Microsoft Word Version 2002 Inside Out (Microsoft Press, 2001).

Publishing Access Tables in Word Documents

If you’re working with an Access table that you want to spruce up and incorporate in a formal report to your board of directors, you can quickly and easily publish the information from Access to Word by using Publish It With Microsoft Word. This Office Links choice lets you go directly from Access into Word and produces a simply formatted but attractive Word table.

To publish an Access table in a Word document, follow these steps:

In the Database window, click Tables.
Select the table you want to publish in a Word document.
Click the Office Links button, and click Publish It With Microsoft Word. Word will launch, and the data will be placed in a default Word table, as shown in Figure 17-7. The default document format is RTF.
Figure 17-7. You can use Publish It With Microsoft Word to place an Access table into an RTF document in Word.

Troubleshooting - The default table format doesn’t work for me

If you want a different format for the table you’ve published in Word, you can choose from a number of styles. To modify the format, first select the table by clicking the table selector handle (the small square just outside the upper left corner of the table). The entire table will be highlighted.

Next choose Table, Table AutoFormat. In the Table AutoFormat dialog box, scroll through the various table styles and select the look that best fits your document needs.

Click Apply to reformat your Access table. Don’t forget to save the document by pressing Ctrl+S before you exit and return to Access.

Creating an Excel Worksheet from an Access Query

If you want to analyze data in your table or query, you can use Analyze It With Microsoft Excel to export the data to a worksheet. This is a seamless process that simply launches Excel 2002 and pastes the data from the table or query you select into a new worksheet. Here are the steps:

In the Database window, click Tables or Queries.
Select the table or query you want to use in the Excel spreadsheet.
Click the Office Links selector, and click Analyze It With Microsoft Excel. Excel will launch, and your Access data will be displayed in a new worksheet, as shown in Figure 17-8. You can then format and save the worksheet as you normally would.
Figure 17-8. You can create an Excel worksheet from an Access query.

Exporting Forms

You can use two of the Office Links choices—Publish It With Microsoft Word and Analyze It With Microsoft Excel—to publish and analyze form data, but you should first test how these features work before you use them to export forms or reports with lots of data. In some cases (especially for forms), the output is unattractive—you might get something similar to the worksheet shown in Figure 17-9.

figure 17-9. an excel worksheet created from an access form has little resemblance to the original form.

Figure 17-9. An Excel worksheet created from an Access form has little resemblance to the original form.

As you can see in Figure 17-9, Access exported the data as if it had been exported from a table, but the rows are very tall. You’re better off just exporting the data from a table or query and doing any fine-tuning of the document or worksheet in Word or Excel instead of exporting a form.

Exporting Reports

The same caveat applies to using reports with the Office Links choices: Test before you leap. If you export a report (perhaps based on a query that limits results to just a few pages of data) and check what the exported document looks like before you publish that big report you need for the meeting in 15 minutes, you’re more likely to be safe than sorry. In my experience, the best choices for exporting reports are RTF and Snapshot. The RTF export isn’t perfect, but it gives good results for most reports, as long as they don’t use graphics. Snapshot format gives excellent results, but, as mentioned earlier, only users with Access or the Snapshot Viewer installed can read this format.

Although a simple report might fare well when exported to RTF, if you select a complex report (such as the Northwind Catalog report) and click Publish It With Microsoft Word, the Northwind logo, all product graphics, and the decorative lines and rectangles will be missing from the RTF document opened in Word. Each text block will have a tab at the beginning of each line and a hard return at the end. Tabular text will be exported as plain text rather than converted to a Word table. If the report has headers andfooters, they won’t be converted to Word headers and footers; they’ll be included as plain text in the document, with hard page breaks.

One way to make sure that you get an attractive Word report based on your Access data is to take matters into your own hands by using VBA code. You export the data to Word from a table or query, fill a preformatted Word table with the data, and do any other required formatting using components of the Word object model. This approach will give you total control over the appearance of the document. The Word export code sample in the section "Exporting Data Using VBA Code," illustrates this technique.

Exporting a PivotTable to Excel

As you saw in Chapter 12, "Using PivotTables and PivotCharts to Analyze Data," PivotTables, which were first introduced in Access 2000, have been greatly enhanced in Access 2002. This feature enables you to analyze data from a variety of perspectives. You can even export an Access PivotTable to Excel so that you can analyze the data in a worksheet using Excel’s advanced data analysis tools. This is a fairly straightforward process, as follows:

In the Database window, click Forms.
Open the form with the PivotTable you want to export.
Display the PivotTable view by choosing View, PivotTable View.
Choose PivotTable, Export To Microsoft Excel. Excel will launch, and the PivotTable will be displayed in a new worksheet, as shown in Figure 17-10.
Figure 17-10. This Excel PivotTable was created from an Access PivotTable.

Troubleshooting - My PivotTable is read-only in Excel

The idea behind exporting PivotTables from Access to Excel is to let you analyze the data in the PivotTable using Excel’s advanced data analysis tools. Because Excel generates a read-only HTML file when you export a PivotTable from Access, you can’t save or modify the Excel PivotTable initially.

If you want to work with the PivotTable in Excel format, you can choose File, Save As and enter a new name for the PivotTable. Select the folder in which you want to store the file, and click Save. Excel will copy the read-only PivotTable into an Excel worksheet, thereby making it editable.

InsideOut

Unfortunately, there’s no way to export a PivotChart to Excel. (The PivotChart menu has no Export To Microsoft Excel command.) You can export a form that has a PivotChart view, but you’ll just get the usual columns of data in the new Excel worksheet. At that point, you can make a PivotTable with a PivotChart report in Excel, but you’re starting from scratch as far as the PivotChart’s layout is concerned.

Exporting Database Objects to Other Access Databases

The exporting process in Access doesn’t limit you to exporting objects to other programs; you can also export objects to other Access databases. Any database object you create in Access—including tables, queries, forms, reports, macros, and data access pages—can be used in other Access databases.

To export a database object, follow these steps:

Display the Database window of the file from which you want to export objects.
Select the object type you want to export, and then click the specific item in the displayed list.
Choose File, Export, and navigate to the folder containing the database file to which you want to export the object. (See Figure 17-11.)
Figure 17-11. You can easily export an object from one Access database to another by choosing File, Export and selecting the target database file.
Select the file, and click Export. Access will export the object and add it to the database you selected.

InsideOut

Access doesn’t offer an option for exporting custom toolbars and menus to another Access database. However, you can import custom toolbars and menus into an Access database by clicking the Options command button in the Import Objects dialog box and selecting the Menus And Toolbars check box, as shown in Figure 17-12.

figure 17-12.select the menus and toolbars check box in the advanced screen of the import objects dialog box to import custom toolbars and menus.

Figure 17-12. Select the Menus And Toolbars check box in the Advanced screen of the Import Objects dialog box to import custom toolbars and menus.

Troubleshooting - I can’t export query results to my older Access databases

Suppose you’re trying to export a great query you created in Access 2002 to a similar database you created last year using an earlier version of Access—say, Access 97. After you click the Export button, Access will greet you with the following error message: You can’t export database objects (except tables) from the current version of Microsoft Access to earlier versions of Microsoft Access.

For queries (only), there’s a simple workaround for this problem, which is available if you still have the earlier version of Access installed. Open the query in SQL view, and then highlight and copy the SQL statement to the Windows Clipboard by pressing Ctrl+C. Next open the target database in the earlier version of Access, create a new query, paste the SQL statement text into its SQL window by pressing Ctrl+V, and save the query.

Exporting Access Query Results to an Earlier Excel Format

Earlier in this chapter, you learned how to quickly export a table or query to an Excel worksheet using the Analyze It With Microsoft Excel option in Office Links. If you want to export a query to an Excel worksheet using an Excel format that isn’t available through Office Links (say, Excel 3 or 4), follow these steps:

In the Database window, click Queries.
Select the query you want to export.

note

When you export a query, you’re actually exporting the query results, or the data generated by running the query.
Choose File, Export. The Export dialog box will appear.
In the Save As Type drop-down list, select the appropriate Excel format.
Click the Export button to export the query.

Exporting Data to Text Files

When you export a table or query to a text file, you have the choice of exporting to a delimited file or a fixed-width file. As discussed in Chapter 16, a delimited file is one in which the beginning and end of each data item is marked, or delimited, with a specific separator character—usually a comma, tab, semicolon, or space. A fixed-width file, sometimes also called a positional file, is one in which the fields appear in specific places and are given specific widths for each record field in the file. The fixed-width columns let the database determine where each data field begins and ends.

note

Fixed-width text files will be displayed correctly only when a fixed-width font (one whose characters are all the same width, such as Courier New) is used.

To export a table or query to a comma-delimited text file (the most widely used type of text file), follow these the steps:

In the Database window, select the table or query.
Choose File, Export. The Export dialog box will appear.
In the Save As Type drop-down list, select Text Files.
Give the new file the CSV extension (csv stands for comma-separated values), and click Export. The Export Text Wizard will open, as shown in Figure 17-13.

tip

Files saved with the CSV extension can be opened directly in Excel.

Figure 17-13. The Export Text Wizard helps you export a table or query to a comma-delimited text file.
Select Delimited (the default selection), and click Next.
Set the delimiter and text qualifier. The default selections of comma and double quotation marks are usually fine. (See Figure 17-14.)
Figure 17-14. The wizard prompts you to select the delimiter to use for separating data items.
If you want to include the field names in the exported data, which is generally a good idea, select Include Field Names On First Row, as shown in Figure 17-14. Click Next.
If you want, you can change the export file name and path, as shown in Figure 17-15.
Figure 17-15. You can change the export file name and path on the last page of the wizard.
Click Finish to create the text file. Figure 17-16 shows the new text file in WordPad.
Figure 17-16. The individual data items in each record in the exported text file are separated by commas and enclosed in double quotation marks.

Exporting Data to XML Files

Extensible Markup Language (XML) is gaining popularity as the standard format for data exchange on the Web, although it’s less useful for exchanging data among software applications because many applications can’t interpret the data in this format correctly. If you export a PivotChart from Access to XML, for example, and then open it in Word or Microsoft Internet Explorer, you’ll get a text listing of the data, in XML format (which is a variant of HTML), but you won’t see a PivotChart.

If you’re able to import XML files in an acceptable manner, Access 2002 lets you export tables, forms, reports, and queries to XML format so that they can be used on the Web and possibly in other software applications. The export options for XML let you opt to save the data only, save the schema in addition to the data, or save the presentation of the data in addition to the data and the schema.

note

The schema for an XML file is the record of the structure of the data and is saved in a file with an XSD extension. The presentation of the data saves the information used to display the data in a spreadsheet format in a file with an XSL extension.

To export a table or query to XML, follow these steps:

In the Database window, select the object you want to export in XML.

note

You can export tables, queries, forms, and reports to XML, but you can’t create a presentation file of XML data if you export a form or report.
Choose File, Export. The Export dialog box will appear.
Navigate to the folder in which you want to store the exported file(s).
In the Save As Type drop-down list, select XML Documents. The Export XML dialog box will appear, as shown in Figure 17-17.
Figure 17-17. When you export an object to XML, you can export the data only, the data and its structure, or the data, its structure, and the way it’s presented and linked.
Select the export options you want: data only, the schema (data structure) of the exported file, and the presentation of the data (the way in which the data is presented and linked).
If you want to specify a particular location for the XML data, schema, and presentation files you create, click the Advanced button. Each tab (Data, Schema, and Presentation) includes an option that lets you change the location where the files will be stored.
Click OK to export the object to XML in the files you specified. Figure 17-18 shows the resulting XML file opened in Internet Explorer.
Figure 17-18. This XML file was exported from an Access query.

Exporting Data to Non-Microsoft Applications

In addition to exporting Access data to other Access databases or to Office programs such as Word and Excel, you can also export data to non-Microsoft applications.

Databases that were popular before Access 1.0 was released in 1992—such as dBASE and Paradox—are still in use in some businesses. If your company or client uses some of these programs, you’ll need some way to trade data with them.

To export a table or query to a non-Microsoft program, follow these steps:

In the Database window, select the table or query you want to export, and click the item.
Choose File, Export. The Export dialog box will appear.
In the Save As Type drop-down list, select the program and version you want to export to. (Table 17-2 provides a quick overview of supported non-Microsoft export formats.)
Navigate to the folder in which you want to store the file.
Type the file name you want to assign to the exported file.
Click Export to complete the process.

Table 17-2. Export formats for non-Microsoft applications

Application	Versions supported	File format
dBASE	III, IV, 5	DBF
Lotus 1-2-3	WK1, WK3, WJ2	WK1, WK3, WJ2
Paradox	3, 4, 5, 7–8	DB

Exporting to an ODBC Database

Open Database Connectivity (ODBC) is a standard way to share data between databases and programs on various platforms, which extends the reach and potential use of the database objects you create in Access. ODBC isn’t used as much as it was in former years, because Automation code lets you work directly with data in many other applications. However, you can still set up an ODBC data source to work with data in an ODBC database from Access. And if you need to export data by using a specific file format that isn’t one of the selections in the Export dialog box and that format supports ODBC, you can export a table or query to an ODBC database by following these steps:

In the Database window, select the table or query to export, and then choose File, Export. The Export dialog box will appear.
In the Save As Type drop-down list, select ODBC Databases. A small Export dialog box will appear with the name of the selected table or query, as shown in Figure 17-19. Verify that this is the object you want to export, and click OK.
Figure 17-19. This Export dialog box displays the name of the Access table being exported to an ODBC database.

InsideOut

Several of the Machine Data Source selections (dBASE Files, Excel Files, and MS Access Database) have their own export file types. If you want to export to one of these file formats, it’s much simpler to just select the format in the Export dialog box instead of working through the ODBC dialog box.
In the Select Data Source dialog box, shown in Figure 17-20, select the data source you want to use. Use the Machine Data Source tab to choose a driver that’s local to your machine, and use the File Data Source tab to select a file that describes the driver you want to use. Click OK.
Figure 17-20. In the Select Data Source dialog box, you select a machine source for exporting to an ODBC program.
If necessary, log in to the database. Access completes the export without any further messages and exports the selected table or query to the ODBC program.

For an alternative method of exporting Access data to SQL Server, see Appendix E, "Upsizing to SQL Server," which describes using the Upsizing Wizard to upsize an Access database to SQL Server.

Exporting to HTML

If you’ve created a database object you want to use directly on the Web, you can export the item directly to HTML using Access’s export features. Here’s how:

In the Database window, select the database object you want to export.
Choose File, Export. The Export dialog box will open.
Navigate to the folder in which you want to store the file.
In the Save As Type drop-down list, select HTML Documents. For some export selections, the HTML Output Options dialog box will appear, as shown in Figure 17-21.
Figure 17-21. When outputting to HTML, you can select an existing HTML template or use the default Access template.
If you want to use an existing HTML template, click Select A HTML Template; otherwise, leave Default Encoding selected.
Click OK to close the dialog box and export the object in HTML format.