I have several txt files that I import on a regular basis using an import
routine. But I have no way of identifying which file they came from if I have
to back track. I want to be able adjust my import routine so that it will
place the name of the file in a field the table its creating. Below is the
import routine I use.


Option Compare Database

Function btnImportAllA4s()
Dim strfile As String

ChDir ("D:\FTR\")
strfile = Dir("*.*")
Do While Len(strfile) > 0
DoCmd.SetWarnings False
DoCmd.TransferText acImportFixed, "FTR", _
"DLA_A4s", "D:\FTR\" & strfile, False
Kill "D:\FTR\" & strfile
strfile = Dir
Loop

End Function

Thank you in advance for assistance you can provide me.

--
dbguy11

Well, how about an executing an Update query immediately after the
TransferText statement.

CurrentDb.execute "UPDATE DLA_A4s SET FileName = '" & _
strfile & "' WHERE FileName IS NULL", dbFailOnError

This assumes you already have a field called FileName in the table and every
record will have a value except when a new file is imported.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"dbguy11" <(E-Mail Removed)> wrote in message
news:48BD817F-353B-4AA4-B6A6-(E-Mail Removed)...
>I have several txt files that I import on a regular basis using an import
> routine. But I have no way of identifying which file they came from if I
> have
> to back track. I want to be able adjust my import routine so that it will
> place the name of the file in a field the table its creating. Below is the
> import routine I use.
>
>
> Option Compare Database
>
> Function btnImportAllA4s()
> Dim strfile As String
>
> ChDir ("D:\FTR\")
> strfile = Dir("*.*")
> Do While Len(strfile) > 0
> DoCmd.SetWarnings False
> DoCmd.TransferText acImportFixed, "FTR", _
> "DLA_A4s", "D:\FTR\" & strfile, False
> Kill "D:\FTR\" & strfile
> strfile = Dir
> Loop
>
> End Function
>
> Thank you in advance for assistance you can provide me.
>
> --
> dbguy11

Thank you very much it works great! I will be using this on several of my dbs
its going to come in very handy.
--
dbguy11


"Roger Carlson" wrote:

> Well, how about an executing an Update query immediately after the
> TransferText statement.
>
> CurrentDb.execute "UPDATE DLA_A4s SET FileName = '" & _
> strfile & "' WHERE FileName IS NULL", dbFailOnError
>
> This assumes you already have a field called FileName in the table and every
> record will have a value except when a new file is imported.
>
>
> --
> --Roger Carlson
> MS Access MVP
> Access Database Samples: www.rogersaccesslibrary.com
> Want answers to your Access questions in your Email?
> Free subscription:
> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>
>
> "dbguy11" <(E-Mail Removed)> wrote in message
> news:48BD817F-353B-4AA4-B6A6-(E-Mail Removed)...
> >I have several txt files that I import on a regular basis using an import
> > routine. But I have no way of identifying which file they came from if I
> > have
> > to back track. I want to be able adjust my import routine so that it will
> > place the name of the file in a field the table its creating. Below is the
> > import routine I use.
> >
> >
> > Option Compare Database
> >
> > Function btnImportAllA4s()
> > Dim strfile As String
> >
> > ChDir ("D:\FTR\")
> > strfile = Dir("*.*")
> > Do While Len(strfile) > 0
> > DoCmd.SetWarnings False
> > DoCmd.TransferText acImportFixed, "FTR", _
> > "DLA_A4s", "D:\FTR\" & strfile, False
> > Kill "D:\FTR\" & strfile
> > strfile = Dir
> > Loop
> >
> > End Function
> >
> > Thank you in advance for assistance you can provide me.
> >
> > --
> > dbguy11
>
>
> .
>