Run Code Automatically

[kitty77]

I have the following on a form. How can I run this code automatically via a scheduled task?

Private Sub Command12_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "Supporttbl"
DoCmd.OpenQuery "Customertbl"
DoCmd.OpenQuery "Sunshinetbl"

End Sub

 

[June7]

Windows Task Scheduler can either open db or run script file.

Can have code behind form that is set to open by default when db opens.

Or put code in a general module and VBScript can open db and run procedure. Here is some sample scripts, take your pick:

Dim objFSO, oShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""\filepathhere\MaterialsDatabase.accdb"""

Dim ObjAccess
Set ObjAccess = CreateObject("Access.application")
ObjAccess.visible = false
ObjAccess.OpenCurrentDatabase("C:\Users\June\Umpires.accdb")
ObjAccess.UserControl = False
ObjAccess.Run ("test")
ObjAccess.Run "Sub", "argument"
ObjAccess.DoCmd.RunMacro "Macro"
ObjAccess.Quit
Set ObjAccess = Nothing

Dim cn
Set cn = CreateObject("ADODB.Connection")
cn.open = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\June\Umpires.accdb"
cn.execute "INSERT INTO Rates(RateID,RateLevel) VALUES('zz','zz')"
cn.Close

 

[theDBguy]

By "scheduled task," are you referring to the Windows Task Scheduler? If so, you can put those commands in a macro and then schedule a task to run Access to open your db with the /x command switch.

 

[Pat Hartman]

I'm guessing there is some serious design flaw if you are having to run action queries this way. If they are not action queries, then the procedure makes absolutely no sense at all.

I have a couple of applications where there are batch processes involved. Mostly they are related to sending/receiving files to/from a different application or with printing things like invoices or emailing invoices since you don't want to do these large batch processes during normal working hours because they can slow down user interactions.

The processes are run on PC's that are locked in an office or closet and never used for anything else. The PC's must be left on 24/7 in order for Windows scheduler to work. The procedures all send confirmation emails to at least two people at the end of each task and they log errors in tables so that debugging is easier. As a double check, each time any user opens the app, they get an error message if there are error logs that have not been marked as reviewed. The problem with batch processes arises from the lack of something happening so people who expect to get emails every day, need to take action when they don't get the email. This is the trickiest part of the whole thing. Creating a notification scheme that ensures that the process runs when it is supposed to run and the failure to run doesn't end up as a black hole.