Windows Task Scheduler .Bat File Macro

[ItsMe]

Here you go.



https://www.accessforums.net/macros/...tml#post218112

And BTW, you do not have to wait to start splitting your DB. Start now with your Workbook export thing. Create a new file and place the resources necessary in the file. Then, link (only) the tables you need. That's it. Others can continue working in the Backend as your new application links to the same Backend.

[CJ_London]

I've not really considered using the task scheduler but seeing this thread I do have two instances where it would be useful.

The first to compact the backend db on once a week or once a month basis perhaps over a weekend.

At the moment I do this from another management db which is left open and the event is triggered through a form timer at 3am. However sometimes that db has been closed for some reason, so does not run.

The process runs at 3am -hopefully users are not working at that time over a weekend, but if they are, they are given 5 minutes before they are booted out in an orderly fashion, then compact the db. An email is then sent to them saying they can log back in again.

My thinking is that some or all of that process could perhaps be run from a script instead of the management db - or would it be simpler just to have the task scheduler open the management db a few minutes before 3am? In which case, what happens if the db is already open? would a second instance be created which would start to conflict with the open db?


The second is running some scheduled reports - at the moment these are run manually as first act of business for a particular user on a daily basis - basically a summary of the previous days activity, reports are run and forwarded by email to various managers. But sometime that user is late or on holiday or goes straight into a meeting so does not run the reports until later in the day - or not at all. The other problem is he uses a laptop which is not always connected to the network. I appreciate the scheduler is not going to work (or at least not complete it's task) if he is not connected (although is there a network scheduler instead?) but if he is at least logged in, the scheduler could still be triggered. I would need to modify the code to say 'reports sent' to prevent them going twice but that is a small price to pay for timely delivery. Does anyone have any thoughts?

[ItsMe]

My thinking is that some or all of that process could perhaps be run from a script instead of the management db - or would it be simpler just to have the task scheduler open the management db a few minutes before 3am? In which case, what happens if the db is already open? would a second instance be created which would start to conflict with the open db?

I do not believe a second instance will be created. For some things, it may be easiest to run a task from script, entirely. If you are going through the trouble of emailing, shutting users down, etc. I would probably have script launch an Access app. The idea of having a switch to run a Macro Object in the exe line might be of use. This way you could have one app that performed different operations (different switches for different schedules and function calls).

I would create classes in an effort to maintain separation of concerns. So, with your management DB, make sure that you have separate modules, functions, forms, etc. for a specific operation. With that, you can create multiple applications. Create an application for each operation or create an application for any group of operations that may conflict with another operation(s). With proper separation of concerns, there would not be an automation conflict with your management app being open.

The other problem is he uses a laptop which is not always connected to the network. I appreciate the scheduler is not going to work (or at least not complete it's task) if he is not connected (although is there a network scheduler instead?) but if he is at least logged in, the scheduler could still be triggered. I would need to modify the code to say 'reports sent' to prevent them going twice but that is a small price to pay for timely delivery. Does anyone have any thoughts?

I use servers to run schedules all of the time. The trick is to make sure that the directories have the correct permissions. Also, you want to create the Task Schedule thing on the server and with admin privileges for said controller/server. So, make sure the directories have the correct permissions with the shares that contain the Access apps, file copies, etc.

In fact, after a manual process is fine tuned, I move it to Automation as soon as possible and eliminate the manual thing.

[CJ_London]

Thanks Itsme,

I'll talk to my client about the second thing, the backend is on a server but so far as I know I do not have access to the related task scheduler - the other problem may be the email- I'm assuming the server would need outlook as a client? Currently the reporting uses the users outlook instance for 'from'

[ItsMe]

You can use any computer as a server as long as it has access/permissions to the share folder. You could put the FE files, Scheduler, and Access Runtime on an old XP box and it would work. As for the email, you could use CDO schemas on the server and bcc the Outlook client.

Here is an example of CDO that has several CDO schemas. I believe this has the bcc. Also, the html tags are hacked a little because I redacted parts.
https://www.accessforums.net/program...tml#post223496

[Micron]

I've used Scheduler to run code this way: Use '/cmd Otto Mayshun' (no quotes & yeah, dumb joke) as the switch in a shortcut that Scheduler (not users) has access to. The autoexec macro runs the startup code regardless of how the db is opened. There, I assign the database property Command to variable svCmd. It's not necessary to make this assignment, but by doing so, I can play with its value during testing. Command is not "" if a command line switch is used in the shortcut. Windows passess a fixed length string in the switch, so if it's important to you, use the Trim function on svCmd to remove trailing spaces. Anyway, if svCmd = "" , we do all the user login stuff, else it is bypassed and we go straight to the line that calls the update function in a standard module. There I call a batch file that copies the back end as dbBAK.mdb before I go messing with the data. One nice feature I found for batch files allows me to perform this file copy regardless of what the user's assigned drive letter is (not everyone is native to the drive, so we cannot use F:\ for everyone, and apparently, batch files do not like UNC paths. I will pass it along here because it was a real gold nugget for me, and took a lot of searching to find.
The statement is:
set MyDir=%~dp0
Code without all the notes and Dim statements:
function StartUp()
svCmd = Command
If svCmd = "" Then
do lotsa login stuff
end if

UpdateDB
Exit Function
errHandler:
err stuff

end function

[CJ_London]

Thanks guys - food for thought. When I progress it I'll start a new thread if I hit any problems

[LonghronJ]

I have followed so many different posts from different forums, but I still cannot get it to work right. I get to the point where I run the task scheduler, it says it's running and the history shows Task Completed, but I know my Access did not open. I definitely think it's the Task Scheduler end of things that's not working, because when I open Access, I see the macro does its thing. When I open a .vbs to launch the Access file, it does it, so I'd greatly appreciate anyone who can help me troubleshoot.

[pbaldy]

I just created an automated routine using vbs script:

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "msaccess.exe C:\FolderName\FileName.accdb"

and I run that vbs file from Scheduled Tasks.

[LonghronJ]

In Task Scheduler, what parameters did you put in Program/script, Add arguments, and Start in?

[pbaldy]

Program/script is the full path to the file, I left the other 2 blank.

[ItsMe]

Also, IIRC, you can right click the Task and run it manually to test the task.