How to run an Access script using windows task scheduler

Question

I want to execute a timer-triggered function in my local development environment (Node, OS X) but it seems to require some changes to the HTTP-triggered functions setup I have.

Here's the code related to the timer function so far:

/cron-job/function.json defines a timer input binding scheduled to run every minute. It also has a reference to the code entry point (compiled from Typescript):

{
  "bindings": [
    {
      "type": "timerTrigger",
      "direction": "in",
      "name": "timer",
      "schedule": "0 */1 * * * *"
    }
  ],
  "scriptFile": "../dist/cron-job/index.js"
}

/cron-job/index.ts

import { AzureFunction, Context } from '@azure/functions'

const timerTrigger: AzureFunction = async function (
  context: Context,
  timer: any,
) {
  console.log('context', context)
  console.log('timer', timer)

  // move on with async calls...
}

export default timerTrigger

/local.settings.json

{
  "IsEncrypted": false,
  "Values": {
    "FUNCTIONS_WORKER_RUNTIME": "node",
    "AzureWebJobsStorage": ""
  }
}

When I try to start the function app:

 ~/Projects/test-api (dev) $ func start --verbose

I get an error:

Missing value for AzureWebJobsStorage in local.settings.json. This is required for all triggers other than httptrigger, kafkatrigger. You can run 'func azure functionapp fetch-app-settings <functionAppName>' or specify a connection string in local.settings.json.

When I add AzureWebJobsStorage setting to the local.settings.json I get another error:

The listener for function 'Functions.cron-job' was unable to start.
The listener for function 'Functions.cron-job' was unable to start. Microsoft.Azure.Storage.Common: Connection refused. System.Net.Http: Connection refused. System.Private.CoreLib: Connection refused.

Solution

I believe you must select "Run only when user is logged on" in your Windows Task Scheduler Job Properties. You must also be logged on to automatically have scheduled Access macros run.

This really seems like a huge limitation of Access, but I have not seen another solution out there. It would be great to have a solution for running an Access macro while logged off.

Comments

[Yass]

Have you set the Action as Start a Program?

[PowerUser]

@yassarikhan786, yes.

[Michał Sacharewicz]

In which user context does the scheduler run the MS Access? Does that user have sufficient access rights to the .accdb file?

[PowerUser]

I am using my own user account and "run with highest privileges" is checked. Also, I can successfully run this command line from a batch file.

[PowerUser]

The History tab seems to always stop at Event#129:"Created Task Process".

[Michał Sacharewicz]

I've recreated what you did (using simple, fresh file with simple macro writing text to file) and after launching the command from scheduler, it... works :(

[Michał Sacharewicz]

Once I run the task, I get following chain of events ID-s: 110-319-100-200-129-201-102, how does it look at your host? Have you separated executable path from arguments, when setting action parameters?

[PowerUser]

I've removed the parameters (yes, they were separated from the executable path, but at this point I just want to see it open Access). I get 319-100-200-129. Is it opening Access in some hidden manner where I can't see it in the task manager?

[Michał Sacharewicz]

The lack of event 201 at the end of your chain suggests, that Access has been launched and has not been closed afterwards. Which means it works properly. Could you please make and link the screenshots of the task properties tabs: General, Conditions and Settings?

[PowerUser]

I found half the problem! I was running this 'whether the user is logged on or not', i.e. as a service, meaning it was inivisible to me. Normally, this would be fine, except my script was also throwing an error (and I intentionally haven't included any error handling yet), hence the chain of event IDs was never completing.

[PowerUser]

I don't recall if I did that or not. In the end, I gave up on the VM and just had a user run the script as needed. In hindsight, this turned out to work better for us than my original VM attempt.