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?
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
Copyright © 1999-2008 by Shamil Salakhetdinov. All rights reserved.