Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Pass-through queries or ado recordsets for subreports?

Pass-through queries or ado recordsets for subreports?

Pass-through queries or ado recordsets for subreports?

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?

RE: Pass-through queries or ado recordsets for subreports?

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

Procrastinate Now!

RE: Pass-through queries or ado recordsets for subreports?

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!

RE: Pass-through queries or ado recordsets for subreports?

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.

RE: Pass-through queries or ado recordsets for subreports?


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.

RE: Pass-through queries or ado recordsets for subreports?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close