none
How do I get a query plan for an ADO query coded in VBA in Access 2016? RRS feed

All replies

  • Hi Newely,

    Thanks for visiting our forum.

    Then this forum mainly discusses general questions and feedback about Office client, since your issue is about VBA in Access, I'llmove your question to the following dedicated MSDN forum for Access:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, March 8, 2017 3:08 AM
  • Assuming you running 2010 on an x64 box, then this reg key will turn on “show plan”

    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
    "JETSHOWPLAN"="ON"

    You need to exit/restart Access. When you run your query, you should find a text file called SHOWPLAN.OUT (it will be placed in the current default folder – usually my documents.

    Don’t forget to turn this off after you are done, since the text file can/will grow quite large.

    I would say however that looking at a query plan in a lot of cases will not really solve your performance issues.

    The “low” hanging fruit things to check:

    Join and group by columns used – are they indexed?

    Avoid dlookup() and other expressions when possible.

    It also not clear if you executing some select query, or filling a reocrdset and then doing some processing (updates).

    Regardless, the above should generate a query plan for you.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, March 8, 2017 4:02 AM