Import mutiple name text files into Access 2003

Discussion in 'Access VBA Modules' started by info@trevorlarge.co.uk, Apr 27, 2012.

  1. Guest

    I am importing succesfully multiple text files into Access 2003,
    however I need to ad a column in my access table to contain the name
    of each file in order for me to identify each record set, any help
    would be greatly appreciated, I am using the folowing VB

    Public Sub pfImport()
    On Error GoTo Err_F

    Dim strPathFile As String, strFile As String, strPath As String,
    strSpec As String
    Dim strTable As String, ynFieldName As Boolean
    ynFieldName = False
    strPath = "C:\TextFiles\" 'where my files are located
    strTable = "NotepadTable" 'the name of my Access table
    strFile = Dir(strPath & "*.txt")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile,
    ynFieldName
    ' Uncomment out the next code step if you want to delete the
    'file after it's imported
    ' Kill strPathFile
    strFile = Dir()
    Loop
    Exit_F:
    Exit Sub
     
    , Apr 27, 2012
    #1
    1. Advertisements

  2. Public Sub pfImport()
    On Error GoTo Err_F

    Dim db As DAO.Database
    Dim strPathFile As String, strFile As String, strPath As String,
    strSpec As String
    Dim strTable As String, ynFieldName As Boolean

    Set db = CurrentDb
    ynFieldName = False
    strPath = "C:\TextFiles\" 'where my files are located
    strTable = "NotepadTable" 'the name of my Access table
    strFile = Dir(strPath & "*.txt")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile,
    ynFieldName
    db.Execute "UPDATE NotepadTable SET FileName = '" & strFile & "' " _
    "WHERE FileName Is Null", dbFailOnError
    ' Uncomment out the next code step if you want to delete the
    'file after it's imported
    ' Kill strPathFile
    strFile = Dir()
    Loop

    Cleanup:
    Set db = Nothing
    Exit Sub

    Exit_F:
    Resume Cleanup

    Exit Sub

    wrote in message
    news:...

    I am importing succesfully multiple text files into Access 2003,
    however I need to ad a column in my access table to contain the name
    of each file in order for me to identify each record set, any help
    would be greatly appreciated, I am using the folowing VB

    Public Sub pfImport()
    On Error GoTo Err_F

    Dim strPathFile As String, strFile As String, strPath As String,
    strSpec As String
    Dim strTable As String, ynFieldName As Boolean
    ynFieldName = False
    strPath = "C:\TextFiles\" 'where my files are located
    strTable = "NotepadTable" 'the name of my Access table
    strFile = Dir(strPath & "*.txt")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile,
    ynFieldName
    ' Uncomment out the next code step if you want to delete the
    'file after it's imported
    ' Kill strPathFile
    strFile = Dir()
    Loop
    Exit_F:
    Exit Sub
     
    Douglas J Steele, Apr 28, 2012
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.