pyodbc connect connect to MS Access 2007 file

[GoogleCodeExporter]

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

Original issue reported on code.google.com by isaac...@gmail.com on 15 Mar 2013 at 8:41

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

using RODBC in R, access2007 file can be connected properly:
: odbcConnectAccess2007() function 

so I guess there might be some special option for access 2007 file in odbc 
connection?

Original comment by isaac...@gmail.com on 19 Mar 2013 at 9:15

[GoogleCodeExporter]

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?

Original comment by jtas...@gmail.com on 30 Apr 2013 at 7:19

[GoogleCodeExporter]

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

Original comment by isaac...@gmail.com on 8 May 2013 at 3:14

[GoogleCodeExporter]

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;")

Original comment by isaac...@gmail.com on 8 May 2013 at 5:47

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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

Original comment by jtas...@gmail.com on 8 May 2013 at 6:43

[GoogleCodeExporter]

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)

Original comment by bjorn.ma...@operationsresearchgroup.com on 15 May 2013 at 8:42

[GoogleCodeExporter]

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

Original comment by isaac...@gmail.com on 20 May 2013 at 2:45