|
|
I'm just starting to use pass-through queries to sql server which I am
setting in code. I set the recordsource of report to this query. Hopefully,
this is right.
strSql="SELECT * From Table Where Field=" & Parameter
CurrentDb.QueryDefs("qryDCAReport").SQL = strSql
Report.RecordSource = "qryDCAReport"
Do I need to set the subreports in this report to pass-thrpugh queries,
also? Or should I use the link Master/Child? I'm looking for better
performance. Where will the processing be done for the subreports, in Access
or in SQL Server?
|
|
A pass-through query means that the SQL statement is passed directly
to SQL Server, where it is executed. Therefore you need to use
Transact-SQL in your strSql variable, not Access SQL. Because it's
executed on the server, expressions and VBA functions will just give
you a syntax error. To test the syntax, open Query Analyzer (or SSMS
depending on version) and execute the complete string. Server-side
execution is usually more performant than client-side.
--Mary
On Tue, 12 Jun 2007 13:40:02 -0700, Crossh
<Crossh[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >I'm just starting to use pass-through queries to sql server which I am
>setting in code. I set the recordsource of report to this query. Hopefully,
>this is right.
>
> strSql="SELECT * From Table Where Field=" & Parameter
> CurrentDb.QueryDefs("qryDCAReport").SQL = strSql
> Report.RecordSource = "qryDCAReport"
>
>
>Do I need to set the subreports in this report to pass-thrpugh queries,
>also? Or should I use the link Master/Child? I'm looking for better
>performance. Where will the processing be done for the subreports, in Access
>or in SQL Server?
|
|
I have been passing parameters to sql server in my pass-through queries, and
they are working fine.
Sorry, I guess I wasn't clear in my question:
If the main report is set to a pass through query, do I need to set the
subreports in a report to pass-through queries, also? Or should I use the
link Master/Child and just use an access query? I'm looking for better
performance. Where will the processing be done for the subreports, in Access
or in SQL Server?
"Mary Chipman [MSFT]" wrote:
[Quoted Text] > A pass-through query means that the SQL statement is passed directly
> to SQL Server, where it is executed. Therefore you need to use
> Transact-SQL in your strSql variable, not Access SQL. Because it's
> executed on the server, expressions and VBA functions will just give
> you a syntax error. To test the syntax, open Query Analyzer (or SSMS
> depending on version) and execute the complete string. Server-side
> execution is usually more performant than client-side.
>
> --Mary
>
|
|
On Thu, 14 Jun 2007 08:33:00 -0700, Crossh
<Crossh[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >I have been passing parameters to sql server in my pass-through queries, and
>they are working fine.
>
>Sorry, I guess I wasn't clear in my question:
>If the main report is set to a pass through query, do I need to set the
>subreports in a report to pass-through queries, also? Or should I use the
>link Master/Child and just use an access query? I'm looking for better
>performance. Where will the processing be done for the subreports, in Access
>or in SQL Server?
>
Master/Child isn't supported for passthrough queries, so it is more
difficult to limit the records for subreports when using passthrough
queries. There are workarounds like changing the selection criteria
for each master row, or loading local JET work tables, but they aren't
pretty.
We usually use regular linked tables for the subreports, or better
yet, linked views. Then we can use normal Master/Child settings. We
find that performance is usually acceptable.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
|
|
Another way would be to use ADP for creating your reports/subreports but its
long term prospect seems dim at the moment.
My opinion: if linked tables - or better, using Views - aren't giving you a
sufficient performance, store the relevant data for the subreports in a
separate table by using code directly inside the SP. Adding a datetime
field should make easy the cleanup of this table at regular intervals.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Armen Stein" <ArmenStein[ at ]R3m00v3Th1s.gmail.com> wrote in message
news:1e8d73lpgdrvkutpuvadac1oje2buqmgt9[ at ]4ax.com...
[Quoted Text] > On Thu, 14 Jun 2007 08:33:00 -0700, Crossh
> <Crossh[ at ]discussions.microsoft.com> wrote:
>
>>I have been passing parameters to sql server in my pass-through queries,
>>and
>>they are working fine.
>>
>>Sorry, I guess I wasn't clear in my question:
>>If the main report is set to a pass through query, do I need to set the
>>subreports in a report to pass-through queries, also? Or should I use the
>>link Master/Child and just use an access query? I'm looking for better
>>performance. Where will the processing be done for the subreports, in
>>Access
>>or in SQL Server?
>>
>
> Master/Child isn't supported for passthrough queries, so it is more
> difficult to limit the records for subreports when using passthrough
> queries. There are workarounds like changing the selection criteria
> for each master row, or loading local JET work tables, but they aren't
> pretty.
>
> We usually use regular linked tables for the subreports, or better
> yet, linked views. Then we can use normal Master/Child settings. We
> find that performance is usually acceptable.
>
> Armen Stein
> Microsoft Access MVP
> www.JStreetTech.com
>
|
|
|