D
derekrap
Guest
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:
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
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
Code:
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
Code:
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