| Issue 312: | pyodbc connect connect to MS Access 2007 file | |
| 1 person starred this issue and may be notified of changes. | Back to list |
What steps will reproduce the problem?
1. import pyodbc;
2. conn=pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=test_db.mdb;")
3. Error output:
Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x103c Thread 0x15e8 DBC 0x1bb9f5c Jet'. (63) (SQLDriverConnectW); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x103c Thread 0x15e8 DBC 0x1bb9f5c Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x103c Thread 0x15e8 DBC 0x1bb9f5c Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x103c Thread 0x15e8 DBC 0x1bb9f5c Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044)")
What is the expected output? What do you see instead?
What version of the product are you using? On what operating system?
tried pyodbc 3.0.6 and 2.1.8 under python 2.7.3
Mar 19, 2013
#2
isaac...@gmail.com
Apr 30, 2013
Try this connection string:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=Admin;
Pwd=;
Replace "C:\mydatabase.accdb" with the full path to your access database. Does that work?
May 8, 2013
tried this, still doesn't work:- (when I used RODBC (under R ), access 2007 was connected fine).
conn=pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};D---------------------------------------------------------------------------
Error Traceback (most recent call last)
<ipython-input-7-33cd28d6a40b> in <module>()
----> 1 conn=pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:\test_db.accdb;Uid=Admin;Pwd=;")
Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x173c Thread 0x8f0 DBC 0x177b014
Jet'. (63) (SQLDriverConnectW); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x173c Thread 0x8f0 DBC 0x177b014
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'. (-1811); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile)
Ace DSN for process 0x173c Thread 0x8f0 DBC 0x177b014
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x173c Thread 0x8f0 DBC 0x177b014
Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'. (-1811)")
May 8, 2013
guess what, using an extra "\", this is working now:
- couldn't believe that's the issue!
conn=pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:\\test_db.accdb;")
May 8, 2013
Great, I was going to suggest that next! For anyone else who sees this, the MSDN reference for connecting to an Access database is this page: http://msdn.microsoft.com/en-us/library/windows/desktop/ms713896.aspx And as you can see, it uses two backslashes...
May 15, 2013
Hello,
For my own needs I started writing a pyodbclib, that contains connection strings to make it easier for the users. Anyway, this code works fine for me:
import pyodbc
def access2007(db, sqlstring='select * from table', user= 'admin', password=""):
"""Create function for connecting to Microsoft Access using ODBC database connection."""
odbc_conn_str = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=%s;Uid=%s;Pwd=%s;' % (db, user, password)
conn = pyodbc.connect(odbc_conn_str)
cur = conn.cursor()
cur.execute(sqlstring)
data = list(cur)
conn.close()
return data
def excel2007(db, sqlstring='select * from table', user= 'admin', password=""):
"""SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]".
I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
"""
odbc_conn_str = 'Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%s;' % (db)
conn = pyodbc.connect("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Dbq="+db, autocommit=True)
cur = conn.cursor()
cur.execute(sqlstring)
data = list(cur)
conn.close()
return data
if __name__ == '__main__':
"""Now I can access Excel, Access, etc. using a simple function call:
>>> import pyodbc_connection as pc
>>> db = r'C:\pyodbc_access2007_sample.accdb'
>>> sql="select * from [Customer Orders]"
>>> data = ps.access2007(db,sql)
>>> print len(data)
129606
"""
# Access DB example
db1 = r'C:\Users\bmadsen\ownCloud\PythonNoteBook\pyodbc_access2007_sample.accdb'
sql1="select * from [Customer Orders]" ## tables: 'Customer Orders', 'Physical Stoks','Prodplans'
data1 = access2007(db1,sql1)
print len(data1)
# Excel example
db2 = r'C:\Users\bmadsen\ownCloud\PythonNoteBook\pyodbc_excel2007_example.xlsx'
sql2='SELECT * FROM [Sheet1$]'
data2 = excel2007(db2,sql2)
print len(data2)
May 20, 2013
hi Bjorn, thanks for sharing the codes, I think these are useful. I am just curious if we should include these conveniences into pyodbc itself? Best regards, Isaac |