ACCESS macro to run on task scheduler (1 Viewer)

MyAlex2

New member
Local time
Today, 14:33
Joined
Jun 4, 2014
Messages
7
I have an ACCESS macro setup that runs inside its ACCESS database. I want to run it on schedule using task scheduler. I'm using MS ACCESS 2010 and Windows 7.
Here's my .bat file entry:

"C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" "C:\ForAmy_NewProjectCoding - allprojectsB.mdb" /x APEXSummary

The .bat file runs successfully when double clicked from the c drive.
The ACCESS database never opens, hence the macro never runs, but the status on task scheduler states "Running". I end up just ending the task from task scheduler because it's not even running the macro.

I'm baffled by this because I do not see any error at all. It just does not launch ACCESS db.

Please help. Thank you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:33
Joined
Feb 19, 2013
Messages
16,553
not sure if I can be much help

Have you tried naming your macro autoexec so it runs automatically when the db is opened?

Not sure if it is relevant but if you have Access 2010, why are you running a .mdb? Just wondering if there is a difference running a 2003 db in a 2010 environment
 

MyAlex2

New member
Local time
Today, 14:33
Joined
Jun 4, 2014
Messages
7
The ACCESS db I have is old. It was inherited from a previous employee. When I was assigned my desktop, they installed MS ACCESS 2010. Do you think that is an issue?
Should I try to create a .accdb ACCESS and move all my objects from the old ACCESS db? That old ACCESS db has a macro that contains almost 40 queries, so I'm hesitant to actually move them to a new ACCESS db. I don't have any issues manually running the macro inside the ACCESS db.

Please advise. Thank you.
Ming A.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:33
Joined
Feb 19, 2013
Messages
16,553
they installed MS ACCESS 2010. Do you think that is an issue?
I don't know, but it is a possiblility

so I'm hesitant to actually move them to a new ACCESS db.
What have you got to lose? create a new .accdb and copy everything across and try it, you will still have the original .mdb
 

MyAlex2

New member
Local time
Today, 14:33
Joined
Jun 4, 2014
Messages
7
OK, I'll try to do that and let you know if it works.
Thank you.

Ming A.
 

dlambert

Member
Local time
Today, 22:33
Joined
Apr 16, 2020
Messages
42
Hello, tagging on to the this thread as the title matches what i need
I have an access macro that i would like to run on a schedule. I have made a shortcut to my desktop to the required macro, and if i click that shortcut the macro runs exactly as expected (and opens the database if it is not already open).
But if i set the shortcut of the macro to run by the task scheduler it doesn't work...
All i need is for something to effectively automatically 'double click' the macro shortcut on a schedule...
Anyone have any idea of how to achieve this?
Or is there another way to have a access macro run on a schedule?
Thanks
 

Micron

AWF VIP
Local time
Today, 17:33
Joined
Oct 20, 2018
Messages
3,476
But if i set the shortcut of the macro to run by the task scheduler it doesn't work...
That doesn't reveal much.
TS doesn't run the task? Runs the task and opens the db but the macro doesn't run? The macro is an auto exec macro or not?
I didn't know you could create a shortcut that points to a macro - your shortcut path actually does this? I have used TS to open a db with an auto exec that ran a scheduled task but that would be different.
 

Micron

AWF VIP
Local time
Today, 17:33
Joined
Oct 20, 2018
Messages
3,476
AFAIK, you only absolutely need a shortcut if you want to pass a command property value to the db code. That way, any startup code can tell the difference between one method of opening vs another (e.g. don't update / update automatically).
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:33
Joined
Sep 21, 2011
Messages
14,047
AFAIK, you only absolutely need a shortcut if you want to pass a command property value to the db code. That way, any startup code can tell the difference between one method of opening vs another (e.g. don't update / update automatically).
Well I copied the OP's attempts. The shortcut from the TS soes not invoke the macro, though it does open the DB ok.
Using the method I linked to, runs the macro. The shortcut from the desktop also runs the macro, just not from the TS.

Mt shortcut had C:\Users\Paul\Documents\Test.accdb /x "Macro1"
If you try that in the TS, it warns you and puts the /x "Macro1" as an argument. That does not work.

For me, I'd be happy having a way to do this, and not worried about the ins and outs of why one works and the other does not. :)
 

Micron

AWF VIP
Local time
Today, 17:33
Joined
Oct 20, 2018
Messages
3,476
Well I copied the OP's attempts. The shortcut from the TS soes not invoke the macro, though it does open the DB ok.
Then I guess it has to be a shortcut regardless of whether or not you want to read the Command property in db code. M$ gives 2 methods for running a macro using the x switch - Run dialog or shortcut and provides no other methods. I have to conclude then that a shortcut is the only way it will work using TS since that is not the Run dialog. All I knew for sure at this point is that you cannot access the Command property in db code unless you use a shortcut to open the db, and that shortcut has to contain a command switch value.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:33
Joined
Sep 21, 2011
Messages
14,047
Then I guess it has to be a shortcut regardless of whether or not you want to read the Command property in db code. M$ gives 2 methods for running a macro using the x switch - Run dialog or shortcut and provides no other methods. I have to conclude then that a shortcut is the only way it will work using TS since that is not the Run dialog. All I knew for sure at this point is that you cannot access the Command property in db code unless you use a shortcut to open the db, and that shortcut has to contain a command switch value.
@Micron
No, quite the opposite. The shortcut run from TS will not run the argument.?
For the TS option to work with a macro/code, you run access and pass the db and db argument of the macro. Then it runs.

Try it yourself, it only takes a minute or so to setup.
 

Users who are viewing this thread

Top Bottom