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:
Method 1 (Recommended): Modify the Windows RegistryWARNING: 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:
Method 2: Use Code to Change the ExtensionMicrosoft 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:
- Open the database in Microsoft Access.
- On the Insert menu, click Module to start the Visual Basic Editor and create a new, blank module.
- 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 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"
strFileCopy = objFile.Name & ".txt"
'Create a copy of the file with a .txt extension
objFile.Copy strFileCopy, True
DoCmd.TransferText acImportDelim, , strTableName, strFileCopy, True