ACC2000: Special Characters in Field Names Are Changed When You Export a Table to a Different File Format (308686)

RESOLUTION

There are five possible resolutions.

Method 1:

Do not use special characters such as the number sign and the period (ADP) in table and field names.

Method 2:

Before you export the data, change the field names in Access so that they do not have number signs.

Method 3:

Modify the resulting exported file so that the field names have the characters that you want.

Method 4:

Save the file as formatted. For an example of how to do so, follow these steps:

1. Click the table that you want to export.
2. On the File menu, point to Export.
3. In the Save as type box, click Text Files or one of the Excel file formats, click to select the Save formatted check box, and then click Save.NOTE: For HTML files, use an HTML template to save the file as formatted. You can create a blank HTML template by creating an empty text file and then changing its extension from .txt to .html.

Method 5:

Use a custom export procedure.

The following example demonstrates how to export a table that has field names that have number signs to a Text File.

1. Add a reference to the Microsoft Scripting Runtime library (scrrun.dll).
2. Add the following code to a module in the database or project. NOTE: The routine accepts two arguments: the name of the table (or query) that you want to export, and the full path and name of the final text file that is exported:

   Public Function FixExportedFieldNames(strTableName As String, strFileName As String)
   
   Dim fso As New FileSystemObject
   Dim ts1, ts2 As TextStream
   Dim strTxtLine, strTempFileName As String
   strTempFileName = "c:\FixFieldNamesTemp.txt"
   DoCmd.TransferText acExportDelim, , strTableName, strFileName, True
   
   ' Open files.
   Set ts1 = fso.OpenTextFile(strFileName)
   Set ts2 = fso.OpenTextFile(strTempFileName, ForWriting, True, TristateFalse)
   
   ' Replace the period on the first line with a number sign.
   strTxtLine = ts1.ReadLine
   strTxtLine = Replace(strTxtLine, ".", "#")
   ts2.WriteLine strTxtLine
   
   ' Loop while not at the end of the file.
   Do While Not ts1.AtEndOfStream
   strTxtLine = ts1.ReadLine
   ts2.WriteLine strTxtLine
   Loop
   
   ' Close the files.
   ts1.Close
   ts2.Close
   DoEvents
   
   ' Overwrite original file with new file
   fso.CopyFile strTempFileName, strFileName, True
   fso.DeleteFile strTempFileName
   
   End Function