Report is slow for non administrator Access 2007


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?
 
Ad

Advertisements

P

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.
 
P

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.
 
Joined
Feb 9, 2011
Messages
2
Reaction score
0
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:
 
Ad

Advertisements

Joined
Feb 9, 2011
Messages
2
Reaction score
0
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')
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top