title: VBA ADODB- Select query using the excel sheet of the same workbook as Database
body: <p>I am novice in VBA so please don't mind if the question is of low level.I am trying to run a SQL query where the data has to be extracted from one of the sheets of the same workbook.</p>
  
  <p><a href="https://i.stack.imgur.com/su5Ca.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/su5Ca.jpg" alt="enter image description here"></a></p>
  
  <pre><code>SQL = "Select ProductNumber from [sData$] where ProductSource = " &amp; pSource &amp; "
  
  'pSource is a string that stores Product Source
  'sdata is a sheet named as Data in the workbook
  
  dataPath = ThisWorkbook.Fullname
  
  'Not sure if this is the value I shall send as datapath in getData function
  
  Set rst = getData(dataPath,SQL)
  rst.Open
  </code></pre>
  
  <p>The getData function is defines as below</p>
  
  <pre><code>Public funtion getData(path as String, SQL as string) as ADODB.Recordset
  Dim rs as ADODB.Recordset
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset
  cn.Open ("Provider= Microsoft.Jet.OLEDB.4.0;" &amp; _
             "DataSource= " &amp; path &amp; ";"&amp;_
              "Extended Properties=""Excel 8.0;HDR=Yes;FMT=Delimited;IMEX=1;""")
  rs.ActiveConnection =cn
  rs.Source= SQL
  Set getData =rs
  End Function
  </code></pre>
  
  <p>Now after I get the numbers from Data sheet, I need to find the corresponding
  ProductCompany from Relation sheet. 9 is for Amul, 5 is for Nestle and so on.</p>
  
  <p>Relation:</p>
  
  <p><a href="https://i.stack.imgur.com/kBwTb.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kBwTb.jpg" alt="enter image description here"></a></p>
  
  <p>I am not sure how to do that. The numbers corresponds to their respective Product company in order.</p>