PLEASE HELP!!! Error 53 / File Not Found

[jackadams]

Hello everyone. I had this code working last night, but then I closed the database and now it will not open the appropriate file "SourceFile ", which is a simple comma delimited text file that the user selects, for processing...

Here is the code that was working...
The line where it states the error is in BOLD font below...

Private Sub SCANFILE_DECODER()
    DoCmd.setWarnings False
     
    Dim LineData As String
    Dim RawID_NUMBER As String
    Dim ID_NUMBER As String
    Dim SourceFile As String
    Dim SQLString1 As String
    Dim SQLString2 As String
    Dim SQLString3 As String
    Dim SQLString4 As String
    Dim SQLString5 As String
    Dim SQLString6 As String
    Dim SQLString7 As String
    Dim NoOfDataRows As Integer
    Dim sDirectoryName As String
    Dim strPath As String
    
    Set cncurrent = CurrentProject.Connection
    Set rsDiag = New ADODB.Recordset
    
    sDirectoryName = BrowseDirectory("Find the directory where you want to import the required text files from.")
       
    'Set file directory for files to be imported
    strPath = sDirectoryName
    
    'Placeholder for the text file located in the user selected folder
    SourceFile = Dir(strPath & "\SG*.txt")
     'Deletes any existing data in the SCANFILE_FILE_TABLE_WITH_SSN_DODID
    SQLString1 = "DELETE * FROM SCANFILE_TABLE_WITH_SSN_DODID;"
     'Deletes any existing data in the SCANFILE_FILE
    SQLString2 = "DELETE * FROM SCANFILE_TABLE;"
    
    'Retrieves the data imported into the SCANFILE_TABLE and decodes the 18-digit character string into [SSN],[SSN5]
    '[SSN4] and [DOD_ID] and then writes the decrypted information into the CANFILE_TABLE_WITH_SSN_DODID table
    SQLString3 = "INSERT INTO SCANFILE_TABLE_WITH_SSN_DODID ( ID_NUMBER, SSN5, SSN4, DOD_ID ) " & _
                 " SELECT DISTINCT [SCANFILE_TABLE].[ID_NUMBER] AS ID_NUMBER, Left(decodeSSN([SCANFILE_TABLE].[ID_NUMBER]),5) AS SSN5, Right(decodeSSN([SCANFILE_TABLE].[ID_NUMBER]),4) AS SSN4, Right(decodeDOD_ID([SCANFILE_TABLE].[ID_NUMBER]),10) AS DOD_ID  " & _
                 " FROM SCANFILE_TABLE " & _
                 " WHERE NOT (([SCANFILE_TABLE].[ID_NUMBER]) LIKE '*CAC*');"
     'Update query that copies the decryped DOD_ID number from the SCANFILE_TABLE_WITH_SSN_DODID by matching SSN5 and SSN4
    'in both tables.  If there is no DOD_ID number in the SSN table corresponding to the matched SSNs, it will update the missing DOD_ID
    'corresponding to those SSNs as long as SSN5 does not start with '999' which is an invalid SSN (New CAC card).
    SQLString4 = "UPDATE [SSN] INNER JOIN [SCANFILE_TABLE_WITH_SSN_DODID] ON ([SSN].[SSN4] = [SCANFILE_TABLE_WITH_SSN_DODID].[SSN4]) AND ([SSN].[SSN5] = [SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) " & _
                 "SET [SSN].[DOD_ID] = [SCANFILE_TABLE_WITH_SSN_DODID].[DOD_ID] " & _
                 "WHERE NOT ((([SSN].[DOD_ID]) Is Null) AND (([SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) Like '999*'));"
    
    'This is an UPDATE query that copies the decryped DOD_ID number from the SCANFILE_TABLE_WITH_SSN_DODID by matching SSN5 and SSN4
    'in both tables.  If there is no DOD_ID number in the Trainees table corresponding to the matched SSNs, it will update the missing DOD_ID
    'corresponding to those SSNs as long as SSN5 does not start with '999' which is an invalid SSN (New CAC card).
    SQLString5 = "UPDATE [TRAINEES] INNER JOIN [SCANFILE_TABLE_WITH_SSN_DODID] ON ([TRAINEES].[SSN4] = [SCANFILE_TABLE_WITH_SSN_DODID].[SSN4]) AND ([TRAINEES].[SSN5] = [SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) " & _
                 "SET [TRAINEES].[DOD ID] = [SCANFILE_TABLE_WITH_SSN_DODID].[DOD_ID] " & _
                 "WHERE NOT ((([TRAINEES].[DOD ID]) Is Null) AND (([SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) Like '999*'));"
    
    'This is an UPDATE query that copies the decryped DOD_ID number from the SCANFILE_TABLE_WITH_SSN_DODID by matching SSN5 and SSN4
    'in both tables.  If there is no DOD_ID number in the Soldier Tracker table corresponding to the matched SSNs, it will update the missing DOD_ID
    'corresponding to those SSNs as long as SSN5 does not start with '999' which is an invalid SSN (New CAC card).
    SQLString6 = "UPDATE [Soldier_Tracker] INNER JOIN [SCANFILE_TABLE_WITH_SSN_DODID] ON ([Soldier_Tracker].[SSN4] = [SCANFILE_TABLE_WITH_SSN_DODID].[SSN4]) AND ([Soldier_Tracker].[SSN5] = [SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) " & _
                 "SET [Soldier_Tracker].[DOD_ID] = [SCANFILE_TABLE_WITH_SSN_DODID].[DOD_ID] " & _
                 "WHERE NOT ((([Soldier_Tracker].[DOD_ID]) Is Null) AND (([SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) Like '999*'));"
    
    DoCmd.runsql SQLString1
    DoCmd.runsql SQLString2
     
     'Loops through all the text files stored in the directory that was selected
    Do While SourceFile <> ""
       'This will process the user selected file by stripping the needed information out of the text file, which
       'is the first 18-digit character string in each line of the file.
       [B]Open SourceFile For Input As #1[/B] 'Opens the user selected text file
       SQLString7 = "Select * from SCANFILE_TABLE" 'Opens the table to insert the text file into
       rsDiag.Open SQLString7, cncurrent, adOpenDynamic, adLockOptimistic
    
       Do While Not EOF(1)
            Line Input #1, LineData 'Reads a line of data, which is everything in that line
            RawID_NUMBER = Trim(Left(LineData, 20))     'Trims the line that was just read into a 18-digit
                                                       'character string starting and ending with ""
            ID_NUMBER = Trim(Mid(RawID_NUMBER, 2, 18))  'Strips the "" from the 18-digit character string
          
            rsDiag.AddNew
            rsDiag!ID_NUMBER = ID_NUMBER
            rsDiag.Update   'Updates the table with the 18-digit character string
        Loop    'Continues to process each line until the end of the file has been reached
    
        Close #1 ' Close the data file
        rsDiag.Close
        
        SourceFile = Dir
    Loop
     DoCmd.runsql SQLString3
    DoCmd.runsql SQLString4
    DoCmd.runsql SQLString5
    DoCmd.runsql SQLString6
    
    'Creates a count of the number of rows in the SCANFILE_TABLE_WITH_SSN_DODID table
    NoOfDataRows = DCount("[SCANFILE_TABLE_WITH_SSN_DODID].[ID_NUMBER]", "[SCANFILE_TABLE_WITH_SSN_DODID]")
     MsgBox "Import Completed! " & vbCrLf & NoOfDataRows & " Rows Imported", vbOKOnly, "Text file import"
 End Sub

