Identifying Tables And Queries Used in Reporting Code

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





.

Relevant Pages

  • Re: Performance Improvements: Hardware vs. DB Design
    ... > I recently designed & developed a data warehouse for a client of ours. ... > database with a substantial amount of data, ... I think many companies are inclined to add more server ... the queries, revise the indexes if necessary, and verify the reporting ...
    (microsoft.public.sqlserver.server)
  • Re: Any tips on working with a database that you did not create?
    ... am new to Access and taking over an entire database. ... In fact it had been thrown together using wizards ... Look through the queries and spot whether any of them are anything other ... Look at the reports. ...
    (microsoft.public.access.gettingstarted)
  • Re: Any tips on working with a database that you did not create?
    ... am new to Access and taking over an entire database. ... In fact it had been thrown together using wizards ... Look through the queries and spot whether any of them are anything other ... Look at the reports. ...
    (microsoft.public.access.gettingstarted)
  • Re: Any tips on working with a database that you did not create?
    ... I was given what looked like a "nearly finished" database which apparently represented a great deal of work. ... In fact it had been thrown together using wizards based on a wholly wrong table structure. ... Check to see if the database has "linked tables" - a common way of building a database is to put the tables in one file and all the queries, forms and reports in another, and link the two. ...
    (microsoft.public.access.gettingstarted)
  • Re: Designing a new DB
    ... Stay with one table and use queries and/or reports to group your data by ... ACC2002: Database Normalization Basics ... Database Normalization Tips by Luke Chung ... > There will be simple calculations in certain fields in both the> monthly and annual reports. ...
    (microsoft.public.access.gettingstarted)
Quantcast