Exporting Access 2007 Tables to Other File Formats - dummies

Exporting Access 2007 Tables to Other File Formats

By Laurie Ulrich Fuller, Ken Cook, John Kaufeld

While every Microsoft Access 2007 object can be exported, exporting the data in a table or query to another program — such as a spreadsheet — is the most common export task. Exporting a table or query involves reorganizing the data it contains into a different format. As with importing, Access can translate the data into a variety of file formats depending on your needs.

Export formats

The External Data tab on the Ribbon reveals all file formats to which the object can be exported. (Access exports to the same formats that it imports. Access also exports to PDF — Adobe Acrobat files — and Microsoft Word.)

The main problem to keep an eye out for when exporting is data loss. A fabulous Access table doesn’t always translate to a fabulous Paradox table. Not all databases share the same rules for

  • Data types: Special Access data types such as AutoNumber, Yes/No, Memo, and OLE are almost sure to cause problems in other programs. You may need some creative problem solving to make the data work just the way you want it to work.
  • Field names: Each database program has its own set of rules governing field names such as the length of and the special characters (like a dollar or percent sign) allowed in the field name.
    To avoid field name problems during export, keep your field names short and use only letters and numbers while naming your Access fields. If your Access table’s field names break the rules of the program you are exporting to, the export will not work properly.

Be ready to spend time tuning the export so that it works just the way you want. If you have a problem during export, consult the documentation of the software whose file format you are exporting to for field name and data type rules.

Exporting table or query data

The steps to exporting a table or query are simple:

1. With the database open, click the table or query that you want to export.

The table name is highlighted.

2. Click the External Data tab on the Ribbon.

The Export tool group appears on the Ribbon:

• The common exporting tasks have their own buttons.

• The seldom used formats are lumped onto the More button.

3. Click the tool that matches the program to which you’ll export your data.

An Export dialog box customized to your format of choice appears.

4. Follow the steps in the Export dialog box to complete the export.

The Export dialog box will display the choices for your export file format:

• Every choice will ask for a filename (including path) for your exported data.

• Some export processes also ask whether you want to open your new file after the export is complete. This can prevent a frustrating search after you save the file.

5. Select the Save Export Steps check box if you know you’ll do this export again.

The Ribbon contains a button called Saved Exports. Use this to easily export a table or query on a routine basis.