Normally when Access is slow like that, it means Access
is requesting only some of the records. Sometimes, one
at a time, sometimes depending on the ODBC buffer settings.

Are you dynamically creating this pass-through query?
If so, try not doing that.

(david)

"Mike G[raser]" wrote in message
...
I have a query that executes a Table-Valued Function in SQL server. If I
execute it withing Query Analyzer in SQL server, it completes in 40
seconds.
If I execute the same query in a pass-through query from Access, it takes
over 5 minutes.

I ran sp_lock to view the locking information during the execution of the
query from both locations. Query Analyzer came back with around 5300
locks
immediately and the number remained constant throughout query execution.
When executed from Access, it would add about 100 locks every 10 seconds.
It
would take a very long time to get up to 5300 locks required to complete
the
query!

Does anyone know what's happening here? I thought Access would just pass
the command off to SQL and when SQL is done with it's stuff it would send
the
info back to Access. But, it looks like there must be some sort of
communication going on there that slows it all down?

Anyone have any idea?