lunedì 10 luglio 2017

Multiuser concurrent write access

UCanAccess uses the Jackcess API to manipulate the Access database. Jackcess performs read and write operations directly on the Access database file – .accdb or .mdb – without using the Access Database Engine. When a Jackcess process makes modifications to the database file it does so in isolation; it has no way of controlling (or even knowing) what other processes, including other Jackcess processes, might be doing to the file.

UCanAccess does support concurrent read/write operations from multiple threads of the same JVM (Java Virtual Machine) process. So, for example, multiple concurrent users are able to update the database if they connect to a Java web application or web service that uses UCanAccess to perform the updates from a single JVM process.

However, UCanAccess cannot support separate concurrent processes making simultaneous changes to the Access database. This includes:

  • Concurrent updates by several users of a stand-alone UCanAccess application that connects directly to the same Access database from different machines,
  • A UCanAccess application directly updating the database while another UCanAccess application on the same machine updates the same database from a different JVM process, e.g., a background process or web application that writes to the database,
  • A UCanAccess application updating the database while other processes perform updates with applications that use the Access Database Engine, e.g., Microsoft Access itself, or applications that use Microsoft’s own data-access technologies (Access ODBC, Access OLEDB, or ACE/Jet DAO).

UCanAccess does support multiple concurrent processes where:

  • all processes read the database but do not write to it, or
  • only one process writes to the database while all other processes just read it,

although in the latter case

  1. There could be a significant performance penalty because if UCanAccess detects that the Access database has been changed by another process then it must re-load the entire database to ensure that it has the most current information, and
  2. If a UCanAccess application updates a database while other users have the same database open in Microsoft Access itself then the Access users may have to close and re-open the database before they can see the changes made by the UCanAccess application.

1 commento:

  1. Hello Gord
    I really appreciate this document because it helps me understand why ms access database sometimes get damaged and need compact and repair and consequently deleting damaged rows for some tables.
    Our situation is that we have an ms access 2003 program that read/write to also ms access 2003 (.mdb) database where tables resides through linked table strategy. I developed java application that also read/write to the same database through UCanAccess 4.0.4. The problem, as I have clarified, is that the .mdb database become corrupted and need compact and repair every day. I believe that this due to the concurrent updates to the same .mdb file form stand-alone UcanAccess application and Access Database Engine.
    What I am going to do is to create a stand-alone .mdb dedicated to our UCanAccess application so that I guarantee that all write operations is done through single JVM. But, because of some business needs, I have to let the access program read (and only read) some tables through linked tables from my dedicated mdb. In your opinion, will this solve my problem ? Will the Access users have to close and re-open the database before they can see the changes made by the UCanAccess application as you said ?