Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103130

From BetaArchive Wiki

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:


  1. Open NWIND.MDB.
  2. 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.
  3. In the Action column choose TransferText.
  4. 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.

  5. Choose Save from the File menu, type a unique macro name, and press ENTER.
  6. 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:


  1. Follow steps 1-3 for TransferText, but choose TransferDatabase as the macro action.
  2. 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 
  3. Follow steps 5-6 for TransferText.

Example C

To export a query as an Excel spreadsheet using TransferSpreadsheet:


  1. Follow steps 1-3 for TransferText, but choose TransferSpreadsheet as the macro action.
  2. 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.

  3. 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.


  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.



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
Issue type : kbhowto
Technology :


Last Reviewed: November 4, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.