title: Excel VBA - how to query Access database using column values as a parameter?
body: <p>I have an Excel worksheet that has a list of about 1000 Item Numbers in column A on Sheet1. Currently, I import Sheet1 into an Access table named <code>ItemNumbers</code> and run the following query:</p>
  
  <pre><code>SELECT MyTable.ItemNumber, MyTable.ItemName, MyTable.ItemPrice
  FROM [ItemNumbers] INNER JOIN MyTable ON [ItemNumbers].ItemNumber = MyTable.ItemNumber
  ORDER BY MyTable.ItemNumber;
  </code></pre>
  
  <p>And then I copy/paste the output to Sheet2. </p>
  
  <p>How can I do this in VBA in Excel and put the results in a recordset? I can figure out how to loop through the recordset and put the results in Sheet2. I'm just not sure on the code to run the query.</p>
  
  <p>I have the following so far. It just needs to be modified to use the values in Sheet1 Column A.</p>
  
  <pre><code>Dim cn As Object
  Dim rs As Object
  Dim strSql As String
  Dim strConnection As String
  Set cn = CreateObject("ADODB.Connection")
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
      "Data Source=C:\MyDatabase.accdb"
  strSql = "SELECT MyTable.ItemNumber, MyTable.ItemName, MyTable.ItemPrice " &amp; _
           "FROM MyTable " &amp; _
           "WHERE WHERE (((MyTable.ItemNumber)= ??? IS IN Sheet1!A:A ???  )) " &amp; _
           "ORDER BY MyTable.ItemNumber;"             
  cn.Open strConnection
  Set rs = cn.Execute(strSql)
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
  </code></pre>
  
  <p>Thanks!!!</p>