Help with Microsoft Jet Database Error - Code Assistance

[Chris Parris]

Hello,

I receive runtime error 3011 Microsoft Jet database could not find object 'downloadCLASSACT HOLDINGS.txt'...The section of code the debugger highlights as the error is the first DoCmd TransferText line:

Private Sub Get_Report_Click()
'On Error GoTo Err_Get_Data_Click
DoCmd.SetWarnings (off)

Input_file_Path = "\\msad\root\na\ny\lib\mssb\Trust\PTMKT\TRUST ADMINISTRATION\CUSTODY\CLASS ACTIONS\DOWNLOADS"
'****************** start test **********

ReportTbl = "[CLASSACT HOLDINGS]"
DoCmd.RunSQL "Delete from " & ReportTbl & " ;"
Input_file_Name = "CLASSACT HOLDINGS.txt"
Table_Name = "CLASSACT HOLDINGS"
spec = "CLASSACT HOLDINGS Import Specification"
DoCmd.TransferText acImportFixed, spec, Table_Name, Input_file_Path & Input_file_Name
ReportTbl = "[CLASSACT History to date L1]"
DoCmd.RunSQL "Delete from " & ReportTbl & " ;"
ReportTbl = "[CLASSACT History to date L2]"
DoCmd.RunSQL "Delete from " & ReportTbl & " ;"

I do have write permission to the file path and have tried creating a path to my local drive and recompiling the code. Still received the error. I ran regedit and see that I do not have an 'Engine' folder in subdirectory Jet, 4.0. Could this be the culperate? The entire code is attached for reference.

Thank you.
Chris

[beacon]

Hi Chris,

Welcome to Bytes!

It looks like you don't have a "\" after "DOWNLOADS" in your Input_File_Path, so the code thinks it's looking for a file at:

If Input_file_Path = "\\msad\root\na\ny\lib\mssb\Trust\PTMKT\TRUST ADMINISTRATION\CUSTODY\CLASS ACTIONS\DOWNLOADS"
 
And
 
Input_file_Name = "CLASSACT HOLDINGS.txt"
 
Then
'Notice how the path and file name run together
\\msad\root\na\ny\lib\mssb\Trust\PTMKT\TRUST ADMINISTRATION\CUSTODY\CLASS ACTIONS\DOWNLOADSCLASSACT HOLDINGS.txt
 

You can either add the "\" to the end of the file path variable, the beginning of the file name variable, or you can add it to the DoCmd.TransferText line of code like this:

DoCmd.TransferText acImportFixed, spec, Table_Name, Input_file_Path & "\" & Input_file_Name