ACC1x: How to Export the Results of a Query

ID: Q103130

The information in this article applies to:

Microsoft Access versions 1.0, 1.1
Microsoft Access Distribution Kit, version 1.1

SUMMARY

Microsoft Access does not provide a direct way to export the results of a query directly to a file using the Export command from the File menu. After the user selects Export from the File menu and selects an output format, the Select Microsoft Access Object dialog is presented which lists only tables, not queries, from the current database.

MORE INFORMATION

The following information discusses two ways to export the results of a query to a file.

Method 1: Uses the Transfer macro actions (TransferText, TransferDatabase, TransferSpreadsheet) to export query results directly to a file.

Method 2: Uses a Make Table query to save the results to a new table. Once this table exists, it can be exported by choosing Export from the File menu.

Method 1: Transfer Macro Action

The three Transfer macro actions: TransferText, TransferSpreadsheet, and TransferDatabase were designed to help automate the process of exporting (and importing) data from the file menu. These commands can also be used to facilitate exporting data from a query directly to a file.

The following examples use the Employee Sales for 1991 query included with the sample database NWIND.MDB supplied with Microsoft Access.

Example A

To export a query to a comma delimited text file using TransferText:

Open NWIND.MDB.

Choose the Macro button, then the New button, from the Database window. This will present you with a new macro grid containing Action and Comments columns.

In the Action column choose TransferText.

Below the macro grid you will see the arguments that need to be filled in for the TransferText action. Fill them in with the following settings:


      Action: TransferText
      --------------------
         Transfer Type: Export Delimited
         Specification Name: <leave empty>
         Table Name: Employee Sales for 1991
         File Name: C:\EMPSALES.TXT
         Has Field Names: Yes

NOTE: You can specify the name of a query in the Table Name argument.

Choose Save from the File menu, type a unique macro name, and press ENTER.

Choose Run from the Macro menu to execute the macro. Microsoft Access will run the query and save the results to the specified destination file name (EMPSALES.TXT).

Example B

To export a query as a DBase IV file using TransferDatabase:

Follow steps 1-3 for TransferText, but choose TransferDatabase as the macro action.

Fill in the macro arguments with the following settings:


      Action: TransferDatabase
      ------------------------
         Transfer Type: Export
         Database Type: dBASE IV
         Database Name: c:\ 
         Object Type: Query
         Source: Employee Sales for 1991
         Destination: EMPSALES
         Structure Only: No

Follow steps 5-6 for TransferText.

Example C

To export a query as an Excel spreadsheet using TransferSpreadsheet:

Follow steps 1-3 for TransferText, but choose TransferSpreadsheet as the macro action.

Fill in the macro arguments with the following settings:


      Action: TransferSpreadSheet
      ---------------------------
         Transfer Type: Export
         Database Type: Microsoft Excel
         Table Name: Employee Sales for 1991
         File Name: c:\empsales.xls
         Has Field Names: No
         Range: <leave empty>

NOTE: You can specify the name of a query in the Table Name argument.

Follow steps 5-6 for TransferText.

Method 2: Make Table Query

Use a Make Table query to export the query results to a table. Once the data is in a new table, the Export command from the File menu can be used to export the data to a file.

The disadvantage of this approach is that a new table increases the size of your database. After you export the data, the table is no longer needed. If you delete the table, Microsoft Access will not reclaim the once occupied space until you compact the database file.

The following examples use the "Employee Sales for 1991" query included with the sample database NWIND.MDB.

Open NWIND.MDB.

Choose the Query button in the Database window, select Employee Sales for 1991, and choose the Design button.

Choose Make Table from the Query menu.

In the Table Name box, type a unique table name where the data will be exported to. Choose OK.

Choose Run from the Query menu. The query stores the results in the new table.

You may want to save this query and give it a new name via the Save As command on the File menu if you need to export the results regularly.

Use the Export command from the File menu to export the new table's contents to a file.

REFERENCES

For more information on compacting a database see the "Microsoft Access User's Guide," version 1.0, Chapter 25, "Administering a Database System," pages 627 - 628

Keywords : kbinterop kbusage QryHowto
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto