MS Access & MS SQL Server: long time answers

I use MS ACCESS and tables linked to MS SQL SERVER tables.
There are indexes defined (they are visible in MS ACCESS too).
Sometimes DLOOKUP functions are taking long time.
Why?Are you using a standard Microsoft Access database with links to the SQL Server tables, or are you using a Microsoft Access Data Project (.adp file extenstion)?

If you are linking to SQL server, MS Access will do it's best to formulate your queries as "pass through", meaning only the sql text is sent to the server and only the process results are returned. If it is unable to formulate it as a pass-through query than Access must temporarily transfer all the tables involved across your network and then process the results locally. You can imagine how this could be unimaginably slow. (?)

I recommend that you convert your application to an Access Data Project if possible.

blindman|||I am not sure about ADP, but Access2K does not use pass-through for linked objects. In fact, the only way you can use pass-through is by selecting it from the query builder.|||It is standard Access 97 VBA application with use of DLOOKUP function.
The long time answers for DLOOKUP queries are not the rule.
I can use DLOOKUP about 10 times with fast answer (under 1 second) and next time I have to wait 10 seconds.sql