search

gordthompson / msaccessdb

Python package to create a new (empty) Access database.
Apache License 2.0
10 stars 3 forks source link

General error Unable to open registry key Temporary (volatile) Ace DSN #4

Closed abubelinha closed 2 months ago

abubelinha commented 2 months ago

I created a function to generate the files:

def createAccessFile(db_file = r'C:\path\to\new.accdb'):
    """ Create blank database file with msaccessdb
        - <https://stackoverflow.com/questions/39995802/>
        - <https://github.com/gordthompson/msaccessdb/>
        """
    import msaccessdb
    import pyodbc
    print("msaccessdb: ",msaccessdb.__version__)
    print("pyodbc: ",pyodbc.version)
    print(db_file)
    msaccessdb.create(db_file)
    cnxn_str = (
        'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
        'DBQ={}'.format(db_file)
    )
    cnxn = pyodbc.connect(cnxn_str)
    return cnxn

createAccessFile("testfile01.mdb")
createAccessFile("testfile02.accdb")

When I run the script, files are created (either .mdb or .accdb, depending on which line is first). 2nd one is not created because function exits:

I am using Python 3.8 on Windows 7 Home Premium SP1

gordthompson commented 2 months ago

That is an issue with the Access ODBC driver's handling of unqualified file names. Instead of

cnxn_str = (
    'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    'DBQ={}'.format(db_file)
)

use

cnxn_str = (
    'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    'DBQ=.\\{}'.format(db_file)
)
abubelinha commented 2 months ago

Thanks for the quick answer @gordthompson That changed the error to a new one (again with both .mdb and .accdb, whatever it goes first):

With the original syntax, the error ended like this: Not a valid file name. (-1044) With your new syntax, now the error ends like this: Could not find file '(unknown)'. (-1811)

But in both cases, the first file of my two lines (either .mdb or .accdb) is being created.

 msaccessdb:  1.0.0
pyodbc:  4.0.32
testfile02.accdb
Traceback (most recent call last):
  File "C:\scripts\dbfunctions.py", line 515, in createAccessFile
    cnxn = pyodbc.connect(cnxn_str)
pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x1da8 Thread 0xf18 DBC 0xe03d9f8                                                              Jet'. (63) (SQLDriverConnect); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x1da8 Thread 0xf18 DBC 0xe03d9f8                                                              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 0x1da8 Thread 0xf18 DBC 0xe03d9f8                                                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x1da8 Thread 0xf18 DBC 0xe03d9f8                                                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'. (-1811)")

One odd thing I noticed. The yellow locker over the new file icons is gone, but not due to the new syntax you propose. I changed the code to the original one and no more lockers either.

I wonder why that locker appeared in my first tries but it seems not to be related to the script origin of these files because it is not happening now. I still did not delete them. If I right click and see file details, the attributes of those "locked" files are "AI", whereas "normal" files have "A" attributes. Any ideas?

gordthompson commented 2 months ago

What happens if you go back to the original 'DBQ={}'.format(db_file) in your connection string and use a fully-qualified path, e.g.,

createAccessFile(r"C:\Users\Gord\PycharmProjects\pyodbc_demo\testfile01.mdb")
abubelinha commented 2 months ago

That seems to work well. No errors. Also this relative path syntax works with no errors: cnmdb = createAccessFile(r".\testfile03.mdb")

I still need to make more tests regarding the "lockers". I haven't seen any more yellow lockers yet, but now I saw many blue lockers in additional .laccdb / .lmdb files which were being created, like when the database file is kept open with Access

I had taken your stackoverflow example lines, so I guess the not closed pyodbc connection was causing that and I added a return after this line: msaccessdb.create(db_file)

I played with many calls to the function testing different paths, and I kept seeing some of these blue-locked .lmdb / .laccdb So for a few minutes I had the feeling that the last file created with createAccessFile() was being left open. But probably not: I guess some locks were just remains of previous script runs ... and they did not go away until I re-created exactly the same file name in the same path without the pyodbc call. Does this make sense to you?

I'll play with it more in a few weeks and report if I see something wrong, but hopefully not. Thanks a lot for your help

gordthompson commented 2 months ago

Orphaned lock files (.ldb and .laccdb) can happen when a program crashes and the connection is not closed cleanly. They usually do not cause problems themselves and get cleaned up later, as you have seen.