Knowledge Base

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

Article ID: 304206

Article Last Modified on 6/28/2004

APPLIES TO

Microsoft Access 2000 Standard Edition

This article was previously published under Q304206

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SYMPTOMS

When you try to import or link a text file, you receive one of the following error messages:

Run-time error '3027':

Cannot update. Database or object is read-only.

-or-

Cannot update. Database or object is read-only.

CAUSE

The file that you are importing or linking does not have a valid file name extension.

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:

- txt
- csv
- tab
- asc

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:

Open the sample database Northwind.mdb.
Click the Modules tab.
Click New.

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

Save the module as Import_Text.
Create a new macro that has the RunCode action.
In the Function Name property, enter ImportNonTXT().

NOTE: "ImportNonTXT" is equivalent to the function name.
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:

Delete the imported file from the database.
Rename the file in C:\ to Employees.rtf.

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")

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.

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

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text
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.
Quit Registry Editor.

STATUS

This behavior is by design.

MORE INFORMATION

Before the latest update of Microsoft Jet version 3.5 (Jet 3.5, Service Pack 3), Microsoft Access 97 did not generate any errors when you imported or linked files that did not have a valid extension. In fact, the file was not required to have any extension. This behavior was changed in Jet 3.5 Service Pack 3 and later, which affects Access 2000. Importing or linking text files that do not have a .txt, .csv, .tab, .asc, .tmp, .htm, or .html extension is no longer supported.

Steps to Reproduce the Behavior

Use Notepad or any other text editor to create a new text file.
Save the file as MyTest.txt.
Rename the file to MyTest, without the .txt extension.
Import MyTest into Microsoft Access using the Text Import Wizard.

REFERENCES

For additional information, click the article numbers below to view the article in the Microsoft Knowledge Base:

239471 Jet 4.0 Text IISAM Allows User to Append Lines to System Files

239105 Jet 3.5 Text ISAM Allows User to Append Lines to System File

172733 ACC97: Updated Version of Microsoft Jet 3.5 Available

For more information about the TransferText action or method, click Microsoft Access Help on the Help menu, type transferText in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: prb acc2000 access 2000 TransferText Transfer Text txt *.txt .txt

Keywords: kberrmsg kbprb KB304206

Microsoft KB Archive/304206

Contents