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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. J S

    Mutiple user access to access?

    J S, Feb 7, 2004, in forum: Access VBA Modules
    Replies:
    1
    Views:
    72
    John Spencer (MVP)
    Feb 7, 2004
  2. Dave

    delete mutiple objects at once

    Dave, May 24, 2004, in forum: Access VBA Modules
    Replies:
    3
    Views:
    98
    Chris Nebinger
    May 24, 2004
  3. Steven M. Britton

    Mutiple items selected in a list box

    Steven M. Britton, Jun 19, 2004, in forum: Access VBA Modules
    Replies:
    1
    Views:
    66
    Alex Dybenko
    Jun 19, 2004
  4. Louise
    Replies:
    1
    Views:
    167
    Allan Murphy
    Mar 2, 2006
  5. Tony_VBACoder

    ACC 2000: VBA Import Text Files into Memo field

    Tony_VBACoder, Mar 15, 2007, in forum: Access VBA Modules
    Replies:
    3
    Views:
    135
    Dirk Goldgar
    Mar 15, 2007
  6. Ingrid L.
    Replies:
    2
    Views:
    90
    Klatuu
    Jan 8, 2008
  7. Blakey300

    Adding Mutiple Where Statements to Code

    Blakey300, May 15, 2009, in forum: Access VBA Modules
    Replies:
    9
    Views:
    97
    Blakey300
    May 15, 2009
  8. The Flash

    Appending mutiple reports to PDF using VBA.

    The Flash, Nov 2, 2009, in forum: Access VBA Modules
    Replies:
    4
    Views:
    198
    The Flash
    Nov 3, 2009
Loading...