Importing text files and add create date of the file to access table using VBA

[Private_email]

I have the below code and it works great, however, I want to be able to pull in the File Create date from the properties of the files that are being imported and write that to the date field in the import table. I do not know VBA per se, this coded I modified from something I found online.

Sub Import_PDF()
Const strcPath As String = _
"C:\Users\bkh\Desktop\Web Unload\PDF"
Const strcTableName As String = "tbl_pdf"

Dim strPath As String
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String

If Right(strcPath, 1) = "" Then
strPath = strcPath
Else
strPath = strcPath & ""


End If

strFile = Dir(strPath & "*.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"

GoTo Exit_Import_From_Excel

End If

For intFile = 1 To UBound(strFileList)

strFullPath = strPath & strFileList(intFile)
DoCmd.TransferText acImportDelim, "spc_pdf", strcTableName, strFullPath, True, ""

Next

MsgBox UBound(strFileList) & " file(s) were imported", _
vbOKOnly + vbInformation, "Program Finished"

Exit_Import_From_Excel:
Exit Sub
End Sub

[ssanfu]

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

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

[Private_email]

Thank you, Steve,
I copied the above code, changed the field name, and I get a syntax error on this line:
CurrentDb.Execute sSQL, dbFailOnError

Any ideas?

[ssanfu]

I had to comment out the "TransferText" line, but I just tested the loop with the "CurrentDb.Execute" line and did not get any errors.

Did you set a reference set to "Microsoft DAO 3.6 Object Library"?

** 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"

[Private_email]

Yes, I did set that reference......I will try the same and comment out the transfer text line. Thank you!!

[ssanfu]

You need the "TransferText" line. I had to comment it out because I don't know what fields are in the CSV file and I don't know what the import specification is.
I was just testing the "Currentdb.Execute" line.

In the IDE, did you compile the code? (Under the DEBUG menu option)
Have you done a "Compact and Repair" lately?