Limit query to run only once a day?
- Thread starter Bonifaceg
- Start date
FoFa
Registered User.
- Local time
- Today, 02:26
- Joined
- Jan 29, 2003
- Messages
- 3,672
Store the date last run in a table (I usually use a table called Parameters for this kind of stuff).
Using a Macro with conditions OR vba code, check the date last run to see if it outside your parameters, and either run it or don't.
If you do run it have an update query run right after to update the date last run.
As one way.
Using a Macro with conditions OR vba code, check the date last run to see if it outside your parameters, and either run it or don't.
If you do run it have an update query run right after to update the date last run.
As one way.
- Local time
- Today, 02:26
- Joined
- Feb 28, 2001
- Messages
- 30,501
FoFa is right but left out something important. I am presuming this is an update query or otherwise involves a transaction somehow, something that should not be initiated more than once per day.
If this is a shared .MDB and your users can see that query, there is NO WAY to stop them from running it unless you stop EVERYONE from running it. Everyone except YOU, that is. Even that might not work if you have a bad day and are forgetful.
Basically, the ONLY other way to do this is to build the query so that you don't CARE if it is run once or a bazillion times a day.
I would include a date field in the table. When you run the presumed update, update the date field with the Date$() function, which (when you omit the argument) stores the date of today. I would ALSO place a criterion in the query such that it will only work on records for which the date is not equal to Date$().
If you cannot filter it this way, then your only other solution is to protect the query from all users except the Admin, then build some VBA to execute the SQL and update a separate table with the date of the last time this action was run. Make the VBA examine the table and refuse to run if the date-holder says it was already run today.
The VBA method would work BUT it would be far better if you could just update a table in a way that prevents it from being updated a second time on the same date using WHERE clauses (criteria rows) in the query. That would be far more reliable.
It might also have the side effect of allowing you to do recovery if the query runs for a little while and then blows up. I.e. you update about half the table, then something kicks out the rest of the operation. Fix the cause of the kick-out then run it again, knowing that the records already updated won't be touched.
Basically, this is a case where knowing Murphy's Laws becomes not merely a convenience but a necessity. But you have to know the ENTIRE law, not just the popular part.
"Whatever can go wrong WILL go wrong... design it so that it cannot go wrong."
If this is a shared .MDB and your users can see that query, there is NO WAY to stop them from running it unless you stop EVERYONE from running it. Everyone except YOU, that is. Even that might not work if you have a bad day and are forgetful.
Basically, the ONLY other way to do this is to build the query so that you don't CARE if it is run once or a bazillion times a day.
I would include a date field in the table. When you run the presumed update, update the date field with the Date$() function, which (when you omit the argument) stores the date of today. I would ALSO place a criterion in the query such that it will only work on records for which the date is not equal to Date$().
If you cannot filter it this way, then your only other solution is to protect the query from all users except the Admin, then build some VBA to execute the SQL and update a separate table with the date of the last time this action was run. Make the VBA examine the table and refuse to run if the date-holder says it was already run today.
The VBA method would work BUT it would be far better if you could just update a table in a way that prevents it from being updated a second time on the same date using WHERE clauses (criteria rows) in the query. That would be far more reliable.
It might also have the side effect of allowing you to do recovery if the query runs for a little while and then blows up. I.e. you update about half the table, then something kicks out the rest of the operation. Fix the cause of the kick-out then run it again, knowing that the records already updated won't be touched.
Basically, this is a case where knowing Murphy's Laws becomes not merely a convenience but a necessity. But you have to know the ENTIRE law, not just the popular part.
"Whatever can go wrong WILL go wrong... design it so that it cannot go wrong."
Let me get to this.
