pass thru qry

[slimjen1]

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

 

[Guus2005]

A solution? No. A workaround? Yes.

Create a temporary table (in SQL Server 2008) containing the result of the crosstab query.
Use this table as a source for your form.

HTH

 

[slimjen1]

Hi Guus, thank you for the reply however, I am not following you answer. The pass thru is not based on a crosstab. Now; I can probably create a make table based on the pass thru but how would this get updated when I add more records to the orginal tables? Please advise. Thank you

 

[Pat Hartman]

The key to good performance with linked SQL Server tables is to use queries that have criteria that limits the number of rows returned. No need for pass through queries. Access makes every effort to pass through every query anyway.

 

[Galaxiom]

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.

 

[slimjen1]

thanks so much for the suggestions. I'll try these.

 

[Pat Hartman]

If you are going to go down the unbound form path, you probably shouldn't be using Access at all. You should be using something easier to distribute since you will not be getting any benefit from Access' RAD environment if you have to code everything yourself instead of letting Access do it for you.

 

[slimjen1]

The key to good performance with linked SQL Server tables is to use queries that have criteria that limits the number of rows returned. No need for pass through queries. Access makes every effort to pass through every query anyway.

Thanks Pat. A light bulb went off. I guess I was getting too hung up on the fact that the database has a SQL backend
Thanks again!