Visual Basic Run-time Error 76 (Path Not Found) - Task Scheduler Problem

Run-time 76 error (path not found) generated.  

Software/Versions Used: MS Access 2007 Macro, MS Server 2008 OS, Task Scheduler, SQL Server 2008

Process:
A scheduled task  is kicked off nightly.  It invokes an MS Access Macro (visual basic) that imports data into a SQL Server database.  The macro resides on a virtual server, "oemvm".  The MSS database and log files reside on the database server, "oeppodb1-pri1" (oeppodb1 alias).  The database server is mapped to the z: drive.  The VB code fails with a "path not found" on this instruction:

Open StartFilePathName For Output As #1

Snippets of code that precede instruction:
Dim StartFilePathName As String
'Location of ASCII export file
    PathName = "Z:\SAA_Export.txt"
'Location of log files
    StartFilePathName = "Z:\HISTORY\Start_SAA_Export-" & Date$ & ".txt"
    StatusFilePathName = "Z:\HISTORY\SAA_Export-" & Date$ & ".txt"

Code has executed since 2005.  Has not been changed.  Believe problem may be related to how the scheduled task is set up.  A colleague corrupted the task while I was on vacation.  Problem began occurring when I recreated the task.

The task is set up in Task Scheduler as follows:
Program/script - "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE"
Arguments - "C:\SAA_IMPORT_EXEC\Rosters SQL.mdb" /x "SAA_Import_Macro"
Start In - set to nothing...this may be part of the problem

Z: drive is mapped to \\oeppodb1-pri1\saa where "saa" is the share.  Same results when alias oeppodb1 is used.

Marisa StevensonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you directly run the "SAA_Import_Macro" by opening Access and running that macro, do you encouter any errors?
0
Jeffrey CoachmanMIS LiasonCommented:
<Believe problem may be related to how the scheduled task is set up.  A colleague corrupted the task while I was on vacation.>
...Then do you have a system backup you can restore the task from?
0
p912sCommented:
I've seen that error many times when the Start In directory is left blank. You should put "C:\SAA_IMPORT_EXEC\" in the Start in directory.

