1

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.

2
  • Check the properties of the ODBC connection and verify that "exclusive" is off.
    – Andrea B.
    Apr 12, 2023 at 12:48
  • Thanks, I have checked and it is not set to 'exclusive'.
    – Matt B
    Apr 12, 2023 at 14:58

1 Answer 1

0

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.

3
  • Thanks for your answer. Yes, concurrent access to the Access database is definitely possible. There is no problem whatsoever with several users using front-ends with linked tables to the same back-end. As I understand it, those are just ODBC connections as well, which leaves me mystified why the same concurrent access doesn't work when it is an outside program establishing the same kind of connection.
    – Matt B
    Apr 12, 2023 at 14:58
  • @MattB Unfortunately it might even just be a localized issue to QGIS and you may need to work with their support to solve the issue. If the concurrency involved is just multiple ODBC connections, then to your point, it seems like the issue isn't the ODBC connection(s) or concurrency. It may be a QGIS specific issue. If you have a SQL Server instance laying around, you can verify the ODBC connection is working by creating a Linked Server object with it.
    – J.D.
    Apr 12, 2023 at 15:05
  • I actually have exactly the same problem when connecting to the database with R (via the 'RODBC' library). Here, too, the ODBC connection works only if nobody else was using the database while the connection was opened. So I do not think it is a QGIS issue, but rather Access behaving idiosyncratically in allowing multiple connections if they come from other Access clients, but not if they come from different software. I may try to install SQL server and connect that in the future.
    – Matt B
    Apr 12, 2023 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.