How to Import Text File into Microsoft Access Table Using DDE (113905)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition, 16-bit, for Windows 4.0
  • Microsoft Visual Basic Enterprise Edition, 16-bit, for Windows 4.0
  • Microsoft Visual Basic Standard Edition for Windows 3.0
  • Microsoft Visual Basic Professional Edition for Windows 3.0
This article was previously published under Q113905

SUMMARY

This article shows you how to implement the built-in TransferText command of Microsoft Access to import data from a text file into a Microsoft Access Table using DDE. This method is faster than the alternate method outlined in the following article in the Microsoft Knowledge Base:

103807 : How to Convert a Text File into a New Access Database

MORE INFORMATION

The TransferText command is used in Access Basic to import data from a text file into a table. If the table specified doesn't exist, the TransferText command creates the table. If the table does exist, the text file's format must match that of the existing table.

The text file can be comma delimited with strings enclosed in quotation marks or it can be of fixed width. The delimiter and fixed width settings can be set up in Microsoft Access by choosing Imp/Exp Setup from the File menu. For more information on these settings, please consult the Microsoft Access Help menu.

Step-by-Step Example

  1. Using Notepad, create the following file to import and save it as C:\ACCESS\SHIPPERS.TXT:

    4,"world traveller"
    5,"frazier freight"
    6,"longfellow hauling"
    7,"toolsmith shipping"
  2. Start Microsoft Access and open the C:\ACCESS\NWIND.MDB database.
  3. Start a new project in Visual Basic (ALT, F, N). Form1 is created by default.
  4. Place a text box (Text1) and a command button (Command1) on the form.
  5. Add the following code to the click event of Command1: 
       Sub Command1_Click ()
      Dim cmd As String      ' Holds DDEExecute command.
      On Error GoTo DDEerr   ' Error trap.

      ' Make sure there isn't an existing DDE conversation:
      text1.LinkMode = 0

      ' Set up the DDE link:
      text1.LinkTopic = "MSACCESS|C:\ACCESS\NWIND.MDB"
      text1.LinkItem = "All"
      text1.LinkMode = 2

      ' DDEExecute command to import text file into Microsoft Access Table:
      cmd = "[TransferText ,,'Shippers','C:\ACCESS\SHIPPERS.TXT']"

      ' Execute the TransferText command and close the DDE link:
      text1.LinkExecute cmd
      text1.LinkMode = 0
      MsgBox "Transfer OK"
      Exit Sub

   DDEerr:
      ' An error has occurred.
      MsgBox Error
      MsgBox "Error in transfer."
      Exit Sub
   End Sub
  6. Run the Visual Basic program and click the command button to import the information in the text file into the Shippers table. A message box will be displayed showing you whether or not the transfer was successful.
For more information on how to implement DDE with Microsoft Access, please see the following article in the Microsoft Knowledge Base:

96845 : How to Use DDE to Display Microsoft Access Data in VB

Modification Type:MinorLast Reviewed:1/8/2003
Keywords:KB113905
©2002 Microsoft Corporation. All rights reserved.