TransferText - Multiple txt files simultaneously

Question

I need to regularly move all text files from a directory into a database (Access97). All text files are exactly the same in specs and the data within needs to be appended to a single table. Is there a way to loop through all .txt files from a directory and run a TransferText that appends the data from each into the target table?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: TransferText - Multiple txt files simultaneously
Date: 27 May 1999 0:46

Stefano,

Here is one of the possible solutions:

  ' Assuming that *.txt files to import are in the same
  ' directory as the .mdb with this code and that
  ' table name is "tblTest" we get this
  ' quick template solution:

  Dim strTblName As String
  Dim strFullDBPath As String
  Dim strImpPath As String
  Dim strImpFileNameExt As String

  strTblName = "tblTest"
  strFullDBPath = CodeDb().Name
  strImpPath = Left(strFullDBPath, _
             Len(strFullDBPath) - Len(Dir(strFullDBPath)))
  strImpFileNameExt = Dir(strImpPath & "*.txt")
  While (strImpFileNameExt <> "")
    DoCmd.TransferText acImportDelim, , strTblName, _
          strImpPath & strImpFileNameExt, True
    Debug.Print strImpPath & strImpFileNameExt & " imported."
    strImpFileNameExt = Dir
  Wend

HTH,
Shamil