How do I Save/Import .txt file name to Access when importing?

[PJ Crittenden]

I'm loading one file at a time and I need to also save the file name to the database. How do I do that?

Thanks.

[ranman256]

If you are running transfertextfile, and you have the SPECname defined in import specifications,
Link the text file as a table, then import that table to an internal table.

In the code:

vFile = txtFilePath

  'link the file as a table
DoCmd.TransferText acLinkDelim, SPECname, "txtTable2Import", vFile, True

   'run the append query to put the text file data into a table.  IN THE QUERY HAVE A FIELD THAT WRITES THE FILE PATH from the txtBox.
  'as  forms!frmImport!txtFilePath

   'append data
docmd.openquery "qaAppendData"

[PJ Crittenden]

I am not using the import wizard. There is a reason I have to process one file at a time... So, there is ONE file in a folder on a network drive. I have to change the name of that file in order to process it, but BEFORE I change it, I want to save the ORIGINAL file name to a table in Access.

Could you please help me with that?

[ranman256]

You HAVE to process 1 file at a time. No other way.

You do NOT have to change the name of the file to process it.
1. Put the filename in the text box,
2. the DoCmd.TransferText LINE PUTS THE PICKED FILE AS txtTable2Import TABLE. (but I didnt give you the delete line), so...

const  kTMPtbl =  "txtTable2Import"
dim vFile

vFile = txtFilePath
   'delete the previous table
CurrentDb.TableDefs.Delete kTMPtbl 

  'link the file as a table
DoCmd.TransferText acLinkDelim, SPECname, kTMPtbl, vFile, True
   'run the append query to put the text file data into a table.  IN THE QUERY HAVE A FIELD THAT WRITES THE FILE PATH from the txtBox.
   'as  FILENAME: forms!frmImport!txtFilePath

   'append data
docmd.openquery "qaAppendData"