Automate .txt File import and assign Import ID field with filename

[Guest]

I am in the processes of creating an Access database that will import Text
file data into a cumulating table; however, to uniquely identify which
records came from which file, I created a field titled 'Import ID' in the
cumulative table were I would like the file name where the records came from
to be populated in this field (with or without the '.txt' file
extension--this part doesn't matter). The text files are named in this
sequence: 'Daily-20060601.txt'.

Does anyone know if this can be done, or, perhaps a better way to uniquely
identify each record by the date given in the file name? This piece would
need to be automated without manual intervention, becuase it will be utilized
by several users, to prevent any possible human error.

 

[Guest]

When you import external data to a table, it does not append to the existing
data, it replaces it. Also, given you wish to have a field to carry the name
of the file the data came from, a straight import would not do what you want.
To do this, I would suggest the following:

Create your table that will have the fields and data types you want to
import from the text files. Add the field you want to carry the filename in.

Create an Import Sprecification Name for the text files. You do this be
doing a manual import, and when you get the import dialog screen, click on
Advanced. Here you will be able to define the field names, data types,
delimiters, etc to tell Access how you would like the data formatted. When
you are done, click on Save As and give it a name. This name you will use in
the TransferText method to tell Access how to handle the file. See the
TransferText Method in VBA Help for Details.

Create an Append query that will copy the data from text file and add it to
the table. You can give it the file name in the column where you want the
file name to go, and it will put it in every record.

Rather than import the text file, link to it.
Run the Append query to move the data.
Use the DeleteObject method to delete the link to the text file.