Wednesday, January 18, 2012

Troubleshooting automation

Mostly I'm writing this to leave a note for my future self in case I run into this problem again, or for someone else who might be having the same weird problem.

Trying to automate anything on a Windows machine is an exercise in frustration. That's probably the case for any GUI, but Windows is the one I know best and therefore butt heads with the most.  Anyway, I had this piece of automation that downloaded some data, processed it, and then sent out an email with the results.  I inherited some automation software along with written macros for them, but that only worked about half the time.  Gradually, I've been transitioning to command line work automated through batch files set up as scheduled tasks, and that's worked really well for things like downloading data, running reports, etc., but I could never find a way to make it work with our email client (Lotus Notes).

Recently, I've been working on adapting a VBA module written by a coworker to generate and send an email with a file attached.  I could get it to work by calling the VBA code from an Access macro, and I could call that macro using a desktop shortcut, and I could call that shortcut from a batch file.  That's the approach I've used many times before, and it worked fine up to a point. To be specific, it worked when I ran the batch file directly, and it worked fine if I created a scheduled task and played it directly as a test. But it would not work if triggered at a specific time.

I tried everything I could think of, but what finally worked was ditching the desktop shortcut, and instead using the actual command line interface for Access to open the database with the macro name following a /x. Hopefully it'll work tomorrow, too.


cleek said...

yeah, you gotta be careful with scheduled tasks. If they want to interact with a desktop, and you're not logged in, then the tasks will fail.

cleek said...

Another gotcha with scheduled tasks is that the user logon script is usually not run, so the PATH variable is not configured. You may need to specify the full path to any executables you want to run instead of relying on the PATH variable.