Error 53 / File Not Found

[china99boy]

Hi guys, I have been using the below code for several months to export to excel. Lately I get a run-time error 53 - File not Found. I have not changed anything, so what could be causing this issue. The debugger stops on the line "FileCopy sTemplate, sOutput" Thanks in advance.

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)

 

[Moniker]

Either the name of the template changed, or its location got moved.

 

[china99boy]

Thanks for your quick response, I checked and re-checked, but the name of the template and locations are still the same. For some reason, some users can run the export fine, but some cannot including myself.

 

[Moniker]

Did the path that the DB is stored in change? Maybe to a temporary "development" folder or something? The problem is in the CurrentProject.Path & "\<file_name_here>" statements.

Put a breakpoint on that line, open the immediate window (press Ctrl+G), and type in:

?CurrentProject.Path & "\<your filename_here>"

This will return the path it's trying to use. Open a Windows Explorer (press Windows+E), and copy paste in the path from the immediate window into the addess bar of the explorer window. If Windows Explorer can find this path just fine, then there's something else wrong. If it cannot, then you know the path and/or filename is wrong.

 

[china99boy]

Ok, followed your steps. The path that it is looking in is not where the actual Template is located. How do I tell it to look where the template is and not in the current project path? Thanks

 

[Moniker]

You either have to hardcode the path (instead of using CurrentProject.Path, fully type out "C:\<folder>\<subfolder>\...\<your_filename>"), or you can move your database to the same folder that the template is in. This makes it so that CurrentProject.Path points to the correct location.

 

[china99boy]

Ok, I will have to try the hard code method. The database is split, FE/BE. All the users have the front end MDE file on there desktop, so I wont be able to place the template in the same folder. Thank you for you assistance and patient

 

[boblarson]

Ok, I will have to try the hard code method. The database is split, FE/BE. All the users have the front end MDE file on there desktop, so I wont be able to place the template in the same folder. Thank you for you assistance and patient

Actually, what I would do (and have done in the past) is to create a table to hold information like that and use it to store the location. Then you can change it quickly and easily without having to touch code again. You can use a DLookup to get the location.

 

[china99boy]

Wow, that sounds interesting, do you by chance have an example floating around?

 

[boblarson]

I just threw together a sample. Here you go.

 

[jackadams]

PLEASE HELP!!! Error 53 / File Not Found

Hello everyone. I had this code working last night, but then I closed the database and now it will not open the appropriate file for processing...

Here is the code that was working...
The line where it states the error is in red 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.
       [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

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!

 

[jdraw]

A couple of points:

You have added your post to an older message. Have you looked at some of the suggestions there? Did you see Bob Larson's suggestion?(Bob is well respected in AWF)

Whatever your Sourcefile, you should always make a backup copy and keep it safe. Always work with a copy of your data while testing/developing.

In many systems, the recommendation is to NOT physically delete records. You add a boolean(Yes/No, On/Off, True/False) field (say blnLogicallyDeleted) to a table, and you turn this to TRUE to signify that this record is logically deleted, and should not participate in queries, forms , reports etc.
In any queries or recordsources you

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

When testing your code, DON'T SetWarnings False. That just suppresses any/all error messages, so you're flying blind.

During testing, make use of Debug.Print statements to show the value of certain fields/variables. Not only does this show the values, but confirms the code has been executed.

When posting, give the user an overview of your application (a 30000 ft level picture) since we know only what you tell us. If you just give us code, we have to decipher that as well as guess what you are trying to do. Often you can tell us what your "business/application" is just as you would tell someone who doesn't know you, or your environment. You don't have to tell us about Access necessarily, until we understand WHAT you are trying to achieve. Once you give us things in terms of Access, you're really showing us HOW you have done something, and that something isn't working as expected.

Here's a link to other debugging ideas.

Readers are quite comfortable with a dumbed-down (no confidential data/private data) for reviewing issues. We've done a lot of reviewing/debugging/assisting with Customers/Employees like Porky Pig, Daffy Duck, John Doe, General Purpose and Private Parts.

Good luck with your project.

 

[jackadams]

jdraw,

Thank you for the tips. First, this is a test database with all information backed up to a different location. Secondly, I turned off the warning messages because in the various SQL statements, it deletes all the information in my TEMPORARY tables and the rewrites information from a user selected "SourceFile", which is a simple comma delimited text file. This "SourceFile" is then passed through a series of SQL statements to decode the information in that text file.

My only confusion is why it would work last night without a hitch and then when I reopen the database, the same code would not get past the opening of the file where all information has stayed in the same locations (i.e. database, text file)

Any other suggestions?

Thanks in advance!

 

[jdraw]

Is it possible that the file was
a) deleted
b) moved to a different location

Error 53 is quite explicit.

 

[jackadams]

The database and the file are still in the same location that they were last night. I also put in a user select option so that if the files move, the user will select the directory where they put the SoureFile. The outer loop searches for all files starting with SG and ending in .txt. When I put a watch on the SoureFile, it shows the appropriate file and the same goes for the directory storing that SourceFile. :banghead:

I am going to restart the computer to see if this makes a difference... stranger things have happened! Thanks in advance!

 

[jdraw]

Any update on this???

 

[jackadams]

jdraw, fixed the issue! 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

 

[jdraw]

Well done---perseverance is key!

 

[jackadams]

I have found out that there are extreme highs and extreme lows when programming... where is the in between?