glgcag (MIS) (OP)
26 Jun 07 19:16
I'm trying to get my Access front-end MDB converted to a SQL Server back end. I can't make it an ADP, so I'm stuck with some limitations.
I have a report that contains 12 different subreports and as far as I can tell, there's no way to use pass-through queries as the recordsource for the subreports, only the main report.
(As a side note, I tried just putting a SELECT statement in the recordsource field of each subreport that uses JetSQL to run the query against the linked SQL tables, but it's REAL slow.)
So, to speed it up, I've discovered that I can set the subreports recordsource to rst.source (if rst = my ado recordset), but it can only be done in design view.
Here's what I have to do for each subreport:
1. Create recordset object with a SQL statement and open it
2. Docmd.echo False and open report in design view
3. set report.recordsource to rst.Source
4. Close report saving it
5. Close recordset
So, I've gotten it to work fine with one of the subreports, but I'm worried that cycling through and opening 12 subreports and setting their recordsources to ado recordsets will take a long time, and possibly there's a better way to do this.