Import mutiple name text files into Access 2003

  • Thread starter Thread starter info
  • Start date Start date
I

info

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
 
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

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
 
Back
Top