Failsafe ways to open Access and run a macro and close
- Thread starter CedarTree
- Start date
tvanstiphout
Active member
- Local time
- Today, 03:09
- Joined
- Jan 22, 2016
- Messages
- 349
Zero code needed. Read up on the command line switches for msaccess.exe, especially the /x switch.
Isaac
Lifelong Learner
- Local time
- Today, 03:09
- Joined
- Mar 14, 2017
- Messages
- 10,839
Look into VBScript that opens access (set obj = createobject("access.application"), then set db=obj.opencurrentdatabase("path"), then access vba from there.
No ideas about macros, wouldn't use one. use vba instead.
No ideas about macros, wouldn't use one. use vba instead.
So I'm guessing I set-up a CMD line bat file that I run on a schedule?
GPGeorge
George Hepworth
- Local time
- Today, 03:09
- Joined
- Nov 25, 2004
- Messages
- 2,775
What Tom suggested...
Look at the options for Task Scheduler tasks.
Put MSAccess.exe in the Program to Run Setting.
Add the appropriate command line setting to open a specific accdb and run a specific macro when that accdb opens. For details, you can search online for command line options for Access.
- Local time
- Today, 05:09
- Joined
- Feb 28, 2001
- Messages
- 29,670
There have been many posts about scheduling an Access task to do something at an odd time. Requirements:
1. The computer or computers to do this must be running at the intended time. You can't turn off the machine and expect scheduled tasks to run. That might sound a bit condescending, but you would be surprised how many folks forget this simple fact. And they ALSO forget that some computers have automatic "SLEEP" or "SHUTDOWN" timers as part of their power management options. Not to mention that if you have an active IT group, they might define a group policy that includes forcing screen-lock, sleep, or shutdown after X minutes of being idle. That can also get in the way of doing an automated action.
2. Build an icon to run Access on the particular app file. Include /X:macro-name as a command-line option. (See @GPGeorge's instructions.) Once this is built, schedule it to run as the desired user. NOTE: If you forget to specify the user, it might try to run as user SYSTEM because that is the user who "runs" the task scheduler. If you have a user identification requirement, SYSTEM might not be in your list of authorized users. You must also consider that whatever your app does, there will be no active command input devices (keyboard, mouse) available in that context which means no active login CAN occur. One strategy involves split database setup, where you have an interactive user front end and a non-interactive front end that BOTH talk to the same back end. Put the macro in the non-interactive front end and include tests so that when that FE runs, it tests its location and its run-time condition. The non-interactive FE might bypass the login dialog entirely and use the ENVIRON function or the network GetUserID function to identify the account.
3. In the app file, you must have a macro of the name that you supplied as the argument of the /X option. This macro can do whatever you want it to. Remember that macros have a RunCode option that allows you to run a FUNCTION (yes, it has to be a function!) that can do things in VBA if there is no macro command for what you wanted to do. In that case, the things run by the macro have to be part of the FE file. If you use the split FE file approach mentioned earlier, the modules needed for the automated operation have to be in the non-interactive FE. Whether they also appear in the interactive FE is up to you. The selected macro can have as many steps as any macro can have and can "call" another macro. HOWEVER, the last step of the selected (top-level) macro must QUIT the application and none of the prior steps should set up any condition that would prevent an orderly QUIT from working. Failure to follow this rule would leave an instance of Access "dangling" - which would NOT be a good thing, as you would potentially get all sorts of Windows file-lock issues.
1. The computer or computers to do this must be running at the intended time. You can't turn off the machine and expect scheduled tasks to run. That might sound a bit condescending, but you would be surprised how many folks forget this simple fact. And they ALSO forget that some computers have automatic "SLEEP" or "SHUTDOWN" timers as part of their power management options. Not to mention that if you have an active IT group, they might define a group policy that includes forcing screen-lock, sleep, or shutdown after X minutes of being idle. That can also get in the way of doing an automated action.
2. Build an icon to run Access on the particular app file. Include /X:macro-name as a command-line option. (See @GPGeorge's instructions.) Once this is built, schedule it to run as the desired user. NOTE: If you forget to specify the user, it might try to run as user SYSTEM because that is the user who "runs" the task scheduler. If you have a user identification requirement, SYSTEM might not be in your list of authorized users. You must also consider that whatever your app does, there will be no active command input devices (keyboard, mouse) available in that context which means no active login CAN occur. One strategy involves split database setup, where you have an interactive user front end and a non-interactive front end that BOTH talk to the same back end. Put the macro in the non-interactive front end and include tests so that when that FE runs, it tests its location and its run-time condition. The non-interactive FE might bypass the login dialog entirely and use the ENVIRON function or the network GetUserID function to identify the account.
3. In the app file, you must have a macro of the name that you supplied as the argument of the /X option. This macro can do whatever you want it to. Remember that macros have a RunCode option that allows you to run a FUNCTION (yes, it has to be a function!) that can do things in VBA if there is no macro command for what you wanted to do. In that case, the things run by the macro have to be part of the FE file. If you use the split FE file approach mentioned earlier, the modules needed for the automated operation have to be in the non-interactive FE. Whether they also appear in the interactive FE is up to you. The selected macro can have as many steps as any macro can have and can "call" another macro. HOWEVER, the last step of the selected (top-level) macro must QUIT the application and none of the prior steps should set up any condition that would prevent an orderly QUIT from working. Failure to follow this rule would leave an instance of Access "dangling" - which would NOT be a good thing, as you would potentially get all sorts of Windows file-lock issues.
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 18:09
- Joined
- May 7, 2009
- Messages
- 20,124
you can also create a vbscript to open the db and run a Public Sub in a Module.
the Public Sub will Run your Macro (Docmd.RunMacro "YourMacroName").
the vbscript will look like this (rundb.vbs):
now add the vbscript file to your Task Scheduler.
the Public Sub will Run your Macro (Docmd.RunMacro "YourMacroName").
Code:
Public Sub ThePublicSub()
DoCmd.RunMacro "YourMacroHere"
End Subthe vbscript will look like this (rundb.vbs):
Code:
Dim objAccess
Dim strDatabasePath
' Set the path to your Access database
strDatabasePath = "C:\somePathHere\yourDatabaseHere.accdb"
' Create an instance of MS Access
Set objAccess = CreateObject("Access.Application")
' Open the Access database
objAccess.OpenCurrentDatabase "" & strDatabasePath & ""
' Make MS Access visible (keep it open)
objAccess.Visible = True
objAccess.Run "ThePublicSub"
' Cleanup (if needed)
objAccess.Quit
Set objAccess = Nothingnow add the vbscript file to your Task Scheduler.
- Local time
- Today, 06:09
- Joined
- Feb 19, 2002
- Messages
- 46,413
I haven't had to do this in quite some time but I do remember having trouble scheduling Access directly so I used a batch file and that was fine.
Three things though:
1. You need to use a macro if you use the same FE that your users use. The macro cannot be AutoExec. Passing in the name of this special macro bypasses the normal opening process.
3. You need to either write a log record to a table to indicate that the process ran or send a couple of people emails. Otherwise, you may find that something interfered with the app and your process didn't run. You don't really want to have to remember to check to see if the batch process ran every evening. If you log something to a table, then when the app opens, it can look for the log record and if it isn't there, issue a warning to the user that the batch process didn't run. Make this message very specific so they know whether or not it is safe to use the FE to do their normal functions even though the overnight batch process failed and who to notify. I actually prefer this method.
2. Your last step in the macro, MUST close Access.
Three things though:
1. You need to use a macro if you use the same FE that your users use. The macro cannot be AutoExec. Passing in the name of this special macro bypasses the normal opening process.
3. You need to either write a log record to a table to indicate that the process ran or send a couple of people emails. Otherwise, you may find that something interfered with the app and your process didn't run. You don't really want to have to remember to check to see if the batch process ran every evening. If you log something to a table, then when the app opens, it can look for the log record and if it isn't there, issue a warning to the user that the batch process didn't run. Make this message very specific so they know whether or not it is safe to use the FE to do their normal functions even though the overnight batch process failed and who to notify. I actually prefer this method.
2. Your last step in the macro, MUST close Access.
So when I try to open Excel or Access via task scheduler (on a 2019 MS Server), it doesn't open it -- or at least doesn't open it visibly? Any special hints/tricks here? And this is without any special X switches, etc. Just wanna open Access!
- Local time
- Today, 05:09
- Joined
- Feb 28, 2001
- Messages
- 29,670
It is possible for you to open something in a way that it is not visible, though it is also possible to command the thing (after it has been opened) to become visible if you wanted. I used to do that for Outlook (opened from Access) all of the time. "Open" in Windows context means the file has been opened, presumably by the right utility program. "Open" has nothing to do with visibility. The Office apps all have a .Visible option, though where you find that setting may depend on which app you are launching. Launching from the task scheduler doesn't have a direct interface to the ".Visible" property, though.
That is not the original question you asked. To do what was asked in post #1 of this thread, you need to use the /X option if the task scheduler is used for this situation. All Windows programs (and MS-DOS before that) have default I/O channels that are automagically opened following a successful launch. They are assigned according to how the app was launched. The display, keyboard, and mouse each have one "reserved" I/O channel when you launch from a Windows explorer file icon and ALSO when you launch using the CMD prompt, because the app inherits from its launcher. Interactively by icon launch or a command line launch, your session's defaults are what gets passed along.
For Windows Task Scheduler, that type of launch runs as the SYSTEM process which uses scripts and log files for its default I/O channels. Therefore, an app opened that way inherits its initial channels from SYSTEM - but that account has NO FREAKIN' IDEA what to do with the app, so it leaves it dangling and taking up memory but unable to communicate to get instructions.
Using the /X option in that context TELLS it what to do, gets it started, etc. - and the advice given earlier by several members was based on that background launch context.
You could ask "well, what if I'm there at the time?" The answer is "Yeah, but you ain't logged in as SYSTEM, and the app IS logged in as SYSTEM." Windows security measures will stop your logged in session from seeing that scheduled session because you aren't the same user.
You could ask "well, what if I launch it under my account?" The answer is "Yeah, but the automation still inherits the default I/O channels from its launcher. The permissions would be right so you could use YOUR logged-in process to look at the statistics of the dangling process run under your name - but it still is a dangling process."
You might not realize it but what you are asking is hacker behavior - trying to sneak past Windows rules on interprocess isolation. There is an old rule often quoted about "neat" programming. "If you opened it, you should close it." But the Windows security corollary is "If you didn't open it and aren't an admin, you CAN'T close it."
- Local time
- Today, 03:09
- Joined
- Aug 30, 2003
- Messages
- 36,263
I have a number of automated processes running. I had similar experience and used the same workaround, but for a number of years I've been able to just call the accdb/mdb directly. Not sure what changed or when.
