Issue 37: Cannot access linked tables in Access through pyodbc
Status:  Complete
Owner: ----
Closed:  Mar 2009
Reported by heatherk...@gmail.com, Feb 25, 2009
What steps will reproduce the problem?
1. In an Access db, create a link to a table in a SQL Server db using a DSN.
2. Connect to the Access db with pyodbc using a DSN.
3. Execute a SQL string that selects data from the linked SQL Server table. 

What is the expected output? What do you see instead?
I expect the results of the query to get returned to the cursor, but
instead I get this error message (rocky2lemma is the name of the SQL Server
DSN): 

Traceback (most recent call last):
  File "C:\lemma_code\test_pyodbc.py", line 17, in <module>
    cursor.execute(sqlStr)
pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]
ODBC-
-connection to 'rocky2lemma' failed. (-2001) (SQLExecDirectW)")

What version of the product are you using? On what operating system?
python 2.5, pyodbc 2.1.3, Windows XP

Please provide any additional information below.
I can select data from any of the local Access tables using pyodbc, but I
can't select data from any of the linked SQL Server tables through pyodbc.
If I open the Access db, I can open the linked SQL Server tables and run
queries against them so there is no problem with the connection or
permissions. It's only when I try to select data from the linked tables
through pyodbc that I get the error. 

Sample Code:
import pyodbc

# coords is a system DSN to an Access db
connectStr = 'DSN=coords;Trusted_Connection=yes'
db = pyodbc.connect(connectStr)
cursor = db.cursor()

# PLOTS_ALL is a local Access table 
# The following code runs successfully
sqlStr = 'SELECT * FROM PLOTS_ALL'
cursor.execute(sqlStr)
print 'selected from plots_all'

# MODEL_REGION is a linked table in a SQL Server db
# The following code returns a pyodbc error before it gets to the print stmt
sqlStr = 'SELECT * FROM MODEL_REGION'
cursor.execute(sqlStr)
print 'selected from model_region'

cursor.close()
db.close()

Mar 18, 2009
Project Member #1 mkleehammer
It appears that the only way to do so is to check the 'Save password' checkbox when
creating the linked table.  I have successfully tested this.

I cannot find any way to supply the password in the connection string.  It seems to
be this way by design.

I *highly* recommend skipping the linked tables if it all possible.  If you can,
connect directly to SQL Server -- you clearly already have the logon info that you need.
Status: Done
Nov 21, 2010
Project Member #2 mkleehammer
(No comment was entered for this change.)
Status: Complete