Question Vista Task Scheduler fails to launch Access 07

[jps1320]

I am trying to use Vista's Task Scheduler to open an Access 2007 (.accdb) database on a daily schedule. Once open, the database has an AutoExec macro that will launch...run some code...then the last action in the macro is "Quit" to close Access.

This is the first time I have ever used Task Scheduler (on any version of Windows).

Everything works perfectly when I manually open the file.
Task Scheduler does attempt to perform the action, but fails.

Error Message is:
Task Scheduler failed to start instance "{17d769db-fd7c-41dd-9e6e-b45cea05c48e}" of "\mytestautomailer" task for user "JPS_PC\JPS-Admin" . Additional Data: Error Value: 2147942593.

I tried searching this site and have not found this issue. I also searched the web and only found a couple forum posts...however they weren't in English...doh!

Here's what I am thinking...
Since my task scheduler seems to be working and the Access database seems to be working...then my issue is how these two are working together.

I have tried setting the task to "Run with the Highest Permissions" and that did not work...thinking maybe it was getting denied access.

There is the option to put command line arguments in there, but I haven't found any information regarding needing to add any nor any information as to what would need to be added.

Hmmm...as I am typing that last part I am starting to wonder if I need to use Task Scheduler to open Access and then a command line argument to open the database...hmmm...I will explore this idea and post back the results.

Thank you in advance for your time and assistance.

JPS

 

[Pat Hartman]

I haven't tried to do this in quite some time but for some reason, when I did it, I used a .bat file to start the Access app. This was a couple of versions of Access and windows ago but perhaps the same issue exists.

 

[jps1320]

well...I think the idea of starting Access and then opening the file is the correct way to use the task scheduler, but still can't get it to work.

Here's what the task action is set to:
Program/Script: "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE"
Additional Arguments: "C:\Users\John-Admin\Documents\myfolder\mydatabasefile.accdb"

When I run this it starts...does NOT fail... but never completes either and the code I wrote never executes either.

So now I am wondering if it's opening Access and can't get the file open? Or it maybe it is trying to open Access and for some reason isn't able?

I don't know...gonna keep plugging away at this.

JPS

 

[jps1320]

Thanks, Pat.

I've seen info about making a .bat file and using Task Scheduler to open the .bat file, which launches the app and opens the file.

Most posts that I have seen say it can be done either way, especially since I am trying to do this locally with full admin privileges.

Maybe I'll give that a whirl next.

JPS

 

[jps1320]

Well...even though I have read that you can use Vista Task Scheduler to open Access and an Access Database, which in turn runs AutoExec macro upon opening...I couldn't get it to work. The closest I got was to get the database to open, but not run AutoExec. I even tried to append the following command line in the task action to force it to run the AutoExec macro:

/x AutoExec

So I went to plan B...I created a procedure called "Go" which simply calls my function (my function does all the actions of this project). Then I found examples of VBScript for launching Access procs and wrote this:

dim accessApp
set accessApp = CreateObject("Access.Application")
' accessApp.visible = True
accessApp.OpenCurrentDataBase("C:\Users\JPS-Admin\Documents\MyFolder\MyAccessDB.accdb")
accessApp.Run "Go"
accessApp.Quit
set accessApp = nothing

*I commented out the accessApp.visible line after testing was complete.

Then I used task scheduler to run the VBScript.

Worked perfectly.

Still unsure why I couldn't get Task Scheduler to do it directly, but the VBScript was pretty simple so I think I will be able to sleep tonight.

JPS

 

[Pat Hartman]

Did you ever try the .bat file or opening Access from VBScript?

 

[jps1320]

Didn't try the .bat file, but looks like it would work fine too. I'll tinker with it this weekend and post back so future seekers will get a full report.

JPS