The location of the database and the text file has not changed since last night, so I have no idea why it will not process the same file that it processed the night before.

I wish I could send a sample of the database but I am working with the U.S. Army and the database I have holds very sensitive data, so I apologize in advance for only giving you partial information.

Any help would be greatly appreciated!

[jackadams]

Well, after hours of tooling around, I solved the issue. I had to change the following code in order for it to function properly... I will see if it works tomorrow!

[B][I]FROM[/I][/B]
    SourceFile = Dir(strPath & "\SG*.txt")

[B][I]TO[/I][/B]
    SourceFile = Dir(strPath & "\" & "*.txt")

[B][I][U]AND[/U][/I][/B]

[B][I]FROM[/I][/B]
    Open SourceFile For Input As #1

[B][I]TO[/I][/B]
    Open (sDirectoryName & "\" & SourceFile) For Input As #1

If anyone is having the same issues, feel free to contact me!

Have a great day!

[NeoPa]

It seems that it would only work otherwise if the Current Drive & Directory point to the folder the file is stored in.

Your update is worth it even with that understanding though. It's better not to rely on chance when you don't have to.

PS. I reset the Best Answer as your post doesn't really match what that is there for. We are also very circumspect about allowing members to set their own posts as Best Answer. It is allowed sometimes, but only when they come up with something fairly fundamental. NB. That should not be considered as any sort of criticism. It's a new arena for you and you clearly have a very tidy and disciplined approach to problems. I suspect that will stand you in very good stead in your future attempts at code development. Good luck.

[jackadams]

NeoPa,

Not sure what you mean when you stated "It seems that it would only work otherwise if the Current Drive & Directory point to the folder the file is stored in."

I use a browse4director y function to browse to the folder that the text files are located. I hope this is not an issue down the road!

Sorry about choosing as best answer... yes I am a newbie to forums, so my apologies. Even if it was criticism, I don't mind... I have to learn somehow, don't I? Thank you for your comments.

BTW I have another question that is now at How Can I Encrypt Text.

[NeoPa]

Not sure what you mean when you stated

I noticed your code was passing a name without the associated folder. In such a situation the Current Drive and Current Folder are used to look for the file within. If this is currently set the the folder the file is in then it will appear to work correctly. If not then it will fail.

Same code - Different result.

PS. No apologies necessary for the Best Answer thing. It's not something you'd be expected to know unless you'd already been told.