Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Timers (AXP / WIN2K)

    I would like to trigger some events to happen automatically in Access. The trigger will happen when 36 hours has gone by since the date/time stamp on a form. I know about form timers but the problem comes from the number of front ends open. We run a FE/BE configuration and everyone has their own copy of the FE. I am afraid that there will be real problems with 20 FE's trying to send a mail message when the timer goes off (this is what will happen when the timer expires). Does anyone have experience with this sort of DB function? I was thinking of making a "Supervisor" file that runs all of the timers but I am not sure that would be the best either.

    Thanks,
    Mark

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Timers (AXP / WIN2K)

    It depends what you want to do. Do you only want the event to happen only once no matter how many front ends are open, or do you want only selected fe's to kick it off?
    From the sounds of it you may only want one to kick it off, in which case you would best make one of the fe's a "Master" or "Supervisor" (as you call it).
    More info please,
    Pat

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    HI Pat,
    Thanks!
    I don't really care at this point what happens as long as I get the one event to happen. The scenario is: We create a record. 36 hours later I want to create an e-mail message that gets sent to a specific mailbox. Has anyone used this sort of thing before? Keep in mind that 20+ people will be running FE's at the same time.

    Thanks,
    Mark

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Timers (AXP / WIN2K)

    Hi Mark,

    When you create the record you should probably include an extra yes/no field that tells you if you have sent the email yet. Obviously you would set the flag after sending the email. You would also need to define an ever incrementing number that starts at zero.

    The problem is that all 20+ forms would time out at roughly the same time (preuming that that they all started their timeout at approximately the same time, if they didn't then the following logic does not apply since there will be probably enough time between timeouts to do their test of the flag anyway)), and if they all started at approx the same time the trick is to make each one of them further wait for a specific number of minutes before testing the flag.
    The number of minutes that a form would have to wait would be determined by what value was in the ever incrementing field.

    So the scenario is:

    a) create the field and set the yes/no field to false and the ever incrementing number to zero.

    [img]/forums/images/smilies/cool.gif[/img] as each form times out, it reads the yes/no flag and if true it just ignores any further action. If the yes/no if false it adds one to the ever incrementing number and times out for that amount of time, before testing the flag again.

    HTH

    Pat

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    Hi Mark,

    I agree with Patt's idea with a slight variation. I would place a time stamp field in addition to the yes/no field. The time stamp would indicate the exact time the record was created.

    Then you could have a form running in the background with a timer that would check every few minutes for records with time stamps created 36 hours before - You could use Now() - 36 hours...

    That way there's only 1 form running a timer at once. It could then send the records that have the qualifying timestamp and mark them sent in the yes/no field.

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Timers (AXP / WIN2K)

    That's cool Mark as long as one of the 20+ users remembers to run that form.

    Nothing worse if no-one remembers.

    HTH
    Pat

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    The form would be automatically launched and remain hidden. That way the user would not have to remember to launch it.

    It might be best to have it launch when the application starts. I would just place it in the Autoexec macro.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Timers (AXP / WIN2K)

    But which of the 20+ users would fire it off?

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    Good point - how about this:

    Be sure that each user only emails his/her own records with this timer.

    In other words, stamp the record with a username in addition to timestamp. Each user's Front-end will run this hidden form that checks for records over 36 hours old created by the current user, then sends the appropriate email.

    Does that sound better?

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    I think that we are on the right track. However this is a technical support department and that person may be out at a customer site or on vacation. How about the scenario of having a master supervisor FE that does the dirty work of checking timers and sending e-mail?

    Thanks,
    Mark

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    Yet another good point! I guess my idea won't work when the individual front-ends aren't activated for a given period of time.

    If you user a "Supervisor" FE then you may be able to avoid changing email profiles through code. The Supervisor could log on to Exchange as the public profile before running the database....

    You could easily create a query to isolate records created over 36 hours ago, then send each of those as an email...

    Is this on the right track??

  12. #12
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    HI Mark,
    I am thinking that this would be the best bet. The "Supervisor" FE could be a fairly stripped down version of what we are running now. I just haven't done anything like this before but the bosses want something that runs automatically without intervention. Any other thoughts?

    Thanks,
    Mark S.

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    Ok - the wheels are turning now....Let's combine our current train of thought with another idea - the Windows Scheduler + Access macro:

    Set up a stripped-down FE with only one purpose in life: Finding the records created over 36 hours ago and sending emails from those records (maybe that's two purposes... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>).

    Create this FE and assign a macro to run your code function that finds the records and sends the emails. Then create an item in the Windows Scheduler either on a server or on your local machine that runs every X minutes (15, 30, 60, etc...). This event will open your database with the /x macroname switch.

    This will launch your new FE, do the work, then exit (you'll have to write a line to Exit the app when it finishes). And it will run without intervention from now until...

    Are we getting there??

  14. #14
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timers (AXP / WIN2K)

    Now you are on to something. I have got to get some updates out today but I will pick this up tomorrow.

    Thanks,
    Mark

Posting Permissions

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