Issue 312: pyodbc connect connect to MS Access 2007 file
Status:  New
Owner: ----
Reported by isaac...@gmail.com, Mar 15, 2013
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
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?
Apr 30, 2013
#3 jtas...@gmail.com
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
#4 isaac...@gmail.com
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
#5 isaac...@gmail.com
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
#7 jtas...@gmail.com
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
#8 bjorn.ma...@operationsresearchgroup.com
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
#9 isaac...@gmail.com
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