I have an Oracle database which contains views which I need to pull data from daily. All of these view exists in the Access database as linked tables via ODBC. Becuase of business requirements I have a bunch of users that only have the ability to use Access to get thier reporting done.
Rather than have every user of the Access database directly query the linked tables every time they want to look at the data, I have a scheduled task on my XP machine that opens a Macro in Access that runs several create table queries early in the morning. The queries all get data from the views for a specified date range and creates a table for them to look at locally.
I have about a dozen queries which I want to control the date range for all. The dates would be the same for all queries.
The way I went about this was to create a table which I called query_dates. The table contains one record with two fields. begin_date and end_date.
So what I would do in my queries is to use:
Now this query seems to work find independently (when i click on the query in access) but there are times when it runs from the scheduled task that it just hangs or hours.
It also seems if I hard code the dates instead of using the sub-selects it works fine e.g.
instead of the above.
Could using the sub-select cause the query to hang? Is there some time out value I can increase?
I also tried creating a function with the date parameters and have had the same issue. Why can't I have this date parameter be variable with out causing such havok?
Thank you.
Rather than have every user of the Access database directly query the linked tables every time they want to look at the data, I have a scheduled task on my XP machine that opens a Macro in Access that runs several create table queries early in the morning. The queries all get data from the views for a specified date range and creates a table for them to look at locally.
I have about a dozen queries which I want to control the date range for all. The dates would be the same for all queries.
The way I went about this was to create a table which I called query_dates. The table contains one record with two fields. begin_date and end_date.
So what I would do in my queries is to use:
Code:
SELECT
linked_table_name.* INTO [Local Table Name]
FROM
linked_table_name
WHERE
(((linked_table_name.[DATE FIELD]) Between
(select begin_date from query_dates) And
(select end_date from query_dates)));Now this query seems to work find independently (when i click on the query in access) but there are times when it runs from the scheduled task that it just hangs or hours.
It also seems if I hard code the dates instead of using the sub-selects it works fine e.g.
Code:
between #3/1/2007# and #3/30/2007#Could using the sub-select cause the query to hang? Is there some time out value I can increase?
I also tried creating a function with the date parameters and have had the same issue. Why can't I have this date parameter be variable with out causing such havok?
Thank you.
