Run On Timer event for specific days and times

Question

Greetings,

I have an Access 2016 database (32 bit) on a Windows 10 machine. I have a form named frmMain with the Timer Interval set for 1 minute (60000). Each minute I need the On Timer event to run a vba sub called ImportShipped. I want this process to run on specific days and on specific times. The application I’m extracting this data from will only allow me access on specific days of the week and during a specific time range.

I created two tables. The first is named tblImportDays with a short text field called ImpDay that contains the long name of the day of the week (ex: Monday, Tuesday) the vba is to run. The second table named tblImportTimes contain a Date/Time field with the specific times (ex: 3:00:00 PM) the vba is to run.

I need help creating a vba that compares the current day of the week and current time to these two tables to determine when to run the On Timer event.

I appreciate any suggestions.

Regards, Kevin

Answer 1

Comparing dates is done with the DateDiff function.

I would also keep a record of which imports have been done, so you don't accidentally do it twice in a row. Note that the timer will not necessarily tick EXACTLY at the right moment.

---

-Tom. Microsoft Access MVP

Answer 2

Hi Tom,

Thank you for the fast reply. I'm not sure I understand your reply. I'm trying to use the two tables to dictate when this import process is to run. One table (tblImportDays) contains the days of the week and the other table (tblImportTimes) contains the import times.

For example, if I have Monday in the tblImportDays and 3:00:00 PM in the tblImportTimes, the vba in the On Timer event will evaluate the current day and time and determine if today is Monday at 3pm so it may run the vba.

What I need is the vba to make the comparison between the current day and time to the days and times listed in the tables above.

Is this something you can help me with? Or maybe you could go into further detail on your previous reply?

Thanks for your help.

Answer 3

Do you know when tblImportDays and tblImportTimes change? It will help to know when they change.

The best solution is to use the Windows Task Scheduler. You can write a program that schedules the VBA to run when needed. It is not necessary for you to do the scheduling (such as On Timer event) yourself.

---

Sam Hobbs
SimpleSamples.Info

Answer 4

Hi Sam,

Thank you for your replay. I'm hoping to use the tables to control the import days/times as it is easy to modify.

Windows Task Scheduler would be the best answer to this issue. However it is locked down on our pc's and our IT Admin department said no when I requested access to it.

After working on this since my last post to Tom, I found a way to get this to work. It is a combination of vba and select queries so involved it will give seasoned Access professionals a heart attack (lol). But it works.

Cheers, Kevin

Answer 5

Windows Task Scheduler would be the best answer to this issue. However it is locked down on our pc's and our IT Admin department said no when I requested access to it.

Assuming this is what is best for the company then you need to learn how to get things done with the cooperation of the IT Admin department.

You need to write specifications for what needs to be done in terms of scheduling; how you need to use the Task Scheduler. It is critical that the specifications be clear and precise. They need to also be clear about why it benefits the company. The import part is separate from the scheduling part. You should request a decision be made about how to do it and part of the decision is who develops that scheduling application.

When PCs were new I was employed by a major aircraft manufacturer. The company created a separate end-user department that specialized in things like Word and Excel. For a few months prior to the closing of the location (thousands of people were laid off) I was in the QA group. So you could say I was in the equivalent of the IT Admin department. I know that the personnel in the end-user department did things in crazy ways. I sure thought that some of their solutions were stupid. It would have been better for them to work with us but they were totally separate and had the authority to do crazy things. I hope that you can work better with your IT Admin department in the most beneficial way possible.

One way to get things done might be a study group or whatever you want to call it where you have meetings. You could have a study to develop ways to get the best solutions for problems such as this. Management tends to cooperate with the result of such things.

---

Sam Hobbs
SimpleSamples.Info

Answer 6

> I have Monday in the tblImportDays and 3:00:00 PM in the tblImportTimes, the vba in the On Timer event will evaluate the current day and time and determine if today is Monday at 3pm so it may run the vba.

No, it does not work that way. That's exactly one of my points. The Timer event is a low-priority event and it will tick if nothing more urgent needs to get done. To expect the tick to happen ON THE DOT is asking too much. You should program accordingly, and say "the 3pm timer event has not been marked as Processed yet, so let's do it now (at 3:00:01 perhaps) and then mark it as Processed."

Similarly, if because of a power outage the computer was off for 3 days and nothing happened, several  events are now sitting in the table, not marked as Processed, and will be processed when we come back online.

So although you may have a table with the weekly events that need to happen, you will also need a table with the forever events that need to happen (mostly inserts of the Weekly table) with an extra Processed field.

---

-Tom. Microsoft Access MVP