Update pass through query of report recordsource

I am working on upsizing my access backend database to SQL Server.  I have 
linked all sql tables to the frontend and am now running into the issue of 
slow performance on one of the reports.

One way to improve that I thought was to change the recordsource query 
(which is a saved query) to a pass through query.  However there are form 
controls that drive the parameters.  So I was wondering if a better way to 
do this is to create the sql query string in vba and then set the report 
record source.  Is this a viable option?  Is there a better way?

Please ask any leading questions if this is too vague.

Access 2007
SQL Server Express 2005


0
Microsoft
2/10/2010 6:41:00 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
3309 Views

Similar Articles

[PageSpeed] 43

You are close. I would create the SQL statement in code and use DAO to update 
the SQL property of your p-t query:

CurrentDb.QueryDefs("qsptYourQueryName").SQL = strSQL

Then open your report and watch it fly.

-- 
Duane Hookom
Microsoft Access MVP


"Microsoft Access" wrote:

> I am working on upsizing my access backend database to SQL Server.  I have 
> linked all sql tables to the frontend and am now running into the issue of 
> slow performance on one of the reports.
> 
> One way to improve that I thought was to change the recordsource query 
> (which is a saved query) to a pass through query.  However there are form 
> controls that drive the parameters.  So I was wondering if a better way to 
> do this is to create the sql query string in vba and then set the report 
> record source.  Is this a viable option?  Is there a better way?
> 
> Please ask any leading questions if this is too vague.
> 
> Access 2007
> SQL Server Express 2005
> 
> 
> .
> 
0
Utf
2/10/2010 9:39:02 PM
Great, thanks

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message 
news:673498BD-1411-40E9-88F5-7AD3F9C2BDA5@microsoft.com...
> You are close. I would create the SQL statement in code and use DAO to 
> update
> the SQL property of your p-t query:
>
> CurrentDb.QueryDefs("qsptYourQueryName").SQL = strSQL
>
> Then open your report and watch it fly.
>
> -- 
> Duane Hookom
> Microsoft Access MVP
>
>
> "Microsoft Access" wrote:
>
>> I am working on upsizing my access backend database to SQL Server.  I 
>> have
>> linked all sql tables to the frontend and am now running into the issue 
>> of
>> slow performance on one of the reports.
>>
>> One way to improve that I thought was to change the recordsource query
>> (which is a saved query) to a pass through query.  However there are form
>> controls that drive the parameters.  So I was wondering if a better way 
>> to
>> do this is to create the sql query string in vba and then set the report
>> record source.  Is this a viable option?  Is there a better way?
>>
>> Please ask any leading questions if this is too vague.
>>
>> Access 2007
>> SQL Server Express 2005
>>
>>
>> .
>> 


0
Microsoft
2/11/2010 9:04:16 PM
Reply:

Similar Artilces: