 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / November 2008Windows Scheduler
Hello, Has anyone ever used windows scheduler to launch a routine witin Access. I have a routine, that runs on an "on click" event. I would like to have this routine kick off at 4:00 a.m. every morning. Through the schedule wizard, I have been able to get my application launched, but I can't trigger the event so nothing happens. I do not want to change this to run when the DB is opened, because I do not want this routine to run every time the DB is launched. Hope this makes sense. Any suggestions would be appreciated. Thank you, Roger One of my clients used the Windows 98 scheduler to download a SQL-Server file every night at 12:30 AM. I've used AT commands to have a server automatically FTP a file to another server.. To trigger an event upon startup, all you need is an autoexec macro with a RunCode action, or better yet a startup form with code on the Load, Open, or Current event.  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Hello, > [quoted text clipped - 14 lines] > Thank you, > Roger I don't use an autoexec macro or startup form to do this because that runs every time the database is opened. I use the /x argument which allows you to pass in the name of a specific macro. Look up "Startup command-line options" in help for details on arguments you can pass when you open the database. In the macro, don't forget to close the database as the last step. I also had run the app from within a .bat file to get it to work properly. Of course that was three versions ago and a different operating system so that may no longer be necessary. > One of my clients used the Windows 98 scheduler to download a SQL-Server > file every night at 12:30 AM. I've used AT commands to have a server [quoted text clipped - 21 lines] >> Thank you, >> Roger I almost always use a startup form. While the code runs every time the form opens, you can use a recordset to make an entry in a table to tell the code not to run again in that branch for a certain period of time. The scheduler program can also be useful to run batch files that don't require starting Access, such as ftp'ing a database early in the morning to another server. I basically use Access to run database centric code, and VB or scripting (or .bat files) to run non-database centric procedures. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >I don't use an autoexec macro or startup form to do this because that runs >every time the database is opened. I use the /x argument which allows you [quoted text clipped - 32 lines] >>> Thank you, >>> Roger I almost always use a startup form. While the code runs every time the form opens, you can use a recordset to make an entry in a table to tell the code not to run again in that branch for a certain period of time. The scheduler program can also be useful to run batch files that don't require starting Access, such as ftp'ing a database early in the morning to another server. I basically use Access to run database centric code, and VB or scripting (or .bat files) to run non-database centric procedures. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >I don't use an autoexec macro or startup form to do this because that runs >every time the database is opened. I use the /x argument which allows you [quoted text clipped - 32 lines] >>> Thank you, >>> Roger Roger, If you have a main form (maybe a splash screen, or something like that), then you can set the forms TimerInterval to some value other than zero (1000 = 1 sec), but start out setting it to something like 10 or 1000, to get the Timer event code to run almost immediately after loading the form. Then you can use the forms Timer event to check to see what the current time is, and at the appropriate time, run your code. If you are going to continue to use the Windows scheduler to open your application, then you could also add code to the Timer event to close the database as well. It might look something like: Private Sub Form_Timer() Static dtPrevious As Date Static dtCurrent As Date Static tmPrevious As Date Static tmCurrent As Date Dim tmRunAt As Date If Me.TimerInterval < 1000 Then dtPrevious = Date - 1 tmPrevious = TimeValue("12:00:00 AM") Me.TimerInterval = CLng(1) * CLng(60) * CLng(1000) End If tmRunAt = CDbl(0.1666666667) 'converts to 4:00:00 AM If dtCurrent <> dtPrevious Then dtCurrent = Date tmPrevious = TimeValue("12:00:00 AM") End If If dtCurrent <> Nz(dtPrevious) Then tmCurrent = TimeValue(Now()) If Nz(tmPrevious, 0) < tmRunAt And tmCurrent > tmRunAt Then MsgBox "run your code here" Docmd.quit End If End If End Sub I'm sure there is something a little more elegant than this, but it should do the job for you. HTH Dale SignatureDon''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > Hello, > [quoted text clipped - 10 lines] > Thank you, > Roger I explain in detail how to use the windows scheduler and windows scripting to CALL a routine *inside* of your application. The windows script can easily be placed in the windows scheduler. The following article of mine should help you: Batch processing in ms-access (how to run ms-access as a batch job). By Albert D. Kallal Friday, April 27, 2007 http://www.members.shaw.ca/AlbertKallal/BatchJobs/Index.html SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com Hello, Thank you very much for the reply. This is exactly what I am trying to accomplish. I have gotten the lvbs file to the point where when I double click on the file icon, the event runs. What I can't figure out is now how to schedule this using the schedule wizard (which seems to be the only way I can create a new scheduled task). When the wizard comes up, I choose Access as the application. Access is opened, but the process isn't triggered. Am I doing something incorrect? How do I get this scheduled to read my .vbs file and then run the code inside the script? Thank you, Roger > I explain in detail how to use the windows scheduler and windows scripting > to [quoted text clipped - 8 lines] > Friday, April 27, 2007 > http://www.members.shaw.ca/AlbertKallal/BatchJobs/Index.html You don't select Access as the application since you are opening a script file. Choose Scripting Runtime if that is an option. I haven't done this in a while so I don't know what your options are. > Hello, > [quoted text clipped - 30 lines] >> Friday, April 27, 2007 >> http://www.members.shaw.ca/AlbertKallal/BatchJobs/Index.html Just create your "vbs" script, and use that for the scheduler. eg: from windows go: start->control panel select the scheduled tasks icon. just click on a add scheduled task..and choose your script that you made. You don't need to do anything more. when you click on the schedule task wizard, simply hit the browse button...and browse to your script. If you don't have windows logon enabled, then entering a password and logon will not work. I suggest you simply "check" the box [x] run only if logged on The above thus assumes the machine is left on, and running. Only uncheck the above check box if you acutally are running this on a box that prompts for the windows logon, and also will be logged off. You of couse will "test" the sciprt by dboule cliking on it..and ensureing it works. Once that script is working the way you want, simply use the "add schidled task" in the above contorl panel option..and you are home free. SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com Got it! Thank you very much. Great article, by the way. Very good stuff! > Just create your "vbs" script, and use that for the scheduler. > [quoted text clipped - 23 lines] > it works. Once that script is working the way you want, simply use the "add > schidled task" in the above contorl panel option..and you are home free. Hi Albert, I was reading your posts and I'm looking to do the same thing but I need to run a macro from within my DB and then have the DB close. Would I put the name of the macro where you have "accessApp.Run "MacroNameHere"? Also what if my DB is secure. How would I have your script use a logon name and password to access the DB? dim accessApp set accessApp = createObject("Access.Application") accessApp.OpenCurrentDataBase("C:\some path name\someMdb.mdb") accessApp.Run "TimeUpDate" accessApp.Quit set accessApp = nothing > Just create your "vbs" script, and use that for the scheduler. > [quoted text clipped - 23 lines] > it works. Once that script is working the way you want, simply use the "add > schidled task" in the above contorl panel option..and you are home free. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.