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

MS - How About Some Answers?

Hey MS,

I realize that this a free forum, but given the number of people who have problems with SSRS configuration and the fact that there are virtually no solutions posted on MSDN, I would think that MS would be more responsive to these forum posts. The most recent post from someone who can't get SSRS working had 1,154 views, but no answers--similar to the other posts by those who can't get the product to work. There seem to be responses to the "easy" questions, like features and techniques.

Unlike the SQK2K version, this version has a GUI-based config tool. That should guarantee proper configuration, but it doesn't. The online knowledge base is not yet adequate to support installation and configuration. So I think that MS should be more responsive to post requesting configuration help.

Hi rlo,

Good post. I like your comments. RS configuration is a sore spot for both MS and for our customers. We have made and are continuing to make configuration better. The Configuration Tool in RS 2005 is a start, but not the panacea for configuration problems. Each version, we will definately work to make configuration easier. And sadly, there is no one solution to configuration problems.

In regards to getting answers for configuration problems via helper forums:

1. Clarify the problem in excrutiating detail. Little detail and most MS employees will move to the next post to help out.

2. Be polite. We have feelings too.

3. Bump the post if you do not get an answer within the first page.

The Reporting Services team takes great pride in helping customers. But do remember there are only a couple handfulls of us, while there are many thousands of you. Your help in responding to customers is as important as ours.