Access 2010: Fixed width text export does not respect the Start and Width settings of Export Specification

In Microsoft Access 2010 when you use an Export Specification in which you select Fixed Width and specify Start and Width settings to export data, the text in the exported file does not display the correct Start and Width settings.

Microsoft is aware of this problem in Microsoft Access 2010.

As a workaround, create a query based on the table you need to export. In that query create an expression using a string function to set the width of the fields. Create a new Export Specification and export the query rather than the table as a Text file.

For instance, using the database created in the Steps to Reproduce in the More Information section below:

1. Create a query named Query1
2. Add tblTest to the query.
3. In the first column in the query grid, add the following expression to display only the first character of the last name:

LastName1: Left([LastName],1)

4. Add the FirstName field to the second column in the query grid
5. Save and close the query

6. Create a new Export Specification:

     a) Right click on Query1, click Export and select Text File
     b) Select the destination of the text file, name the file "Test" and click OK
     c) In the Export Text Wizard, select Fixed Width
     d) Click the Advanced button

        Set the Start for LastName1 as 1 and the Width as 1
        Set the Start for FirstName as 2 and the Width as 20

     e) Click the Save As... button
     f) Enter the following as the Specification Name:

   Query1 Export Specification

     g) Click OK to each dialog
     h) Click Cancel in the Export Text Wizard dialog


7. Update the ExportText function in Module1 to export Query1:

Public Function ExportText()

DoCmd.TransferText acExportFixed, "Query1 Export Specification", "Query1", CurrentProject.Path & "\Test.txt", 0

End Function

The same problem does not occur in Microsoft Access 2003 or in Microsoft Access 2007.

For more information on how to use string manipulation functions see:

http://office.microsoft.com/en-us/access-help/using-string-manipulation-functions-HA001054727.aspx

Steps to reproduce:

1. Create a new blank database. Close Table1 and do not save.

2. On the Create tab, click Table Design and add the following fields:

       Field Name: LastName; Data Type: Text
       Field Name: FirstName; Data Type: Text

3. Save as tblTest. Do not create a Primary Key. 

4. Add a new record in tblTest:

       LastName: ABCDEFG        FirstName: Test

    Close tblTest.

5. Create an Export Specification:

   a) Right click on tblTest, click Export and select Text File
   b) Select the destination of the text file, name the file "Test" and click OK
   c) In the Export Text Wizard, select Fixed Width
   d) Click the Advanced button

      Set the Start for LastName as 1 and the Width as 1
      Set the Start for FirstName as 2 and the Width as 20

   e) Click the Save As... button
   f) Enter the following as the Specification Name:

        tblTest Export Specification

   g) Click OK
   h) Click Next and click Cancel
    i) Click Close

6. Create a new module, Module1, and paste the following function:

          Public Function ExportText()

                  DoCmd.TransferText acExportFixed, "tblTest Export Specification", "tblTest", CurrentProject.Path & "\Test.txt", 0

          End Function

7. Click in the function and click the Run button to execute the ExportText function, which will create the text file

8. Open the Test.txt text file in the same folder as the database file

Results: The text file displays "ABTest" instead of the expected "ATest"

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.