473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Time based triggers

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
7 3344
MMcCarthy
14,534 Expert Mod 8TB
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
geek491
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
14,534 Expert Mod 8TB
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
Killer42
8,435 Expert 8TB
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
geek491
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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

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

Similar topics

1
by: Chris W | last post by:
A friend of mine wants me to create a php web application that will show what tables or table/cols are being replicated and the current status for each replication. He is telling me it won't be...
6
by: aj | last post by:
DB2 LUW 8.1 FP11 Can't I overload SPs based on param types rather than just number of params? If I do: CREATE PROCEDURE FOO( P_DATE_OLD DATE,P_DATE_NEW DATE) then CREATE PROCEDURE FOO(
3
by: rdudejr | last post by:
Hi all, Ive got a database approx 350 GB in which Im getting very high Time waited for prefetch. This is directly out of the snapshot for the db (these are for the entire database I assume as I...
1
by: geek491 | last post by:
Hi I want a method to do the following in oracle 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...
1
by: abhi81 | last post by:
Hello All, I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp....
0
by: Anthony Paul | last post by:
Hello everyone, I'm looking into SQL Server 2005's Notification Services and wasn't able to find a way to create a notification event service based on a DML event such as an Insert or an Update...
0
debasisdas
by: debasisdas | last post by:
trigger sample code Ex#10 ======================= INSTEAD OF TRIGGER ---------------------------------------- create or replace trigger mytrig instead of delete or insert or update on eview...
0
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful. TRIGGERS: =============== Database trigger is a PL/SQL block that is...
21
by: puzzlecracker | last post by:
Problem: I send a lot of requests to the application (running on a different box, of course), and I receive back responses from the app . Below: socket corresponds to Socket socket=new...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.