ACC95: Exporting Query w/ Text Export Wizard Reorders Columns

• Microsoft Access 7.0

SYMPTOMS

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

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

MORE INFORMATION

Steps to Reproduce Problem

1. Start Microsoft Access and open the sample database Northwind.mdb.

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

3. Save the qryExpCustomer query, and then open it in Datasheet view. Note the order of the columns.

4. Close the query and select it in the Database window.

5. On the File menu, click Save As/Export.

6. In the Save As dialog box, click "To an external File or Database," and then click OK.

7. In the "Save Query 'qryExpCustomer' In" dialog box, select Text Files in the Save As Type box, and then click Export.

8. In the first screen of the Text Export Wizard, click Delimited, and note that the columns in the query are ordered correctly. Click Finish.

9. Use Notepad or another text editor to open the qryExpCustomer.txt file. Note that the column containing "ABCDEFG" is reordered.

REFERENCES

For more information about the TransferText method or macro action, search the Help Index for "TransferText action" or "TransferText method."

For more information about exporting text files, search the Help Index for "exporting data."