How to Import Text File into Microsoft Access Table Using DDE

• Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit only, for Windows, version 4.0
• Standard and Professional Editions of Microsoft Visual Basic for Windows, version 3.0

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:

ARTICLE-ID: Q103807

TITLE     : 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.

   ARTICLE-ID: Q96845
   TITLE     : How to Use DDE to Display Microsoft Access Data in VB