Cannot Connect to MS Access Database on Network Drive with pyodbc When Database Opened by Another Process

Question

I am having trouble connecting to an MS Access database on our network drive using the python pyodbc library when the database is already opened by another process.

To reproduce the error, I first create a front end and back end Access Database with a table. I then open up the front end file in the MS Access Application so a .laccdb file is created for the backend. I then run the following code:

import pyodbc
pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=V:\Backend.accdb;')

V: is a shared network drive. Here is the error message I get:

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x85c Thread 0x2e0c DBC 0x92147c28 Jet'. (63) (SQLDriverConnect);
[HY000] [Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use.

I only get this error when the database is already open. There is no error when the script is the only process using the database.

Here is some other information:

I do not get this error when I repeat the test above on my local drive, so it seems to have something to do with the database being on the network drive.
I have the default open mode in MS Access to "Shared", so the database should not be opening in exclusive mode.
The error seems to have something to do with trying to connect via a Python script - for example, I can open the database front end / table using the MS Access Application, and then open up the back end file in MS Access Application simultaneously with no problem on the network drive.
I have read and write permission on the network directory. For example, I can create files and delete files in the network directory using Python scripts.
I have tried tweaking the ODBC Driver / Database Connection String, including opening in Read Only Mode, and Explicitly making sure exclusive mode is turned off. Neither of these options fix the problem.
My user has read access to the ODBC registry as suggested here.

System information:

MS Access 2019 64-bit
64-bit MS Access ODBC Driver
Python 3.9, using Anaconda environment / Anaconda prompt to run scripts
pyodbc version 4.0.32

Any help would be greatly appreciated.

Albert D. Kallal · Accepted Answer · 2022-04-23 02:02:35Z

the best bet?

The access data engine needs CREATE and DELETE rights to that folder.

When you open a database file, Access is a "file" based system. so, when the FIRST user (or process) opens that database file, then the JET (now ACE) data engine creates a locking file (ldb). This file is used to manage concurrent users (there is not server system or code or process running in that folder - it is a plane jane windows file open).

So, if that shared folder on the server (or whereever) does not allow user(s) to create (and delete) files in that folder, then no ldb locking and data collision file can be created, and thus the database will be read only.

When the last user (or process) that opened the file closes the data file, if no ohter users are active, then the ldb locking file is then deleted. Some do get away removing delete rights - and the ldb file can remain, but even that no ability to delete the locking (ldb file) often will mess this up.

So, what occures is you get read only, or often "exclusive" rights upon the open.

To fix, then that shared folder needs to have create, and delete rights active. And I seen in some cases, if the given particular user has been granted speical rights, then the ldb file "inherts" those same rights, and once again, all other users are either read only, or can't use the database.

So, to make a longer story short?

All users rights to that folder as a general rule need file create rights, and need file delete rights. And of course on your own personal computer this works, since for the most part you have full rights to quite much any folder and file(s) on your personal computer. So, you need to open up (relax) the security permissions on that shared folder - users need create file rights, and delete file rights.

Collectives™ on Stack Overflow

Cannot Connect to MS Access Database on Network Drive with pyodbc When Database Opened by Another Process

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related