Attaching filename to a field while importing (1 Viewer)

[derekrap]

Hello, I've searched everywhere for an answer but couldn't find one specific to mine. I have the import code all set up and it is working perfectly, however, because I have multiple files i'm importing, I want to label a field in my database with the filename that is being imported. Here's my code that i've pieced together through different examples:

Private Sub Command0_Click()

Dim InputDir, ImportFile As String, tblName As String, FinalName As String
Dim InputMsg As String
InputDir = "\\2000cluster\Company\Derek\Fidelity Updates\"
ImportFile = Dir(InputDir & "\*.txt")
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM FidelityImports"

Do While Len(ImportFile) > 0
DoCmd.TransferText acImportFixed, "Fidelity Import Specification", "FidelityImports", InputDir & ImportFile, True
DoCmd.RunSQL "UPDATE FidelityImports SET RecordID =" & "'" & ImportFile & "'"
ImportFile = Dir
MsgBox "the import file is" & " " & ImportFile

Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile "\\2000cluster\Company\Derek\Fidelity Updates\*.txt", "\\2000cluster\Company\Derek\Fidelity Updates\imported files\"
DoCmd.SetWarnings True
Loop
MsgBox "Update Complete"
End Sub

what is happening is the SQL statement to record the filename in the field is showing the filename of the last file imported instead of the filename it is currently working on in the loop

DoCmd.RunSQL "UPDATE FidelityImports SET RecordID =" & "'" & ImportFile & "'"

for i.e. if i have 5 files that are in the directory that i want to import:
1
2
3
4
5

when i run my code, 5 will show up in the RecordID field for ALL records that were imported. I want to for example import file 1 and then label all of file 1 with it's filename, then import file 2 and then label it with the filename of file 2

hope someone can help me out - let me know if i'm not clear

 

[Bat17]

Hi, Welcome to the board
You were close! you just need to filter for null ( or possibly "") when you update.

"UPDATE FidelityImports SET RecordID =" & "'" & ImportFile & "' where RecordID is null"

HTH

Peter

 

[derekrap]

Thank You!

Pure and utter genious!

Thank you SOOO much!

 

[xgd]

Greetings,
Excuse me resurrecting such an old post, but I do have a follow-up question.
And it was brillant and exactly what I needed.

Let's say I have a filename of "city_name010120071530.txt"
Using the code in the post, how could I trim "city_name" and "1530.txt" so that the imported field would be 01012007

Thanks in advance,

--gd

 

[xgd]

Should have tried longer brfore posting.
I figured it out, here is how I did it. (Although, I am sure there is a better way, I am still in the learning phase)
Before:

"UPDATE FidelityImports SET RecordID =" & "'" & ImportFile & "' where RecordID is null"

After:

"UPDATE FidelityImports SET RecordID = Mid(" & "'" & ImportFile & "', 10,8) where RecordID is null"