| Issue 37: | Cannot access linked tables in Access through pyodbc | |
| 1 person starred this issue and may be notified of changes. | Back to list |
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
Status:
Done
Nov 21, 2010
(No comment was entered for this change.)
Status:
Complete
|