Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    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.

    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    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.

  4. #4
    Join Date
    Jan 2004
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts