0

I need to run a MS Access job as an automated task. I know Access isn't really built for this type of task, but I have MOST of it working except for one, critical part. In short, this is what it's supposed to do:

  • Generate a PDF report for a user
  • Generate an email for the user
  • Attach the PDF to the email
  • Send the email via SMTP

It works if a user is logged into a desktop session. The process needs to run as an automated process, without requiring a user to be logged in. Using Powershell and the built-in Task Scheduler (Windows 7 Ultimate, 64-bit), I'm able to get it running on schedule. But the Access code fails when it tries to save the PDF. Through experimentation, I learned that I need to save to the "My Documents" folder, and I have the process running as "me", but I keep getting the same error message:

8/18/2014 4:00:17 PM Report Error in <method name>
2302
-1
0
<project name> can't save the output data to the file you've selected.
MTS

So I suspect that if I select the correct location to save the PDF, it will work. Is there a special location that the system and/or Task Scheduler (TS) can save to? Is there a special way to share a folder that it will allow TS to write to it (without requiring a user to be logged in)?

Frecklefoot
  • 1,660
  • 2
  • 21
  • 52
  • What is requiring you to save to the My Documents folder? Can it save to a different folder (such as C:\Test or some such) without error? – TheMadTechnician Aug 19 '14 at 17:20
  • No, it gives the same error as above if I try to save to anywhere but the Documents folder (or anyplace below it) when logged in or not. – Frecklefoot Aug 19 '14 at 18:58
  • 1
    NOT my answer but check out: http://stackoverflow.com/questions/37010776/vba-fails-when-task-scheduler-is-set-to-run-whether-user-logged-on-or-not (folder creation bit worked for me) – EndUzr Nov 22 '16 at 12:05
  • @EndUzr: Thanks, I'll take a look. – Frecklefoot Nov 22 '16 at 14:03

2 Answers2

2

I personally usually save all these types of files/reports into the %TEMP% folder, which seems appropriate for this application since it is only to store it until it is emailed.

I haven't had any permission issues saving into this folder yet.

If you're unfamiliar with %TEMP% you can search for Environ variables and there are usually different useful file paths to common folders used by the system e.g. %APPDATA%,%USERPROFILE%` etc

ashareef
  • 1,846
  • 13
  • 19
  • 1
    Temp really is the most appropriate place for this sort of file. Just make sure you delete the files once the email has been sent - remember, mom always said to clean up after yourself. – alroc Aug 19 '14 at 18:47
-2

Thanks for all the input. After exhaustive testing--based on the response from ashareef above--I've demonstrated that it can't be done. I tried saving to the following environment variable locations:

  • TEMP
  • APPDATA
  • LOCALAPPDATA
  • PUBLIC
  • USERPROFILE

I also tried:

  • C:\Users
  • C:\Temp
  • C:\Users\<my user name>\Documents

And none of those worked if I set the task to run whether I was logged in or not: enter image description here

One location does work, but only if I'm logged in and I set the task to run only when I'm logged in:

  • C:\Users\<my user name>\Documents

To sum up:

  • Saving a file from Access
  • Running as a Scheduled Task
  • Whether you're logged in or not

Is not possible! So here at work, we're going with Plan B. Thanks for your help!

Frecklefoot
  • 1,660
  • 2
  • 21
  • 52
  • The statement that this can’t be done is simply wrong. If you choose the option “run when users is not logged on”, then your running the task with a DIFFERENT logon. That means you have to setup trusted locations for that user (likely the admin user of that computer). – Albert D. Kallal Aug 20 '14 at 17:29
  • I have the "When running the task, use the following user account:" set to me. It should be running as me. But when running from the Task Scheduler, it acts like it has no permission to access any part of the file system. I just now tried to have it save to the Admin account (C:\Users\Administrator\Documents) and it failed as well. If there really is a way, I'd love to hear it. – Frecklefoot Aug 20 '14 at 18:37
  • Well everyone on the planet does this, so I suppose we could get into a liar liar pants on fire type of discussion, but that type of discucisn is for 2 year olds and drunken rodeo clowns. You have some permission issues. Check out: http://technet.microsoft.com/en-us/library/cc785125(WS.10).aspx – Albert D. Kallal Aug 20 '14 at 21:13
  • I checked out the article and did everything it recommends, but for the life of me, I can't get Access to write out the file as a Scheduled Task without a logged-in user. I don't want to engage in a "liar, liar" argument either, but nothing I've tried works. – Frecklefoot Aug 21 '14 at 15:18