Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2005
    Posts
    39

    Unanswered: Schedule procedure

    Hi
    does anyone have an example of a sub which calls a specific sub on a set time?
    Thanks Felix

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2005
    Posts
    39
    I want to call it from VBA. I have the excell way, but that uses the Application.ontime command. Something similar available in VBA MSAccess?
    Regards Felix

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're sure this database is going to be open at the exact moment you need an event to fire?


    if you're dead set on it...

    There is no "object.SomethingFiredAllThetime"

    You can create a persistant form and put something in it's ontimer event, but that's about it.
    Last edited by Teddy; 11-08-05 at 09:59.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2005
    Posts
    39
    Hi, yes, the database will always be on line. mmm, so I cannot used a VBA like this (Excel) to call another sub (in this case upload) on a specific time ....

    Public Runwhen As Double
    Public Const cRunIntervalSeconds = 300 ' Seconds of a day
    Public Const cRunWhat = "The_sub"

    Sub StartTimer()
    ' This stores the date and time
    ' from the current time in the RunWhen variable,
    ' and then calls the OnTime method to instruct Excel
    ' when to run the cRunWhat procedure.
    Runwhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime earliesttime:=Runwhen, procedure:=cRunWhat, _
    schedule:=True
    End Sub
    Sub The_Sub()
    Call UPLOAD
    StartTimer ' To start calculating the next run
    End Sub
    Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=Runwhen, _
    procedure:=cRunWhat, schedule:=False
    End Sub

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Look at the ontimer property of any form object, that should give you a general idea what I was referring to.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you will have a form open (Teddy's "persistant form")
    in an event on that form
    ...perhaps _Load()
    ...perhaps a start-my-repeating-process-now-button _Click()
    you do
    Me.TimerInterval = 300000 'in milliseconds, so that's 300 seconds

    (you can also set a form's timerinterval in design view if you prefer)

    and in the Form_Timer() event of your "persistant form" you put any code you want (including, if you like, gum-up-my-network code that downloads stuff every five minutes even if the data has not changed)
    your Form_Timer() code will run each .timerinterval (or maybe later depending what else is happening on the system)

    key aspect is that you need Teddy's "persistant form". either you fake it with a hidden form that hangs around in the background of your application until you quit, or you use the timer in the form that is actually going to show some of this regularly updated data.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Timed events

    If it's a SQL Server stored procedure you can look at scheduled DTS Packages which are fun to work with. If it's an Access application you want executed with code in it, you can look at running a *.vbs script as possibly running it at an executed time. Ask your network person about running scripts at certain times, perhaps after or before a backup run or some other task. We have *.vbs scripts which open MSAccess when the user logins in (Logon Script) and let's the user select whether they are in or working off site. Another *.vbs script runs when the user logs out (LogOut Script) which directly uses a SQL Statement to write a "LogOut" status to a SQL Server field in a table for the user. You could also shell to another MSAccess program upon some action which would automatically launch some code when opening that program after it's called from the other MSAccess application or possibly shell to a *.vbs script. Hope this helps give you some ideas. If it's something you want to do strictly in MSAccess I might use the Event Timer on a Form which would fire after XXXX intervals and check the current Time (Dim VariableX as Date, VariableX = Now) and if it's passed a certain time of the day, it would then fire the event you want to fire. Remember you have to reset the XXXX interval time when you check it.
    Last edited by pkstormy; 11-08-05 at 18:49.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Apr 2005
    Posts
    39
    Hi all
    thanks for all your contributions, I will play a bit. Indeed I was looking for a solution within Access.
    Regards Felix

  10. #10
    Join Date
    Apr 2005
    Posts
    39
    Dear all, I found the solution on the following link:

    http://www.cpearson.com/excel/ontime.htm

    you would need to use the second option to call on a specific time a piece of code; it works also in the MSAccess enviroment. So no form link or whatever. You just need to make sure your database is open - what was my case. For anyone who has the same situation

    Regards Felix

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That could come in handy...

    The only problem I could see there is abnormal termination of the program leaving this timer thread hanging since it's not really part of the application.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •