Running Macro as Schedule tak in Windows 7 Pro

[geraldk]

Hello,

I have an error while running a macro in Access 2010. This macro will email me a report that I have completed in my database. When I run the macro while in Access it runs fine, but when I run it at a command prompt it errors out. The message comes up:

No Current Record

I have did some research on this error and found out that unattended Macros that run from Access will do this error, this was reported for Access 2007. I was hoping that Microsoft has fixed this error with a KB that I am missing.



I am running another macro in the same database to email me a report and it runs fine for the command prompt.

Here is the command line that I am running:

start Msaccess.exe "c:\users\window7\desktop\Employee Database.accdb" /x mcoEmailPerformance

Any help would be appreciate. Thank you in advance.

[ItsMe]

Have you tried using an autoexec macro so Access will launch the macro vs. having Windows start the macro? Then all you would need to do is open the accdb file.

[geraldk]

If I have the database open in autoexec how would I schedule the macro to run on a certain date (ie: Monday at 7:00am). Is there something in Access to run a macro? Or would I schedule something in schedule task?

[ItsMe]

I use task scheduler. One thing with task scheduler though, is it does not always seem to want to open Access files. The best approach seems to be to have task scheduler open a VBS or batch file. June7 pointed this out to me and she says she uses script files. Looks like you already have the makings of a batch file...

Also, if you have a DB dedicated to the purpose, you can use a form at startup and avoid macros.

[aellistechsupport]

Hi there,
I have a similar problem using Task Scheduler.
It seems to be "stuck".
It shows status as Running but nothing happens.

Program/settings:
"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"

Add argument:
\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\Memberships.acc db /x AutoRun

Start in (optional): C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office

Anyone know what's wrong with the settings?

[ItsMe]

What is the x AutoRun switch for? What does it do? Maybe it is running in the background, not visible. Have you tried double clicking the Task?

[aellistechsupport]

That is the command-line switch to run a Macro in Access. Which is doing a RunCode of a Function. It does an output of a report.
http://support.microsoft.com/kb/209207

I ran the Task in the Task Scheduler and it shows the status as Running but nothing happens.

I've tested the Macro and it works when I double-click on it.

So I know it has something to do with the Task Scheduler side.

[ItsMe]

I gave up trying to get the Task Scheduler to open Access files. I use VB Script files. Here is some code that you can use to create a VBS file using your text editor. Just change the extension to .VBS

Option Explicit
Dim oShell
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"" ""C:\FolderName\FileName.accdb"""

With that, you can simply tell the Task Scheduler where the VB script file is (somewhere locally with rights) using the file picker provided by the task scheduler. I guess you can add your switch after the accdb extension too.

[aellistechsupport]

Ok thank you.

It used to work great in Access 2003 with WinXP Pro with just a short-cut to the Macro ...
and with the /x "macroname"
not sure why it's not working in Access 2007-2010 with Win7 Pro

Well, I'll try the VBS. Thank you

[aellistechsupport]

Hi ItsMe,
So having never created a VBS before,
When I clicked on VB it opened the MS VB for Applications Module1

So I added:


Dim oShell
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"" ""S:\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\Memberships.acc db /x AutoRun"""

Under: Option Compare Database

I get a Compile Error

Invalid outside procedure

[ItsMe]

I am not sure what "clicked on VB" means.

If you want to create a script file, use the text editor of your choice. Maybe open Notepad and past the code I provided in a new Notepad TXT file. Save the TXT file. Rename the file extension to be .VBS.

When you double click the new VBS file it should launch your Access app.

Point Task Manager to open the new VBS file on a schedule.

[aellistechsupport]

Viola! I got it working!

Change the name of the macro to AutoExec to utilize the built-in automated macro run

then in Task Scheduler:
In the Action section
Select the Action: Start a program
Under Settings
Program/script: "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"
Add arguments: "\\File02\usfs\Crystal and Excel Reports - Phoenix\CrystalReports\Memberships\Memberships.acc db"
Start in: C:\Program Files (x86)\Microsoft Office

it works perfect!
just finished testing it.

And of course to open Access and by pass the AutoExec hold down the Shift key until opened and the AutoExec will not run.
\
SOLVED

Where is the Solved button?

[ItsMe]

Glad you got it working. Maybe I will take a look at what you did here to get task manager to open the accdb. Maybe it has to do with trust locations or....

Anyway thread tools is where you can find options to mark a thread as solved. However, you did not start this thread so...

[aellistechsupport]

Definitely update the Trust Locations
And Enable the Access Macros

Yes, I realized that afterwards I didn't start this thread lol

hopefully it solves it for the originator as it did for me