Pass-through queries or ado recordsets for subreports?

[glgcag]

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.

Any thoughts?

 

[Crowley16]

well, if you've already got the recordset, then why not just set the .recordset property of the report to rst?

--------------------
Procrastinate Now!

 

[glgcag]

Because I get an error that says "feature unavailable in an mdb". I think that's only possible in an adp file.

So, as far as I can tell, setting the recordsource property to rst.source is the only way to do it. But I have to do it in design view which really sucks!

 

[lameid]

Any query that you can get to return data by double clicking it in the database window should be able to be used as a report recordsource.

So you should have no problem making a subreport using a passthrough query so long as you don't mind saving the connection information with the query. That being said, if you have to use criteria on a passthrough query to keep from returning too many records, I would recommend changing the where clause of the SQL property of the querydefs before running the reports. Also, having used an ADP, I seriously have wondered if it was worth it.

 

[glgcag]

lameid,

Since I'm using an mdb file, subreports cannot have pass-through queries as their recordsource. It's a weird limitation, so I'm trying to work around it for now. I do change the .sql property of the querydef as needed, as you mentioned, but since I can't use a pass-through query, I lose quite a bit of speed when running the report.

 

[lameid]

For the sake of trying, I just put a subreport based on a pass trough query in a report and it worked.

The Main report was using a JET Table and the sub report the pass through query. It wasn't until I tried to specify Master and Child Fields that I ran into a problem.

 

[Remou]

You can specify master and child fields in the load event of the main form.

Some links:
How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998/EN-US/

ACC2000: Forms Based on ADO Recordsets Are Read-Only

http://support.microsoft.com/kb/227053/EN-US/