Hope that helps.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Marisa StevensonAuthor Commented:
No luck...
The macro runs great if I open Access and run the macro outside of Task Scheduler.
We may have a backup but I'd prefer not to perform a full restore.  Anyone know which specific files or directories to restore just the task?
I tried adding "C:\SAA_IMPORT_EXEC\" as the Start In directory.  Still got the run time error (path not found)
This task runs as user US\sqlprog.  Most events in task scheduler report "......for user 'US\sqlprog'".  However, one of the events specifies the local sqlprog account (doesn't include the US domain), which I don't believe is configured with appropriate privileges.  It says "Task Scheduler launched instance of task \Microsoft\MSAccess\SAA Import for user "sqlprog".  I am logged in as US\sqlprog, the task is configured to run as US\sqlprog.  When I launch manually it reports this on the launch.  May not have anything to do with the problem.  Oddly though, out of nowhere, Task Scheduler is no longer displaying Task History to me - says the account doesn't have privileges.  I didn't logout or change the account...was just changing "Start In" parameter.  Perhaps a fluke that will go away if I logout, but odd.
0
p912sCommented:
If you right-click and select Run on the scheduled task does it run ok?
0
Marisa StevensonAuthor Commented:
If I right-click and select "run" I get the Path Not Found error.
Please disregard my comment about the user in my previous post.  The event still shows "user" instead of "us\user" but I logged out and back in, and now am able to see the task details.
To summarize/clarify:
* MS Access 2007 Macro runs great on its own.  
* Macro fails when run via Task Scheduler (manually launched or event triggered).  
* Macro (vb) instruction fails when opening a non-existent file (should create the file if it doesn't exist)
* The "open" instruction (where it fails) specifies the directory path using a logical drive mapping: z:\history.  Z: is mapped to \\oeppodb1-pri1\saa.  "Saa" is a share.
* Task and macro reside on virtual server oemvm
* SQL Server 2008 backend resides on physical server oeppodb1-pri1
* Not aware of anything that has changed since the last time it ran except the task itself, which had to be recreated after a user corrupted it
* Task runs as user us\sqlprog
0
p912sCommented:
Will it run from a batch file?

Place this in a batch file, save and double-click.

 "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\SAA_IMPORT_EXEC\Rosters SQL.mdb" /x "SAA_Import_Macro"

If yes, then call the batch file from the Task Scheduler.
0
Jeffrey CoachmanMIS LiasonCommented:
Agin, if you have a system backup/image, ...etc from before the user corrupted it, you should be able to resolve this.

...or did I miss something...?

Jeff
0
Marisa StevensonAuthor Commented:
Hi Jeff,
Sorry, I responded in a prior post but perhaps it wasn't clear.  I am looking into restoring from Backup but am trying to avoid performing a full restore.  Do you know which specific files/directories we'd have to restore?  Does Task Scheduler store info in the registry (will we have to modify registry entries?)?
Thanks!
Marisa
0
Marisa StevensonAuthor Commented:
This is strange.  I just noticed that the task appears to continue running, despite the path not found.  It doesn't write the log files to oeppodb1-pri1\history, but it triggers and runs.  I tried creating another task that invokes the macro - both have same problem.
0
p912sCommented:
Marisa,

Windows XP store them in C:\WINDOWS\Tasks

Have you tried creating a batch file to test?

Scot

0
Marisa StevensonAuthor Commented:
Am testing batch in a minute.
0
Marisa StevensonAuthor Commented:
The batch appears to be running.  I'll confirm and attempt to create a task that invokes the batch job.  Any thoughts about why running a batch from Task Scheduler might work, but invoking the macro from Task Scheduler doesn't find the path?
0
p912sCommented:
Because the Task Scheduler can only launch programs. And apparently that complex statement is confusing it...
0
Marisa StevensonAuthor Commented:
Any idea why it would have worked previously?  
0
p912sCommented:
When you entered the task did it look like this in th Run box?

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\SAA_IMPORT_EXEC\Rosters SQL.mdb" /x "SAA_Import_Macro"
0
Marisa StevensonAuthor Commented:
Program was "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE"
Arguments were "C:\SAA_IMPORT_EXEC\Rosters SQL.mdb" /x "SAA_Import_Macro"

But guess what...I tried to launch the batch job from Task Scheduler...and same problem
0
p912sCommented:
When you added the task for the batch file, did you put the path in Start In?

When you say Arguments? Doesn't your Task window look like this?


Capture.JPG
0
p912sCommented:
Just dawned on me "arguments"; you're actually launching this from the server?

I  always run these sorts of tasks from an administrators workstation....
0
Marisa StevensonAuthor Commented:
I am running this from the server where the macro resides.  Running Windows Server 2008 OS.  Screen looks like this.
 
 

TaskScreen.docx
0
p912sCommented:
What happens if you put this entire statement in the Run box? Leave arguments blank.

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\SAA_IMPORT_EXEC\Rosters SQL.mdb" /x "SAA_Import_Macro"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marisa StevensonAuthor Commented:
The problem was resolved by deselecting the "run with highest privileges" flag.  When I put the entire statement in one box, Task Scheduler created a task exactly like the ones before - it determined arguments and set them.  Since I was in a rush, I didn't set the flag.  When it ran, despite being set up exactly like those that didn't run, I started checking for discrepancies.  Further tests confirmed this was the problem.
Would have never figured this out alone.  You all are great - wish I had your brains.  Thanks!
0
Marisa StevensonAuthor Commented:
Proposed solution led me to resolution.  When solution was implemented, it worked.  I realized the only difference between this task and previous ones was that this one did not have the "run with highest privileges" flag set.  I removed the flag from the problematic task and it began working.
0
p912sCommented:
Glad it's working and that I could be of assistance.

Thanks for the points and grade!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.