Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Question Unanswered: AutoRun Query at hourly intervals

    All,
    To help make sure our loads leave on time, I have developed a query that can scan our unshipped outbound loads and based on date and time fields see if any are late. I would like to use this query to perform some type of action such as initiate a form to open if the query finds a record or records to let an associate know they have a load that is late. If the query finds nothing, it just closes and waits for the top of the hour to run again.

    If anyone has any way that works best to perform this, that would be great. I don't want to trust any particular associate to run this on their own.

    Thanks,
    Bob Carter
    Access Junkie

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    2 common methods. One would be the timer event of a form, but that would require that the db be running. What I would do is create a separate db that just did this check when it opened, then closed itself. Then put it in Scheduled Tasks to run every hour. You could have it email someone with either a generic message or specific data if it found anything.
    Paul

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    The DB will be running as it is used to print out bills of lading. Any suggestions on how to use the timer in a form as you mention? I have a query set up that can find records that are overdue and put them in a seperate table. From there I don't have anything built.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I have to leave, but look at the timer event. You'd have to set the appropriate timer interval, then what you want it to do in the timer event code. The specific code will depend on exactly what you want to do if it finds anything.
    Paul

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you need a form that is always open (either an existing form if that is how your application works, or make one just for this purpose and keep it loaded & hidden)

    in that form's _Load()
    me.timerinterval = 999 'whatever number of milliseconds you want

    in that form's _Timer()
    msgbox "the timer ticked - do something useful now"

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree with Izy on keeping a form open, however it does mean that someone has to open that form and keep their copy of Access open during the day. If they shut the form, or forget to open the app, or are off sick or on holiday potetnailly it breaks down.

    however Id think a smarter solution is going to be to use a windows secheduler event, that kicks off at the required times, opening up a copy of Access and doing what ever processing you want. How you handle the output of that process is up to you, you could send an email, you could print a report. Heck you could even get the server running the task to get in touch with its feminine side and start whinging (beeping) untill someone comes round and pretends they care for it.

    have a look at windows scheduler, starting access using command line switches, starting access with a specific form, macro or VB function.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    100% healdem (it seems slightly absurd to use a millisecond timer to run an hourly schedule so i would probably look for a scheduler solution, but i have never checked if there is any overhead involved in using the timer: maybe it does make sense)

    Bob says the app is open - so timer is at least viable.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There used to be a problem with timers.... irrespective of the timer interval its still a process running in background. In the dim and distant past I seem to remember a theoretical limit of around 4..6 timers (not sure that still holds, but there were warnings against using too many timers in W98 (3.11 as well)
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The advice above is good if you stay with Access. However...

    I advise clients NOT to use Access if they have mission-critical scheduled jobs that need to run on a constant basis. Access does not lend itself well to such applications.
    oh yeah... documentation... I have heard of that.

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

  10. #10
    Join Date
    Dec 2005
    Posts
    121
    Interesting stuff guys, I'll take all of it in consideration, and thanks for your help.

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Teddy
    The advice above is good if you stay with Access. However...

    I advise clients NOT to use Access if they have mission-critical scheduled jobs that need to run on a constant basis. Access does not lend itself well to such applications.
    HI Teddy,

    What programs would you recommend for such tasks? How much do they cost and where can they be found? Just for future reference possibly.

    Thanks
    BUD

  12. #12
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What I do is build a New Front end linking to the tables

    then use Windows Scheduled task to run the front ends
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Bud
    HI Teddy,

    What programs would you recommend for such tasks? How much do they cost and where can they be found? Just for future reference possibly.

    Thanks
    BUD
    SQL Server/SQL Express/MSDE are fine.

    I've also gone the route of creating an NT service that is always resident along with a companion thread to monitor it's health. Another route is to create a straight-up executable and fire it via scheduled tasks.

    Access just isn't built to do that. It doesn't have an agent and therefore cannot manage scheduled jobs on its own and it's a whole lot of overhead to fire it up as a scheduled task every single time you want to do something mundane. Counting on Access to be running each and every time your scheduled task needs to run creates a whole new breed of terror that I really don't want to get in to.

    Bottom line, if you have something mission critical that NEEDS to run at a set time interval, use a product that supports firing jobs at a specific time interval.
    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
  •