vba: do something if it is midnight

Question

i will need to run a process once every 24 hours at midnight. it wont matter if it's 12:00:00 or 12:00:03, just as long as it is run once at around midnight.

would i need to have a timer control checking what time it is every minute or is there a more sophisticated/simpler way of doing this?

what would be the if statement to check whether it is midnight currently?

i will be using access-vba to do this

Answer 1

Could you not use task scheduler to run a vbs file which could use ActiveX to run the code you want in your database

Answer 2

Do you have a need to leave the database open all the time?

If not, consider creating a task with Windows scheduler to open your database at midnight. Use an autoexec macro which kicks off your process when the database opens.

If you don't want that process to run every time the database opens, name the macro something other than autoexec. Then use Access' /exec command option with your macro name when you create the task in Windows scheduler.

Update: In answer to "what would be the if statement to check whether it is midnight currently", you can use this expression:

If TimeValue(Now()) = #12:00:00 AM# Then

However, I doubt that will do you much good because that will only ever be True precisely at midnight. You want your process to run at 12:01 AM if it hasn't already. So I think what you need is a CompletedJobs table with a Date/Time field, run_date, and a unique index on that field. Then you can use DCount with the Date function to see whether today's date exists in CompletedJobs.

DCount("run_date", "CompletedJobs", "run_date = #" & Date() & "#")

If DCount <> 0, your process has run today. If DCount = 0, run your process, then insert today's date into CompletedJobs.

INSERT INTO CompletedJobs (run_date)
VALUES (Date());