title: Microsoft access query returning incorrect result when called from Excel using ADO
body: <p>i have a query saved in microsoft access 2010 which when run from within access returns the expected result (no records found).</p>
  
  <p>However, when i use an ADO connection from excel (a select query), i get some rows back which is confusing because i already have criteria in the query that excludes the results which are returned. </p>
  
  <p>I have tried a comibination of things including changing the cursorlocation property to aduseclient, changing the cursortype to static and then also tried changint it to keyset as shown below: </p>
  
  <p>TRIAL 1</p>
  
  <pre><code>Set objRst3 = New ADODB.Recordset
  objRst3.Open strSql, CurrentProject.Connection, adOpenKeyset
  </code></pre>
  
  <p>TRIAL 2</p>
  
  <pre><code>Set objRst3 = New ADODB.Recordset
  objRst3.Open strSql, CurrentProject.Connection, adOpenStatic
  </code></pre>
  
  <p>i have also tried including my criteria in the select statement sent from excel and also excluding the criteria and instead placing it in the query designer within microsoft access.</p>
  
  <p>Im not using the JET driver to connect to access, the connection string im using 
  is</p>
  
  <pre><code>GetAccessConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; path &amp; ";"
  </code></pre>
  
  <p>However, im still getting the strange result. How can i get round this please</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>The structure of my query is as follows. The filter criteria that is not being picked up from excel is the line 'AS MAIN <strong>WHERE STATUS &lt;> "CLPd"'</strong> :</p>
  
  <pre><code>SELECT DISTINCT ..
  FROM 
          (
            SELECT DISTINCT ... STATUS
              FROM ( 
                SELECT distinct .. FROM ... AS OD 
                LEFT JOIN ((select ...FROM ..) as ST) on (OD.id = st.id) and      (od.ord = st.ord)
              )
          ) AS MAIN WHERE STATUS &lt;&gt; "CLPd"
  LEFT JOIN 
          (
             SELECT DISTINCT ....
              FROM tbl1
              INNER JOIN tbl2 on tbl1.id = tbl2.id
              WHERE tbl2.col1 = "Y" and tbl1.col2 = "Y"
              ORDER BY tbl1.id  
          ) AS ITEMCONFIG on MAIN.itemcode = ITEMCONFIG.itemcode
  WHERE
          (
          MAIN.CUSTMR not Like "OTHER*" and MAIN.DATE &lt; DATE()
          )
  ORDER BY MAIN.DATE
  </code></pre>