By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

PLEASE HELP!!! Error 53 / File Not Found

P: 4
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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub SCANFILE_DECODER()
  2.     DoCmd.setWarnings False
  3.  
  4.     Dim LineData As String
  5.     Dim RawID_NUMBER As String
  6.     Dim ID_NUMBER As String
  7.     Dim SourceFile As String
  8.     Dim SQLString1 As String
  9.     Dim SQLString2 As String
  10.     Dim SQLString3 As String
  11.     Dim SQLString4 As String
  12.     Dim SQLString5 As String
  13.     Dim SQLString6 As String
  14.     Dim SQLString7 As String
  15.     Dim NoOfDataRows As Integer
  16.     Dim sDirectoryName As String
  17.     Dim strPath As String
  18.  
  19.     Set cncurrent = CurrentProject.Connection
  20.     Set rsDiag = New ADODB.Recordset
  21.  
  22.     sDirectoryName = BrowseDirectory("Find the directory where you want to import the required text files from.")
  23.  
  24.     'Set file directory for files to be imported
  25.     strPath = sDirectoryName
  26.  
  27.     'Placeholder for the text file located in the user selected folder
  28.     SourceFile = Dir(strPath & "\SG*.txt")
  29.      'Deletes any existing data in the SCANFILE_FILE_TABLE_WITH_SSN_DODID
  30.     SQLString1 = "DELETE * FROM SCANFILE_TABLE_WITH_SSN_DODID;"
  31.      'Deletes any existing data in the SCANFILE_FILE
  32.     SQLString2 = "DELETE * FROM SCANFILE_TABLE;"
  33.  
  34.     'Retrieves the data imported into the SCANFILE_TABLE and decodes the 18-digit character string into [SSN],[SSN5]
  35.     '[SSN4] and [DOD_ID] and then writes the decrypted information into the CANFILE_TABLE_WITH_SSN_DODID table
  36.     SQLString3 = "INSERT INTO SCANFILE_TABLE_WITH_SSN_DODID ( ID_NUMBER, SSN5, SSN4, DOD_ID ) " & _
  37.                  " 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  " & _
  38.                  " FROM SCANFILE_TABLE " & _
  39.                  " WHERE NOT (([SCANFILE_TABLE].[ID_NUMBER]) LIKE '*CAC*');"
  40.      'Update query that copies the decryped DOD_ID number from the SCANFILE_TABLE_WITH_SSN_DODID by matching SSN5 and SSN4
  41.     '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
  42.     'corresponding to those SSNs as long as SSN5 does not start with '999' which is an invalid SSN (New CAC card).
  43.     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]) " & _
  44.                  "SET [SSN].[DOD_ID] = [SCANFILE_TABLE_WITH_SSN_DODID].[DOD_ID] " & _
  45.                  "WHERE NOT ((([SSN].[DOD_ID]) Is Null) AND (([SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) Like '999*'));"
  46.  
  47.     'This is an UPDATE query that copies the decryped DOD_ID number from the SCANFILE_TABLE_WITH_SSN_DODID by matching SSN5 and SSN4
  48.     '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
  49.     'corresponding to those SSNs as long as SSN5 does not start with '999' which is an invalid SSN (New CAC card).
  50.     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]) " & _
  51.                  "SET [TRAINEES].[DOD ID] = [SCANFILE_TABLE_WITH_SSN_DODID].[DOD_ID] " & _
  52.                  "WHERE NOT ((([TRAINEES].[DOD ID]) Is Null) AND (([SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) Like '999*'));"
  53.  
  54.     'This is an UPDATE query that copies the decryped DOD_ID number from the SCANFILE_TABLE_WITH_SSN_DODID by matching SSN5 and SSN4
  55.     '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
  56.     'corresponding to those SSNs as long as SSN5 does not start with '999' which is an invalid SSN (New CAC card).
  57.     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]) " & _
  58.                  "SET [Soldier_Tracker].[DOD_ID] = [SCANFILE_TABLE_WITH_SSN_DODID].[DOD_ID] " & _
  59.                  "WHERE NOT ((([Soldier_Tracker].[DOD_ID]) Is Null) AND (([SCANFILE_TABLE_WITH_SSN_DODID].[SSN5]) Like '999*'));"
  60.  
  61.     DoCmd.runsql SQLString1
  62.     DoCmd.runsql SQLString2
  63.  
  64.      'Loops through all the text files stored in the directory that was selected
  65.     Do While SourceFile <> ""
  66.        'This will process the user selected file by stripping the needed information out of the text file, which
  67.        'is the first 18-digit character string in each line of the file.
  68.        Open SourceFile For Input As #1 'Opens the user selected text file
  69.        SQLString7 = "Select * from SCANFILE_TABLE" 'Opens the table to insert the text file into
  70.        rsDiag.Open SQLString7, cncurrent, adOpenDynamic, adLockOptimistic
  71.  
  72.        Do While Not EOF(1)
  73.             Line Input #1, LineData 'Reads a line of data, which is everything in that line
  74.             RawID_NUMBER = Trim(Left(LineData, 20))     'Trims the line that was just read into a 18-digit
  75.                                                        'character string starting and ending with ""
  76.             ID_NUMBER = Trim(Mid(RawID_NUMBER, 2, 18))  'Strips the "" from the 18-digit character string
  77.  
  78.             rsDiag.AddNew
  79.             rsDiag!ID_NUMBER = ID_NUMBER
  80.             rsDiag.Update   'Updates the table with the 18-digit character string
  81.         Loop    'Continues to process each line until the end of the file has been reached
  82.  
  83.         Close #1 ' Close the data file
  84.         rsDiag.Close
  85.  
  86.         SourceFile = Dir
  87.     Loop
  88.      DoCmd.runsql SQLString3
  89.     DoCmd.runsql SQLString4
  90.     DoCmd.runsql SQLString5
  91.     DoCmd.runsql SQLString6
  92.  
  93.     'Creates a count of the number of rows in the SCANFILE_TABLE_WITH_SSN_DODID table
  94.     NoOfDataRows = DCount("[SCANFILE_TABLE_WITH_SSN_DODID].[ID_NUMBER]", "[SCANFILE_TABLE_WITH_SSN_DODID]")
  95.      MsgBox "Import Completed! " & vbCrLf & NoOfDataRows & " Rows Imported", vbOKOnly, "Text file import"
  96.  End Sub
  97.  
  98.  
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!
Nov 24 '13 #1
Share this Question
Share on Google+
4 Replies


P: 4
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!

Expand|Select|Wrap|Line Numbers
  1.  
  2. FROM
  3.     SourceFile = Dir(strPath & "\SG*.txt")
  4.  
  5. TO
  6.     SourceFile = Dir(strPath & "\" & "*.txt")
  7.  
  8. AND
  9.  
  10. FROM
  11.     Open SourceFile For Input As #1
  12.  
  13. TO
  14.     Open (sDirectoryName & "\" & SourceFile) For Input As #1
  15.  
If anyone is having the same issues, feel free to contact me!

Have a great day!
Nov 24 '13 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 24 '13 #3

P: 4
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 browse4directory 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.
Nov 25 '13 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 25 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.