Temp tables for reporting data

[Mark]

Hello again

I am migrating an MDB/MDE to MSDE/ADE (both XP).

Currently the front-end (mde) holds several tables which are used to
temporarilly store data for reporting etc. There are also tables in another
mdb (linked) on the local hard drive used for a similar purpose.

Using an ADP(E) then, what is the best way to store temporary data for
reporting etc.?

We are trying to avoid stressing the server too much (as it's low spec) and
keep network traffic down. There is a large amount of processing in some
cases and ideally I'd like to pull the raw data to the front-end or a local
mdb.

I gather its not possible to create tables which are stored in a front-end
ADE. Would it be better to use an MDE with linked tables then so some could
link to the server, others to an mdb, and if necessary some could be stored
in the mde itself (which is frequently refreshed or compacted btw)?

Many thanks

Mark

 

[Tom Ellison]

Dear Mark:

This is such a good question! And I have a suggested answer.

Please look at the thread "New user needs help!" It is just two posts older
than yours. I believe I covered a bit of this there.

Please get back with what you think.

Tom Ellison

 

[Mark]

Hi Tom

It's a good answer, but now quite what I'm looking for. There is another
factor I should have mentioned earlier... I have generic reporting code
which works in conjunction with Word and Excel templates. Basically, raw
reporting data is written to an mdb on the local machine and it is then
processed by the template. This allows extremely flexible and quick report
(template) generation without having to update the front-end each time. One
of the reasons I wanted to avoid leaving the data on the server is that I
don't want to have to re-visit these templates. There are loads and time is
an issue.

In the past I have worked with mde front-ends attached to SQL Server using
linked tables, as well as front-end tables etc. Would there be any serious
performance impact in doing it like this as opposed to using an ADP and
doing all the data manipulation using backend tables? As I said before, the
server is very low spec...

Many thanks

Mark

 

[Tom Ellison]

Dear Mark:

You can have a local back end, either Jet or MSDE.

Tom Ellison