Your code wouldn't execute as posted.....the path needed a backslash.
I added code to add the CSV file date to the import table.
#### TEST THIS CODE ON A COPY OF THE DB. ####
Don't say I didn't warn you!!!
** You need to have a reference set to "Microsoft DAO 3.6 Object Library"
In the IDE, click on TOOLS/References. Scroll down until you see "Microsoft DAO 3.6 Object Library" and check it.
The function "FileDateTime" is what gets the file date/time. (duh) "FileDateTime" is part of "Microsoft DAO 3.6 Object Library"
** You will have to change the name of the date field in the code. I didn't know what your name for the field is, soooo... I made up a name
Const strcFieldName As String = "FileDate" <<-- change this in the code
Here is the modified code
Code:
Sub Import_PDF()
Const strcPath As String = "C:\Users\bkh\Desktop\Web Unload\PDF\" '<<- backslash required
Const strcTableName As String = "tbl_pdf" 'import table
Const strcFieldName As String = "FileDate" '<<-- change the name of the date field to the actual name in the table "tbl_pdf"
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String
Dim sSQL As String
Dim dtFileDate As Date
strFile = Dir(strcPath & "*.csv")
While strFile <> ""
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
If intFile = 0 Then
MsgBox strcPath & vbNewLine _
& "The above directory contains no CSV files.", _
vbExclamation + vbOKOnly, "Program Finished"
Else
For intFile = 1 To UBound(strFileList)
strFullPath = strcPath & strFileList(intFile)
'get file date
dtFileDate = FileDateTime(strFullPath)
'import CSV file
DoCmd.TransferText acImportDelim, "spc_pdf", strcTableName, strFullPath, True
' Debug.Print strFileList(intFile) & " / " & dtFileDate
'create the SQL to update the date field
sSQL = "UPDATE " & strcTableName
sSQL = sSQL & " SET " & strcFieldName & " = #" & dtFileDate & "#"
sSQL = sSQL & " WHERE " & strcFieldName & " Is Null;"
' Debug.Print sSQL
'update new records with file date
CurrentDb.Execute sSQL, dbFailOnError
Next
MsgBox UBound(strFileList) & " file(s) were imported", _
vbOKOnly + vbInformation, "Program Finished"
End If
End Sub