QGIS ODBC connection to shared Access database only working when Access closed at QGIS start-up

Question

I am working with an MS Access database which is located on a shared folder and regularly used by other users at the same time (the database is split into several user front-ends and a back-end). I have established an ODBC connection (64 bit) that links a front-end of the database to a layer in QGIS (v. 3.30 "s'Hertogenbosch").

This connection works fine in testing, so I am not asking how to establish such a connection (which are the only questions on QGIS and Access I could find in a Stack Exchange search).

The problem I have is that whenever the Access database is already opened by another user (or by myself) at the time that I open my QGIS project, the ODBC layer will not load. It will show up as a missing layer in the "Handle Unavailable Layers" dialogue.

Whenever I open the project with the Access database closed, it works without issue. I have tried connecting to the back-end directly, rather than to a front-end with a linked table, but the same issue arises there.

As far as I understand the connection, based on my observations/tests, the data is fetched when the connection is established and not updated regularly. I cannot write data from the QGIS attribute table to Access (nor do I want to), and data I enter into Access after the QGIS project has been opened does not show up in the attribute table. I see therefore no reason why the table shouldn't load, even if someone else is using it in Access at the same time.

Is there anything I can do about this short of calling all users and asking them to close Access when I open QGIS?

You are welcome to tell me that I should use a better database solution than Access and a better storage solution than shared folders, but this is the infrastructure this project used when I joined, and there is nothing I can do about it.

Answer 1

You are welcome to tell me that I should use a better database solution than Access and a better storage solution than shared folders, but this is the infrastructure this project used when I joined, and there is nothing I can do about it.

Well, if you're setting up auxiliary technology such as QGIS, then you do have some control over the infrastructure. If you're able to setup an ODBC connection to the MS Access database, then you can also setup the same kind of connection from another modern database system. You can then utilize features of said modern database system to replicate the data and / or access it in real-time.

Microsoft actually recommends using a modern database system like SQL Server to maximize flexibility of sharing an Access database to multiple users. And QGIS supports modern database systems like SQL Server.

As far as your issue with the way your project is currently architected, it'll probably be hard to debug without more information. The only guess I could provide is verifying that the database itself isn't locked while there's concurrent usage of it. That will depend on how the MS Access database was implemented. At one point, concurrent access wasn't allowed with MS Access databases, but I believe that's a thing of the past.