title: Oppposite Workings of OLEDB/ODBC between Python and MS Access VBA
body: <p>Fellow more advanced programmers:</p>
  
  <p>Please forgive me if this seems like the tired Python 32-bit/64-bit ODBC/OLEDB Windows issue but I tried searching the forums and can't quite find the reason to my issue.</p>
  
  <p>Basically, I tried to connect a very simple Python script first via  ODBC using the pypyodbc module, and then second via OLEDB using the adodbapi module, both attempts to an MS Access 2010 .accdb database. However, for OLEDB I consistently get the <em>'Provider not found. It may not be properly installed'</em> error. And for ODBC I consistently receive the <em>'Data source name not found and no default driver specified'</em>. </p>
  
  <p>Digging deeper, something remarkable happened though. In Python, JET.OLEDB.4.0 works for .mdb files but not ACE.OLEDB.12.0 for .accdb files. Yet, the exact reverse happens when I ran an Access VBA ADODB connection!</p>
  
  <p>My environment includes:</p>
  
  <ul>
  <li>Python3.4 -32-bit</li>
  <li>pywin32-219 (installed with post-install)</li>
  <li>Microsoft Office 2010 64-bit</li>
  <li>Windows 7</li>
  </ul>
  
  <p>Yes, I downloaded and successfully installed the AccessDatabaseEngine_x64.exe. Yes, I pointed ODBC data sources to %Win%/SysWOW64. Yes, I see the Access driver and datasources for mdb and accdb in odbcad32.exe in aforementioned folder. Yes, I see the registry keys in regedit for Access (*mdb) and Access (*mdb, *accdb) data sources and Access driver. And yes, I both restarted and shut off/on my machine.</p>
  
  <p><strong>OLEDB</strong> <br>
  The following is my connection string that returns the <em>'Provider not found error...'</em>:</p>
  
  <pre><code>import adodbapi
  databasename = 'D:\directorypath\DatabaseName.accdb'  
  constr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s'  % databasename 
  db = adodbapi.connect(constr)
  </code></pre>
  
  <p>Remarkably though, the following connection string works perfectly but of course only with .mdb files:</p>
  
  <pre><code>import adodbapi
  databasename = 'D:\otherdirectorypath\OtherDatabaseName.mdb' 
  constr = 'Provider=Microsoft.JET.OLEDB.4.0;Data Source=%s'  % databasename 
  db = adodbapi.connect(constr)
  </code></pre>
  
  <p><br>
  <strong>ODBC</strong> <br>
  The following is my connection string that returns the <em>'Data source name not found...</em>':</p>
  
  <pre><code>import pypyodbc
  databasename = 'D:\directorypath\DatabaseName.accdb'
  constr = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;'  % databasename
  db = pypyodbc.connect(constr)
  </code></pre>
  
  <p>Like above, the following works ideally but only for .mdb files:</p>
  
  <pre><code>import pypyodbc
  databasename = 'D:\otherdirectorypath\OtherDatabaseName.mdb'
  constr = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;'  % databasename
  db = pypyodbc.connect(constr)
  </code></pre>
  
  <p><br>
  <strong>Access VBA</strong> <br>
  Interestingly, the EXACT reverse happens in an Access VBA module (of course with the ActiveX Data Object library reference) using the same connections strings both Driver and Provider.</p>
  
  <p>Code works perfectly for both .mdb and .accdb files:</p>
  
  <pre><code>Dim constr As String
  Dim CN As ADODB.Connection
  Dim RS As ADODB.Recordset
  
  constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\databasedirectory\DatabaseName.accdb;Persist Security Info=False"
  
  ' OR constr = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\databasedirectory\DatabaseName.accdb;Persist Security Info=False"
  
  Set CN = New ADODB.Connection
  CN.Open (constr)
  </code></pre>
  
  <p>Code returns error - <em>'Provider not found'</em> or <em>'Specified driver could not be loaded'</em>:</p>
  
  <pre><code>Dim constr As String
  Dim CN As ADODB.Connection
  Dim RS As ADODB.Recordset
  
  constr = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=D:\otherdatabasedirectory\OtherDatabaseName.mdb;Persist Security Info=False"
  
  ' OR constr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\otherdatabasedirectory\OtherDatabaseName.mdb;Persist Security Info=False"
  
  Set CN = New ADODB.Connection
  CN.Open (constr)
  </code></pre>
  
  <p><br>
  ACE.OLEDB.12.0 VS ACE.OLEDB.14.0 <br>
  Finally, still in Access VBA, only the Provider: Microsoft.ACE.OLEDB.12.0 works which I thought corresponded to MS Access 2007. However, the Provider: Microsoft.ACE.OLEDB.14.0 does not work for my installed MS Access 2010. Any reasons for this?
  <br></p>
  
  <p><strong>Conclusion</strong><br>
  I understand ActiveX library is a different technology than Python's module library and Microsoft does not allow simultaneous use of components in x32-bit and x64-bit but why can't I get my intended result. I thought about the AccessDatabaseEngine_x64.exe /passive but I hear weird Office results going that route. Do I need to install Python3.4 for 64-bit? Please help or suggest! Thanks.</p>