Report is slow for non administrator Access 2007

[Arog]

I am converting an application from Access 2003 to Access 2007. The report
is against a view containing several million records in SQL Server 2008. In
the Report_Open event I load the serverfilter property to filter the report..

When the report runs on 2003 or as Server Administrator in 2007, it comes up
very quickly. In 2007 as a regular user it seems to hang for quite a while
before the actual report comes up.

In SQL server profiler in 2003 and as admin in 2007 the filtered query gets
sent immediately. As a regular user a simple "select * from myview" with no
where clause gets sent and the app presumably waits for all records to be
returned. Later the correct query gets sent and the report comes up.

I tried putting 1=2 into serverfilter at design time, to no avail.
I have worked around this problem by putting "select * from myview where
1=2" instead of the view name into the recordsource property.

We need to convert to 2007 to take advantage of the ability to generate PDF
files.
This is the tip of the iceberg as we have a lot of reports that are
dynamically filtered on views.

It sounds like some sort of permissions problem. Is there some privilege
that can be assigned the users on sql server that will fix this?

 

[Paul Shapiro]

If you haven't installed the latest Office 2007 service pack, you could try
that. The original RTM version had some performance problems that were
improved with service packs, and maybe you're lucky and yours falls into
that category.