A
Alok Joshi
I need help from some Gurus.
Here is my situation. I have developed a large system for a client based on
Access 97 database. Access is used for data storage and manipulation and
there are hundreds of tables and queries. There are over 100 different
reports that have been coded. The data processing and reporting code
consists of over 20000 lines. It is a single user database and my clients
use it as such. The data has not been separated into a separate back-end
database. The database is used so heavily - entering and editing data,
producing reports etc. that there is not enough time to fire off reports
specially since it is tied up with other tasks like date entry. Some of the
reports take up hours of processing time both due to the quantiny of data
and the large number of processing steps required. The reports are all Excel
reports produced dynamically through code. While the reporting code reads a
lot of tables and creates some temporary tables in the process, it does not
change any data in any basic tables. More enhancements and additional
reports are added almost daily if not weekly. There is not much
documentation on which reports rely on which tables and queries.
One option is that the user makes a copy of the database say each morning
and uses say the original for data entry/editing and the copied version for
reporting. Since the database is over 800MB this takes time.
I was wondering if there is a better way of doing this.
Can I, for instance, copy tables into the reporting database based on the
time stamp - bring in only the tables which are newer? Then I can run some
code at any time during the day and bring in only entirely new tables or
those that have changed (either data has changed or structure has changed).
How can I do this?
If there is no way I can determine the time stamp on the tables, can I find
out which tables and queries are opened/used for any report? (is there an
event one can capture when any table or query is opened by code? If so I can
then identify the name of the table/query and record it.) In this fashion if
I succeed in recording the names of all the tables and queries that are
used, then in my reporting database I can program it to import all these
tables/queries.
I would not like to link to table in the main database from the reporting
database.
Can I request you to give me some pointers?
Thanks
Alok
Here is my situation. I have developed a large system for a client based on
Access 97 database. Access is used for data storage and manipulation and
there are hundreds of tables and queries. There are over 100 different
reports that have been coded. The data processing and reporting code
consists of over 20000 lines. It is a single user database and my clients
use it as such. The data has not been separated into a separate back-end
database. The database is used so heavily - entering and editing data,
producing reports etc. that there is not enough time to fire off reports
specially since it is tied up with other tasks like date entry. Some of the
reports take up hours of processing time both due to the quantiny of data
and the large number of processing steps required. The reports are all Excel
reports produced dynamically through code. While the reporting code reads a
lot of tables and creates some temporary tables in the process, it does not
change any data in any basic tables. More enhancements and additional
reports are added almost daily if not weekly. There is not much
documentation on which reports rely on which tables and queries.
One option is that the user makes a copy of the database say each morning
and uses say the original for data entry/editing and the copied version for
reporting. Since the database is over 800MB this takes time.
I was wondering if there is a better way of doing this.
Can I, for instance, copy tables into the reporting database based on the
time stamp - bring in only the tables which are newer? Then I can run some
code at any time during the day and bring in only entirely new tables or
those that have changed (either data has changed or structure has changed).
How can I do this?
If there is no way I can determine the time stamp on the tables, can I find
out which tables and queries are opened/used for any report? (is there an
event one can capture when any table or query is opened by code? If so I can
then identify the name of the table/query and record it.) In this fashion if
I succeed in recording the names of all the tables and queries that are
used, then in my reporting database I can program it to import all these
tables/queries.
I would not like to link to table in the main database from the reporting
database.
Can I request you to give me some pointers?
Thanks
Alok