VB Import text file into Access 2007 table


  • Hello,

    I'm looking for a VB code that imports a text file into a Access 2007 table. The text file can be located in different directories. So I need a Open window to display to navigate to the appropiate directory. The VB code will also need to utilize a previously saved import specification for the text file being imported.

    Thanks in advance for your help.



    Wednesday, May 11, 2011 2:14 AM


  • Hi Kevin,


    By using the FileDialog() its hard to automate the next step. If you can specify file name (ends with "code.txt"), you got automate this by doing something like below code. Copy below code into a New Module and Save it. Then under a CommandButton or Macro;


       Note: Last option "True" if you want to search through subdirectories as well.

          Note: Make sure you change the SpecificationName and TableName into yours.

             Note: Add Microsoft Scripting Runtime to your References.


    Public Function ImportTXT(SourceFolderName As String, IncludeSubfolders As Boolean)
    Dim FSO As New FileSystemObject, SourceFolder As Folder, Subfolder As Folder, FileItem As File
    Dim lngCount As Long, strSQL As String

    On Error GoTo Err_Handler
        Set SourceFolder = FSO.GetFolder(SourceFolderName)
        For Each FileItem In SourceFolder.Files
            Select Case Right(FileItem.Name, 8)
                Case "code.txt"          

                       DoCmd.TransferText acImportFixed, "YourSpecificationName", "YourTableName", FileItem, True
                   '  Optional for deleting file after Importing

                   'Kill FileItem
                End Select
            lngCount = lngCount + 1
        Next FileItem
        If IncludeSubfolders Then
            For Each Subfolder In SourceFolder.SubFolders
                ImportTXT Subfolder.Path, True
            Next Subfolder
        End If
        Set FileItem = Nothing
        Set SourceFolder = Nothing

        Exit Function

        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Exit_Handler

    End Function



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by Bruce Song Friday, May 27, 2011 10:55 AM
    Wednesday, May 11, 2011 2:32 AM