Cannot access linked tables in Access through pyodbc

[GoogleCodeExporter]

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()

Original issue reported on code.google.com by heatherk...@gmail.com on 25 Feb 2009 at 5:27

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 18 Mar 2009 at 3:43

• Changed state: Done

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:44

• Changed state: Complete