pass thru qry

[slimjen]

Hi all, using access 2003 front end sql server 2008 back end. When opening a form with a subform based on a pass thru query, I am getting the following error:



You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport

I am using the pass thru because subforms based on regular access query is slow since upsizing to sql server and thought pass thru would be faster. I've search the internet but haven't come accross any solutions. Wondering if anyone has a solution that I may try to get this running.
Thanks

[slimjen]

Sorry; I posted in another forum and was answered. Thanks for your time.

[RuralGuy]

Would you care to share your solution with the readers of *this* forum?

[Missinglinq]

Posting the answer here, as Allan suggested, is considered to be proper forum etiquette. Posters coming here in the future, searching for an answer to a similar question, can then benefit from your experience! And you have then added to the forum!

Linq ;0)>

[slimjen]

The Pass Through
doesn't work in that situation because of the way the LinkMaster/LinkChildFields
are processed in a subform or subreport as the main form record changes.

Instead, remove the LinkFields Properties
and change the RecordSource of the subform with the OnCurrent Event of the main
form.

If you don't want linked tables
then use a dynamic query or a Stored Procedure with parameters. I found the best
performance using ADODB commands to return a recordset from a parameterised
Stored Procedure. Set the subform's recordset property to the returned
recordset.

Thank you

[RuralGuy]

Thanks for sharing SlimJen!