Report is slow for non administrator Access 2007

  • Thread starter Thread starter Arog
  • Start date Start date
A

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?
 
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.
 
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.
 
Hi,

I had the same problem with Access 2010 and SQL Server 2008. I updated the SQL Server to SP2, and now it is better, but image fields are still so slow (25 seconds instead of 5 for an admin user).

Any suggestions :confused:
 
Seems this problem happens on 64 bits server.
Microsoft KB article 2277078
Work around for now, running this SQL command on the server from time to time : DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')
 
Back
Top