Add file name to table when importing several txt files using an-

[dbguy11]

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.

 

[Roger Carlson]

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/wa.exe?SUBED1=ACCESS-L

 

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