Simple report question re: sources for data

[galorin]

I want to report on data coming from a monster MySQL statement with user input data. Within my application, I use ADODB recordsets throughout my application, and I was wondering if I can do the same for generating reports. If so, how do I do it?

 

[dhookom]

I don't think you can base a report on a recordset like you can a form. Have you considered taking the SQL of your recordset and using it in a pass-through query? You could set the p-t as the record source of your report.


Duane
Hook'D on Access
MS Access MVP

 

[PWise]

in reportopen

dim rst as recordset
'set connection ect....

rst.open yourSqlStatment
set me.recordset = rst

this works

let us know with what you need help

 

[PWise]

works only in office 2003 or greater

 

[galorin]

pwise, will try that after I've put out my administration fires.

Another thought I had was to dynamically create a SQL command, using data input from a form. The query would create either a temptable or "create or replace" view based on the data fed to it on the server, then the report would use a select * from this newly created view/temptable

The query as it stands, based off all the data I have takes 8 minutes or so to finish. Restricting just the date range drops it down to a few seconds.

 

[dhookom]

Is there a problem with modifying the SQL of a pass-through query? This would probably run in a "few seconds".

Duane
Hook'D on Access
MS Access MVP

 

[galorin]

pwise's method didn't work 100% for me (pebkac on my part), so I'll try to re-write the querydef before the report launches.