Strange Access Query Behavior with SQL Server

Strange Access Query Behavior with SQL Server

am 04.04.2008 22:36:13 von ysageev

Greetings,

I've been trying to optimize the performance of the queries in an
Access 2007 front end that utilizes SQL server for all tables
(linked). The application runs fine without error. However, when I
run SQL Server profiler it clearly shows that every entry in a
continuous subform results in an explicit, individual query to the
database.

I.e., instead getting a result set in one shot and populating each
entry in the continuous subform with a row from the returned result
set, which would be much faster, it queries across the network for
each entry. Is there a way to correct this? For example, Access runs
the following query multiple times for each form, or once for each
item.

(query has been shortened for readability, copied from profiler)

SELECT "DescriptionId","ProjectTitle","ItemNumber","Description" FROM
"dbo"."SomeTable" WHERE "DescriptionId" = @P1

Thanks!