Googled but cannot find - Automate a "Run All" button

[ctackett6407]

Greetings Folks,

I have tried to figure this out but I've yet to come across something that will allow me to make this happen.

Every morning I have to open an Access File and press the "Run All" button which will do the macro that's found in the "Code" area.

Is there a way that I can setup Task Scheduler in Windows to run this automatically twice a day? it would be one less process I have to do and can be done before employees show up.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub cmdsyncrunall_Click()
'Dim lbwait
'Call DriveTest 'Module 'DriveSpace' checks for low drive space, F drive
On Error Resume Next 'Run All'
lbRCS.Caption = "Sync TNS with CIS"
'lbwait.Visible = True
RCS.Visible = False
lbupdate.Visible = True
lbRCS.Visible = True
cmdsyncrunall.Caption = "Wait"
lblwait.Visible = True
RCS.SourceObject = ""
lbrandate.Caption = ""
Dim I As Integer
Dim cl As Integer
    
    Call metertype 'updates missing metertype 97 &109
    Call meterclass 'updates GEKV2C meter class to 102
    Call cmdpremise 'Premise
    Call cmdacct  'Acct
    Call cmdrate 'Rate
    Call cmdcycle  'Cycle
    Call cmduser2 'Updates Active Meters reading in TNS user2 notes
    Call cmduser1 'Updates User1 field with DSI Collar SN#
    Call cmduser6 'Updates DRU number in TNS user6 notes
    Call cmdmissingmeter ' Updates missing meter#
    Call delpendingcmds 'Deletes pending SDC Actions and Commands[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
'lbwait.Visible = False
'Call fhide[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
lbRCS.Caption = "TNS Desktop Applications"
cmdsyncrunall.Caption = "Run All"
RCS.Visible = True[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
With DoCmd
    .SetWarnings False
    .OpenQuery "Date_Update_Query"
    .Close acQuery, "Date_Update_Query", acSaveYes
    .OpenTable "Date_table"
    .Close acTable, "Date_table", acSaveYes
    .SetWarnings True
End With
cl = DCount("*", "Meter_Class_Service_Multiplier_Query")
'i = DCount("*", "Accts w/Inactive_Gens and Incorrect Rate Code")
DoCmd.OpenQuery "Estimated_Query"
DoCmd.OpenQuery "AMR_Missing_IntervalReads"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
DoCmd.OpenForm "lookup Tasks Queries"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
lbrandate.Caption = DLookup("todaydate", "Date_table", "key = 1")
Call countrecords
If RCS.SourceObject = "" Then lbupdate.Caption = "" Else lbupdate.Caption = "Inactive Gen Accts that needs Rate Change"
If cl > 0 Then DoCmd.OpenQuery "Meter_Class_Service_Multiplier_Query", acViewNormal[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]FinalNote = MsgBox("Sync Complete")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
'isdone:
   ' Call cmdexit
End Sub[/FONT]

 

[Joe4]

Is your Access database split into separate front-end and back-end databases (it should be, if there are multiple people using it)?
If it is, you can create a new front-end database that runs a Macro called "AutoExec". Any macro named "AutoExec" will automatically run in Access upon opening it.
So, just include a step in that macro to close the database, and then have your Scheduler open this specific front-end database twice a day.

Alternatively, instead of using an "AutoExec" macro, you could use a Startup Form (a Form that opens whenever the database opens), put your VBA code in the "Form Load" event.

 

[Micron]

That certainly will work, but will also attempt to run if any nosy person opens it to see what it is. If you direct TS to use a shortcut, you can pass a value known as a command line switch from the shortcut to the database Command property. You would use that property value to know if TS is opening the db or if it is a user. If a user, you still have the option to manually run the update if TS failed to do so at the appropriate time. If it is TS, you run the code. That way, you only have to concern yourself with development changes to one front end version.

 

[Joe4]

That certainly will work, but will also attempt to run if any nosy person opens it to see what it is.

I typically put it in a folder location that only admins have access to (probably should have mentioned that!).

 

[xenou]

Yet another solution is to write a vbscript. A scheduled task then runs the vbscript file (i.e., just point to it just like you would a .bat file or an .exe).

What follows would be the contents of a sample vbs file (you test it first by just double-clicking it/opening it. Once it works you point your scheduled task to it..

File.VBS

On Error Resume Next
Call Run_Job()

'__________
Sub Run_Job()

	Set objAccess = CreateObject("Access.Application")
	With objAccess
	    .OpenCurrentDatabase "C:\Folder\db1.mdb", False
	    .Run "Scheduled_001"  '//if the routine is a function not a sub: .Run ("Scheduled_001")   
	    .CloseCurrentDatabase
	    .Quit
	End With

End Sub