Reports from multiple databases

[cyberbiker]

Something was brought up recently about running ad hoc reports from multple Access databases.
My first idea was to create a new database "Reports.MDB", link the needed tables from the other databases, create the report and run it. Should work and nobody had a better idea. But I think this way would be just a bit "goofy" for want of a better term.
Any ideas would be appreciated.
By the way, this is only hypothetical at this time. No one actually is asking for such a report. At least not yet.

Terry (cyberbiker)

 

[JoanneM]

Morning Terry

Sounds like a plan to me but experience taught me that when creating 'ad hoc' you can bet someone will want something similar later and you'll be back re- inventing the wheel so I used the properties option on the queries to document the process stage and rpt it sourced this really did help later when I was asked 2 months later and whilst working on a completely different database to 'go back' and tweek the report.

Additionally any code you write, keep each in its own module for the report it is dealing with, because sometimes Ad hoc becomes weekly and needs importing into the source application and if you have used common functions you then have the problem of debugging and testing - whereas this way you just import ( test & de duplicate laterif you have to ) you'd have the solution in place ( the rest the user need not know about)

The only other consideration is Data sensitive reports and who will have access to what.

Hope this helps

Jo

 

[cyberbiker]

Thanks JoAnne,
Your advice is very good. I have had that happen several times (maybe I should say a bunch of times). And it is always good to keep code in modules so it can be reused.
On my last job every "ad hoc report" I wrote was saved. In fact, I used them to write an "ad hoc report writer where I created a small app that would let the user choose a "standard report" from a combo box which would give the select clause (from a table), then choose the criteria by selecting from various comboboxes then entering a value in a text box and create the SQL statement for the report
They were limited to not more that 3 criteria
ie: CompanyName like "Donald Duck" and State = PA and Equipment = "Broken" order by "phone number" ascending.
Most of the combo boxes were filled by reading the table structure.
Worked like a charm and let the end user write handle about half of what they needed. By adding e-mail capabilility and the ability to either print, preview or display the data everybody seemed pleased.
Just remember that if you do soemthing like this, be certain thar preview is the default and that the user knows how many records he or she has found. 56,237 records are way more than you really want to print!

Terry (cyberbiker)