File Creation Date on Linked or Imported Text File

[shaggy]

I have multiple text files I link with an import specification. Is there a way to include the file creation date in the data if I link or import these text files.

I currently import this data using sql in vba. The data from the linked text files is imported into another temporary table for processing. Some of the data in that temporary table is then sent to an archive. I would like to inlcude that file creation date in that archive.

Here's the code from my form. The user selects the cycle (drop down) which selects the linked text file from which the data will be imported.

Private Sub cmdImport_Click()
Dim strTable As String
Dim strSql As String
DoCmd.RunMacro ("mcrBcExc001")
strTable = Form_frmImportSelect.CmbCyc
strSql = "SELECT Dist, "
strSql = strSql & "IIf(Address1 Is Null,Null,Replace(Address1,'-',' ')) & Prefix & Size & '-' & Mtr AS Index1, "
strSql = strSql & "IIf(Address1 Is Null,Null,Replace(Address1,'-',' ')) & Size & '-' & Mtr AS Index2, "
strSql = strSql & "Acct, "
strSql = strSql & "IIf(MtrType1 = '1','E','W') AS EW, "
strSql = strSql & "MtrType2(MtrType1, Prefix, Size, Dials, Cmpdls, Dmt, Mmn) AS MeterType, "
strSql = strSql & "Prefix, "
strSql = strSql & "Size, "
strSql = strSql & "Mtr, "
strSql = strSql & "Status, "
strSql = strSql & "PhyOffType, "
strSql = strSql & "Location, "
strSql = strSql & "RdKind, "
strSql = strSql & "Dials, "
strSql = strSql & "MultiMtr, "
strSql = strSql & "Cmpdls, "
strSql = strSql & "K, "
strSql = strSql & "Class, "
strSql = strSql & "RdResp, "
strSql = strSql & "IIF(Address1 Is Null,Null,Replace(Address1,'-',' ')) AS Address, "
strSql = strSql & "Prem, "
strSql = strSql & "Comment, "
strSql = strSql & "DK, "
strSql = strSql & "Scale, "
strSql = strSql & "Notice, "
strSql = strSql & "[A/S], "
strSql = strSql & "Seq1, "
strSql = strSql & "Sic, "
strSql = strSql & "Rate, "
strSql = strSql & "Sis, "
strSql = strSql & "Svc, "
strSql = strSql & "Key, "
strSql = strSql & "Dmt, "
strSql = strSql & "Pid, "
strSql = strSql & "Dmn, "
strSql = strSql & "Rri, "
strSql = strSql & "Rte, "
strSql = strSql & "Mmn "
strSql = strSql & "INTO tblBcExc001Import "
strSql = strSql & "FROM " & strTable & ";"
DoCmd.RunSQL strSql
DoCmd.RunMacro ("mcrBcExc002")
End Sub

Thanks

 

[JHB]

Have you looked at the [FONT=&quot]FileDateTime Function, in the Help-file?[/FONT]