automatically building reporting database

[wilkob]

I am in need of advise from you experts

I want to build a database stored on a server which can be used by our employees as basis for their Excel pivot reporting

The idea is as follows:

I make a query that will get selected data (via odbc) from several Progress database tables of our ERP system and write them to one MS access table

The idea is to run each night at midnight a query (append or make table) via a schedule and gradually build a huge database table holding sales details

This table will be used as basis for the Excel pivot reporting
These reports are currently run directly on the ERP system's database which slows things down

Question:

1. Is it possible to automate the queries (perhaps via scheduler or 3rd party program ?

2. what would be better to use an append query that adds data or a maketable query which constantly replaces the data ?

3. How many ODBC connections on a MS Access database can I have at the same time ?

 

[Rx_]

I use Excel Object Model Programming for all reports.
The difference is, my data is on MS SQL Server.
Using SQL Views and /or Access Queries only the data needed for the report is pulled over.

In your case, if Access has ODBC linked tables to the server DB, write the queries in MS Access - then learn to use some Excel Automation to create the Excel output.