ACC2000: How to Import a Text File That Has an Extension That Access Does Not Recognize

Moderate: Requires basic macro, coding, and interoperability 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

Summary

This article describes two methods that allow you to import a text file that has an extension that Access does not recognize, without having to change the extension manually.

More Information

To import a text file that has an extension that Access does not recognize as valid, use one of the following methods.

Method 1 (Recommended): Modify the Windows 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.

The recommended method is to modify the Windows registry to include the extension of the text file that you want to import.

The following registry key lists the extensions of text files that can be imported into Microsoft Access:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions
If you try to import a text file that has an extension that is not listed in the DisabledExtensions key, you receive the following error message:

Can't update. Database or object is read-only.
The following article in the Microsoft Knowledge Base has information about modifying this registry key:
245407 ACC2000: 'Can't Update. Database or Object...' Importing Files

Method 2: Use Code to Change the Extension

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. A second method is to write Microsoft Visual Basic for Applications code to programmatically create a copy of the file and add a valid extension. Follow these steps and the example of code:
  1. Open the database in Microsoft Access.
  2. On the Insert menu, click Module to start the Visual Basic Editor and create a new, blank module.
  3. Add the following code to the new module:
    Sub DataImport(strPath As String, strTableName As String)
    'Variables used to create and modify the file extension
    Dim objFileSystem
    Dim objFile
    Dim strFileCopy As String
    Dim intExtPosition As Integer

    'Create an instance of the FileSystemObject to access
    'the local file system
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")

    'Use the GetFile method to return a File object corresponding to the
    'file in a specified path.
    Set objFile = objFileSystem.GetFile(strPath)
    intExtPosition = InStr(objFile.Name, ".")
    If intExtPosition > 0 Then
    strFileCopy = Left(objFile.Name, intExtPosition - 1) & ".txt"
    Else
    strFileCopy = objFile.Name & ".txt"
    End If

    'Create a copy of the file with a .txt extension
    objFile.Copy strFileCopy, True
    DoCmd.TransferText acImportDelim, , strTableName, strFileCopy, True
    End Sub
This procedure accepts two arguments, the path of the file that you want to import and the name of the table that you want to create in the Access database. For example, to import a file named C:\Invoices.dat and create a table named MyInvoices, use the following syntax to call the procedure:

DataImport "C:\Invoices.dat", "MyInvoices"
Propriedades

ID do Artigo: 306144 - Última Revisão: 23 de jun de 2005 - Revisão: 1

Comentários