ACC2000: Importing or Linking a Text File Fails for a File That Does Not Have a Valid File Name Extension (304206)

RESOLUTION

Use any one of the following three methods to work around this behavior.

Method 1 - Rename the File So That It Has a Valid File Name Extension

The following file name extensions are valid: NOTE: If your text files were originally HTM or HTML files, you can also rename them by using the .htm or .html file name extensions.

Method 2 - Programmatically Import Files That Do Not Have a .txt Extension

For an example of how to programmatically import files that do not have a .txt extension, follow these steps:

• Create a new function:
  1. Open the sample database Northwind.mdb.
  2. Click the Modules tab.
  3. Click New.
  4. Type or paste the following code in the module:

     Public Function ImportNonTXT()
         Dim fs, Fn, FLength, Fext, FDot, FOrig
         
         Set fs = CreateObject("Scripting.FileSystemObject")
         Set Fn = fs.Getfile("C:\Employees")
         
         ' This holds the file's original name for rename later.
         FOrig = Fn.Name
         ' Get the length of the file name.
         FLength = Len(Fn.Name)
         ' Set this value = to the last four characters of the file name.
         Fext = Right(Fn.Name, 4)
         ' Set this = to the first character.
         FDot = Left(Fext, 1)
         
         ' If there is a dot in the fourth from the last position...
         If FDot = "." Then
             '... and the extension is not .txt.
             If Fext <> ".txt" Then
                 ' Remove the extension from the file name.
                 Fn.Name = Left(Fn.Name, (FLength - 4))
                 ' Add the .txt to the file name.
                 Fn.Name = Fn.Name & ".txt"
             End If
         Else
             ' If there is not a dot in the fourth position
             ' add the .txt extension.
             Fn.Name = Fn.Name & ".txt"
         End If
         
         ' Transfer the file to a new table.
         DoCmd.TransferText acImportDelim, "", "Employees_Text", "C:\Employees.txt", False, ""
     
         ' After the file is transferred, rename it back to
         ' its original name.
         Fn.Name = FOrig
     
     End Function
     						

  5. Save the module as Import_Text.
  6. Create a new macro that has the RunCode action.
  7. In the Function Name property, enter ImportNonTXT().
     
     NOTE: "ImportNonTXT" is equivalent to the function name.
  8. Save the macro as mcrImport-Text.
• Export the Employees table:
  1. Click the Employees table, but do not open it.
  2. On the File menu, click Export to open the Export Table dialog box.
  3. In the Save in box, browse to the root of drive C (C:\).
  4. In the Save as type text box, click Text Files.
  5. In the File name text box, type Employees.txt.
  6. Click Save to open the Import Text Wizard.
  7. Click Finish.
• Rename the exported file:
  1. In Windows Explorer, browse to the root of drive C where you saved Employees.txt.
  2. Right-click Employees.txt, and then click Rename.
  3. Remove the .txt file name extension, and then press ENTER.
     
     NOTE: Showing of file name extensions may be turned off on your computer. To enable it, click Folder Options on the Tools menu, click the View tab, and then click to clear the Hide file extensions for known file types check box.
  4. Click Yes to the prompt.
• Run the macro. The file is temporarily renamed with the .txt extension, imported, and then renamed to its original name.

In some situations, it may not be possible to use the .txt extension. For example, if the text files are generated electronically, it may be impractical to rename the files or remove the extension, especially, if the file or files are generated regularly.

With a small code change, you can also import .rtf files. For an example of how to do so, follow these steps:

1. Delete the imported file from the database.
2. Rename the file in C:\ to Employees.rtf.
3. Change the file specification for the following line of code from

   Set Fn = fs.Getfile("C:\Employees")
   						

   to:

   Set Fn = fs.Getfile("C:\Employees.rtf")
   					

4. Run the macro again.
   
   Note that the file is imported successfully.

Method 3 - Modify the System Registry

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

1. Start Registry Editor (Regedt32.exe).
2. Locate the Disabled Extensions value under the following subkey in the registry:

   HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text

3. On the Edit menu, click Modify, append the new extension to the existing list, and then click OK.
   
   NOTE: Elements of the Value data list are separated by commas.
4. Quit Registry Editor.