Microsoft KB Archive/163765

= ACC95: Exporting Query with Text Export Wizard Reorders Columns =

Article ID: 163765

Article Last Modified on 11/17/2000

-

APPLIES TO


 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q163765



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When you use the Text Export Wizard to create a delimited text file from a query that contains an expression, the order of the columns in the text file is different than it is in the query.

NOTE: This behavior also occurs when using the Text Import Wizard.



RESOLUTION
If you want to retain the same column order in the exported text file, you can create a table from the query, and then export the table.

Or, you can use the TransferText action or method in a macro or Visual Basic for Applications code.

Method 1: Exporting a Table Created from the Query

 * 1) Create the query that you want to export to a text file.
 * 2) On the Query menu, click Make Table.
 * 3) In the Make Table dialog box, type the name of a new table to store the results of the query.
 * 4) On the Query menu, click Run. Confirm that you want to paste the rows into a new table.
 * 5) Close the query, and then select the new table in the Database window.
 * 6) On the File menu, click Save As/Export to start the Text Export Wizard and save the table to a delimited text file.

Method 2: Using the TransferText Action or Method
The following steps use the TransferText action in a macro; the same results apply if you use the TransferText method of the DoCmd object in Visual Basic code.


 * 1) Create the query that you want to export to a text file.
 * 2) Create a new macro that contains the TransferText action to export the query to a delimited text file.
 * 3) Run the macro to export the query.



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 7.0. This problem no longer occurs in Microsoft Access 97.



Steps to Reproduce Problem
 Start Microsoft Access and open the sample database Northwind.mdb.  Create a new query in Design view based on the Customers table:

     Query: qryExpCustomer Type: Select Query

Field: CustomerID Table: Customers Field: Expr1: "ABCDEFG" Field: CompanyName Table: Customers  Save the qryExpCustomer query, and then open it in Datasheet view. Note the order of the columns. Close the query and select it in the Database window. On the File menu, click Save As/Export.</li> In the Save As dialog box, click "To an external File or Database," and then click OK.</li> In the "Save Query 'qryExpCustomer' In" dialog box, select Text Files in the Save As Type box, and then click Export.</li> In the first screen of the Text Export Wizard, click Delimited, and note that the columns in the query are ordered correctly. Click Finish.</li> Use Notepad or another text editor to open the qryExpCustomer.txt file. Note that the column containing "ABCDEFG" is reordered.</li></ol>

<div class="references_section">