Microsoft KB Archive/103130

{|
 * width="100%"|

ACC1x: How to Export the Results of a Query

 * }

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.


 * 1) Method 1: Uses the Transfer macro actions (TransferText, TransferDatabase, TransferSpreadsheet) to export query results directly to a file.
 * 2) 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: 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.</li>  Fill in the macro arguments with the following settings:

<pre class="FIXEDTEXT">     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 </li> Follow steps 5-6 for TransferText.</li></ol>

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.</li>  Fill in the macro arguments with the following settings:

<pre class="FIXEDTEXT">     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: NOTE: You can specify the name of a query in the Table Name argument. </li> Follow steps 5-6 for TransferText.</li></ol>

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.


 * 1) Open NWIND.MDB.
 * 2) Choose the Query button in the Database window, select Employee Sales for 1991, and choose the Design button.
 * 3) Choose Make Table from the Query menu.
 * 4) In the Table Name box, type a unique table name where the data will be exported to. Choose OK.
 * 5) 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.
 * 1) Use the Export command from the File menu to export the new table's contents to a file.