title: How to query a MS-Access Table from MS-Excel (2010) using VBA
body: <p>I am trying to write some vba code in Excel to query a table in Access. I have tried multiple code samples for this such as the added links and they all seem to fail at the "Open connection" part. I have tried using different references but I'm not sure which I should be using, what the differences are between some of the different versions (ie. Microsoft ActiveX Data Objects 2.0,2.1,...,6.0) or what the provider information should be. For the provider information I've usually been seeing something along the lines of </p>
  
  <blockquote>
    <p>"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="</p>
  </blockquote>
  
  <p>But I'm not sure if that is what I need to use or why/what conditions anything in the provider string shown above should change. Can someone please educate me on how to properly do this sort of thing?</p>
  
  <p>Note: If at all possible I would like a solution that would work without having to download any other application and would work for both 2007 and 2010 versions of Access and Excel since this will need to run on different computers with possibly different versions of office.</p>
  
  <p>Links to similar questions:
  <a href="https://stackoverflow.com/questions/7095447/excel-vba-query-to-access-is-failing">Excel VBA query to access is failing</a>
  <a href="http://www.mrexcel.com/forum/showthread.php?t=527490" rel="nofollow noreferrer">http://www.mrexcel.com/forum/showthread.php?t=527490</a></p>
  
  <p><strong>Code:</strong>  </p>
  
  <pre><code>Sub asdf()
  
  strFile = "C:\Users\bwall\Desktop\Excel Query Access Testing"
  
  Dim cn As Object
  Dim rs As Object
  Dim strSql As String
  Dim strConnection As String
  Dim AppPath As String
  Set cn = CreateObject("ADODB.Connection")
  AppPath = Application.ActiveWorkbook.Path
  
  
  strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
      "Data Source=" &amp; AppPath &amp; "\Masterlist_Current_copy.accdb;"
  Debug.Print strConnection
  strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components];"
  cn.Open strConnection
  Set rs = cn.Execute(strSql)
  MsgBox rs.Fields(0) &amp; " rows in MyTable"
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
  
  End Sub  
  </code></pre>
  
  <p>strConnection value = </p>
  
  <blockquote>
    <p>Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:\Users\bwall\Desktop\Excel Query Access
    Testing\Masterlist_Current_copy.accdb;</p>
  </blockquote>