Can I work with both local and ODBC linked tables in an Access database from Python?

Question

How can pypyodbc connect to linked tables in the .accdb database? Is this possible at all, or is this a limitation of pyodbc?

I need to get data from an MS Acess .accdb database into Python. This works perfectly and I can use pypyodbc 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 pypyodbc complains that it cannot connect to the SQL server.

test.accdb contains two tables: Test (local table) and cidb_ain (linked SQL table)

The following Python 3 code is my attempt to access the data:

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)

Output:

(1, 'eins', 1)
(2, 'zwei', 2)
(3, 'drei', 3)
----
Traceback (most recent call last):
  File "test_02_accdb.py", line 14, in <module>
    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.")

The error message roughly translates to "ODBC connection to 'SQL Server Native Client 11.0SQLHOST' failed".

I cannot access the SQL Server through the .accdb database with pypyodbc, but querying the cidb_ain table from within MS Access is possible. Furthermore, I can connect to the SQL Server directly:

cnxn = pyodbc.connect(driver='SQL Server Native Client 11.0',
                      server='SQLHOST',
                      trusted_connection='yes',
                      database='stuffdb')

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 pypyodbc. (The way driver name and host name are mangled into 'SQL Server Native Client 11.0SQLHOST' in the error message seems somewhat suspicious, too.)

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...

Answer 1

Update:

It turns out that the solution to this problem is as simple as setting pyodbc.pooling = False before establishing the connection to the Access database:

import pyodbc
# ... also works with `import pypyodbc as pyodbc`, too
pyodbc.pooling = False  # this prevents the error
cnxn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ= ... ")

---

(previous answer)

It appears that neither pypyodbc nor pyodbc can read a SQL Server linked table from an Access database. However,  System.Data.Odbc in .NET can do it so IronPython can, too.

To verify, I created a table named [Foods] in SQL Server

id  guestId  food
--  -------  ----
 1        1  pie
 2        2  soup

I created an ODBC linked table named [dbo_Foods] in Access which pointed to that table on SQL Server.

I also created a local Access table named [Guests] ...

id  firstName
--  ---------
 1  Gord
 2  Jenn

... and a saved Access query named [qryGuestPreferences] ...

SELECT Guests.firstName, dbo_Foods.food
FROM Guests INNER JOIN dbo_Foods ON Guests.id = dbo_Foods.guestId;

Running the following script in IronPython ...

import clr
import System
clr.AddReference("System.Data")
from System.Data.Odbc import OdbcConnection, OdbcCommand

connectString = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\Public\Database1.accdb;"
)
conn = OdbcConnection(connectString)
conn.Open()

query = """\
SELECT firstName, food 
FROM qryGuestPreferences
"""
cmd = OdbcCommand(query, conn)
rdr = cmd.ExecuteReader()
while rdr.Read():
    print("{0} likes {1}.".format(rdr["firstName"], rdr["food"]))
conn.Close()

... returns

Gord likes pie.
Jenn likes soup.

Answer 2

First, MS Access is a unique type of database application that is somewhat different than other RDMS's (e.g., SQLite, MySQL, PostgreSQL, Oracle, DB2) as it ships with both a default back-end Jet/ACE SQL Relational Engine (which by the way is not an Access-restricted component but a general Microsoft technology) and a front-end GUI interface and report generator. In essence, Access is a collection of objects.

Linked tables are somewhat a feature of the front-end side of MS Access used to replace the default Jet/ACE database (i.e., local tables) for another backend database, specifically for you SQL Server. Moreover, linked tables are ODBC/OLEDB connections themselves! You had to have used a DSN, Driver, or Provider to even establish and create linked tables in the MS Access file.

Hence, any external client, here being your Python script, that connects to the MS Access database [driver='Microsoft Access Driver (*.mdb, *.accdb)] is actually connecting to the backend Jet/ACE database. Client/script never interacts with frontend objects. In your error Python reads the ODBC connection of the linked table and since the SQL Server Driver/Provider [SQL Server Native Client 11.0SQLHOST] is never called in script, the script fails.

Altogether, to resolve your situation you must connect Python directly to the SQL Server database (and not use MS Access as a medium) to connect to any local tables there, here being cidb_ain. Simply use the connection string of the Access linked table:

#(USING DSN)
db = pypyodbc.connect('DSN=dsn name;')

cur = db.cursor()
cur.execute("SELECT * FROM dbo.cidb_ain")

for row in cur.fetchall()
  print(row)

cur.close()
db.close()


# (USING DRIVER)
constr = 'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=servername;' \
         'DATABASE=database name;UID=username;PWD=password'
db = pypyodbc.connect(constr)

cur = db.cursor()
cur.execute("SELECT * FROM dbo.cidb_ain")

for row in cur.fetchall()
  print(row)

cur.close()
db.close()