Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Pass-through queries or ado recordsets for subreports?

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.

Any thoughts?
Crowley16 (TechnicalUser)
27 Jun 07 8:50
well, if you've already got the recordset, then why not just set the .recordset property of the report to rst?

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

glgcag (MIS) (OP)
27 Jun 07 11:21
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 (Programmer)
2 Jul 07 19:26
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 (MIS) (OP)
3 Jul 07 15:01
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 (Programmer)
3 Jul 07 17:47
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 (TechnicalUser)
3 Jul 07 17:53
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/

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