I have an oracle database and I am using Access 2003 for the front end and reporting (read only - data is entered from a web interface). In an ideal world I would design my reports to run on the server side in a web interface but I'm not an IT guy and our IT dept is understaffed so I am stuck with access.
The oracle database is highly normalized, so getting the data in a useful format for reporting often takes a number of sub queries, some of which take a long time to run. I thought about using pass through queries but I had trouble dealing with the sub queries and my queries use access functions that I am not sure how to deal with on the server side.
My solution is local tables, this way the 5 - 10 sub queries needed to generate a report are only run once, when you update the local tables. Here's the crux:
When I updated the local tables it runs fine, but when I have tested it on user machines it seems to hang on some of the queries. It could be because the users have low end PCs or it could be an issue with permission levels (but I doubt this is the problem since we ar all using the same ODBC connection string).
I want a cancel button that will stop a query in mid-process in case the query hangs. The delete queries and append queries are run as a sub on one form (RunReportsForm) and I have a status form (ProcessingStatusForm) with a progress bar and a cancel button.
Is there a way to have ProcessingStatusForm.CancelButton_OnClick call the Exit procedure in the RunReportsForm sub? If so would it stop a query that is currently running due to a call from the RunReportsForm?
If that is not possible, would it be possible to stop it if the queries were run from a custom public function module?
The oracle database is highly normalized, so getting the data in a useful format for reporting often takes a number of sub queries, some of which take a long time to run. I thought about using pass through queries but I had trouble dealing with the sub queries and my queries use access functions that I am not sure how to deal with on the server side.
My solution is local tables, this way the 5 - 10 sub queries needed to generate a report are only run once, when you update the local tables. Here's the crux:
When I updated the local tables it runs fine, but when I have tested it on user machines it seems to hang on some of the queries. It could be because the users have low end PCs or it could be an issue with permission levels (but I doubt this is the problem since we ar all using the same ODBC connection string).
I want a cancel button that will stop a query in mid-process in case the query hangs. The delete queries and append queries are run as a sub on one form (RunReportsForm) and I have a status form (ProcessingStatusForm) with a progress bar and a cancel button.
Is there a way to have ProcessingStatusForm.CancelButton_OnClick call the Exit procedure in the RunReportsForm sub? If so would it stop a query that is currently running due to a call from the RunReportsForm?
If that is not possible, would it be possible to stop it if the queries were run from a custom public function module?