title: Can I work with both local and ODBC linked tables in an Access database from Python?
body: <p>How can pypyodbc connect to linked tables in the .accdb database? Is this possible at all, or is this a limitation of pyodbc?</p>
  
  <p>I need to get data from an MS Acess .accdb database into Python. This works perfectly and I can use <code>pypyodbc</code> to access tables and queries defined inside the .accdb Database. However, the database also has tables linked to an external SQL Server. When accessing such linked tables <code>pypyodbc</code> complains that it cannot connect to the SQL server.</p>
  
  <p><code>test.accdb</code> contains two tables: <code>Test</code> (local table) and <code>cidb_ain</code> (linked SQL table)</p>
  
  <p>The following Python 3 code is my attempt to access the data:</p>
  
  <pre><code>import pypyodbc as pyodbc
  
  cnxn = pyodbc.connect(driver='Microsoft Access Driver (*.mdb, *.accdb)',
                        dbq='test.accdb',
                        readonly=True)
  
  cursor = cnxn.cursor()
  
  # access to the local table works
  for row in cursor.execute("select * from Test"):
      print(row)
  
  print('----')
  
  # access to the linked table fails
  for row in cursor.execute("select * from cidb_ain"):
      print(row)
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>(1, 'eins', 1)
  (2, 'zwei', 2)
  (3, 'drei', 3)
  ----
  Traceback (most recent call last):
    File "test_02_accdb.py", line 14, in &lt;module&gt;
      for row in cursor.execute("select * from cidb_ain"):
    File "C:\software\installed\miniconda3\lib\site-packages\pypyodbc.py", line 1605, in execute
      self.execdirect(query_string)
    File "C:\software\installed\miniconda3\lib\site-packages\pypyodbc.py", line 1631, in execdirect
      check_success(self, ret)
    File "C:\software\installed\miniconda3\lib\site-packages\pypyodbc.py", line 986, in check_success
      ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
    File "C:\software\installed\miniconda3\lib\site-packages\pypyodbc.py", line 964, in ctrl_err
      raise Error(state,err_text)
  pypyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC-Treiber für Microsoft Access] ODBC-Verbindung zu 'SQL Server Native Client 11.0SQLHOST' fehlgeschlagen.")
  </code></pre>
  
  <p>The error message roughly translates to "ODBC connection to 'SQL Server Native Client 11.0SQLHOST' failed".</p>
  
  <p>I cannot access the SQL Server through the .accdb database with pypyodbc, but querying the <code>cidb_ain</code> table from within MS Access is possible. Furthermore, I can connect to the SQL Server directly:</p>
  
  <pre><code>cnxn = pyodbc.connect(driver='SQL Server Native Client 11.0',
                        server='SQLHOST',
                        trusted_connection='yes',
                        database='stuffdb')
  </code></pre>
  
  <p>Considering that (1) MS Access (and Matlab too) can use the information contained in the .accdb file to query the linked tables, and (2) the SQL Server is accessible, I assume the problem is related to <code>pypyodbc</code>. (The way driver name and host name are mangled into <code>'SQL Server Native Client 11.0SQLHOST'</code> in the error message seems somewhat suspicious, too.)</p>
  
  <p>I have no previous experience with Access, so please be patient and let me know if I omitted important information that seemed unnecessary to me...</p>