By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

Time based triggers

P: 21
Hi

I want a method to do the following in access.

Every day a new row should get added to the table. This should be a time based trigger (ie when the sys date changes the tigger should occur and insert a row)

I am using a windows machine so cron function is not feasible.

using some script is fine but i want the process to happen automatically without anyone initiating the process. Even if the database is not monitored for days it should keep doing its job.
Nov 23 '06 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You're problem is if the Access application is not 'open' no code will be processed. Access will not activate any command regardless of trigger unless the application is actually opened.



Hi

I want a method to do the following in access.

Every day a new row should get added to the table. This should be a time based trigger (ie when the sys date changes the tigger should occur and insert a row)

I am using a windows machine so cron function is not feasible.

using some script is fine but i want the process to happen automatically without anyone initiating the process. Even if the database is not monitored for days it should keep doing its job.
Nov 23 '06 #2

P: 21
You're problem is if the Access application is not 'open' no code will be processed. Access will not activate any command regardless of trigger unless the application is actually opened.
Ok let me tell you what is that i want to happen.

there is a table A that which collects user data on a daily basis for 5 users.

that means each day 5 new rows are added for 5 users with default col values = 0.

even if the user forgets to enter values for that day the values should be set to 0.

So finally one day when someone generates a report for that user and his data. it should display all the 30 days with either 0 or the value which the user entered.
Nov 23 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Ok let me tell you what is that i want to happen.

there is a table A that which collects user data on a daily basis for 5 users.

that means each day 5 new rows are added for 5 users with default col values = 0.

even if the user forgets to enter values for that day the values should be set to 0.

So finally one day when someone generates a report for that user and his data. it should display all the 30 days with either 0 or the value which the user entered.
You can create a table to store a date. It just needs one field and only needs to store one record.

Create a date trigger to activate as soon as the application is opened each day. This event will have to be tied to the startup form. Note: if the application is not opened it will not run.

Once the code to add the values has been run. change the value in the date table to the current date. Therefore the system will check that table first to see when the event was last run.

You may be able to run it for each day since that date and update the date to the current date when finished.

If the date in the table is the current date then the event will not run. Therefore the event will only run once.
Nov 23 '06 #4

Expert 5K+
P: 8,435
Ok let me tell you what is that i want to happen.

there is a table A that which collects user data on a daily basis for 5 users.

that means each day 5 new rows are added for 5 users with default col values = 0.

even if the user forgets to enter values for that day the values should be set to 0.

So finally one day when someone generates a report for that user and his data. it should display all the 30 days with either 0 or the value which the user entered.
Well, that could be done at the time you generate the report.

Alternatively, you could use the Windows Scheduling Manager (I may have the name wrong) to fire off the "add a record" application each day.
Nov 23 '06 #5

P: 21
Well, that could be done at the time you generate the report.

Alternatively, you could use the Windows Scheduling Manager (I may have the name wrong) to fire off the "add a record" application each day.
Ok i will try that. One more help i would need is that

select sysdate from dual; this query works in oracle but not in access.

what sql should i use to get system date in Access
Nov 23 '06 #6

Expert 5K+
P: 8,435
Ok i will try that. One more help i would need is that

select sysdate from dual; this query works in oracle but not in access.

what sql should i use to get system date in Access
I believe Date( ) is generally used in Access to return the current system date.
Nov 23 '06 #7

Expert 5K+
P: 8,435
I believe Date( ) is generally used in Access to return the current system date.
By the way, I hope you didn't miss mmccarthy's message. We almost bumped into each other - about 1 minute apart.
Nov 23 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.