Error 53 / File Not Found (1 Viewer)

Public Function ExportRequest() As String
On Error GoTo err_Handler

Dim dbs As Database
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbs = CurrentDb()
Set frm = Forms!AOSummaryReportForm
' Open QueryDef object.
Set qdf = dbs.QueryDefs("AOSummary")
' Set parameters for query based on values entered
' in AOSummaryReportForm.
qdf.Parameters(0) = Forms!AOSummaryReportForm!StartDate
qdf.Parameters(1) = Forms!AOSummaryReportForm!EndDate


' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim rst As DAO.Recordset
Dim strSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabOne As Byte = 1
Const cStartRow As Byte = 2
Const cStartColumn As Byte = 1

DoCmd.Hourglass True


' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "\AOSummaryTemplate.xls"
sOutput = CurrentProject.Path & "\AOSummary.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)
strSQL = "SELECT * FROM AOSummary " & _
"WHERE APPROACH.Date_Field Between #" & [StartDate] & "#" & " And #" & [EndDate] & "#;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

 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.
       [COLOR=red]Open SourceFile For Input As #1[/COLOR] '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

 SELECT.... Where blnLogicallyDeleted = False ...
[COLOR="SeaGreen"]'This only returns Active (not logically deleted records)[/COLOR]

 [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