How to share an Access database to different pc's for a Java desktop application?

Question

I am creating a desktop application in JAVA (RCP plugin) and I am using MS Access as database for my application.

I kept database file on some shared location but whenever I try to open it from different machine it throws exception saying database is already locked by someone else. Please let me know how to resolve this issue.

Is there any way to share an MS Access database file between different machine using JDBC connectivity?

Answer 1

No. This is like giving your car to several people and all of them want to drive it at the same time: It won't work for many reasons.

Workarounds:

1. Use a real database like MySQL or H2.

2. Create a Java server which talks to the Access database. Let all the other programs talk to this server.

Note: You can't use JDBC in the "other programs" when you use approach #2. You'll have to write your own protocol.

Answer 2

I believe there are some settings you can do for multi user option in MS Access. Please follow the following steps. Open MS Access (2016), navigate to File > Options > Client Settings > scroll to Advanced:-

Default open mode: Shared; Default record locking: No locks;

Answer 3

If your application does in fact use the JDBC-ODBC Bridge (as suggested by the jdbc-odbc tag on your question) and the Microsoft Access ODBC Driver then yes, a modest number of concurrent users should be able to use your Java application to work on a shared Access database.

The most common cause of the behaviour described in your question is insufficient permissions on the folder in which the Access database file (.accdb or .mdb) resides. Access uses a "lock file" (.laccdb or .ldb) to manage multiple concurrent users so all users must have "change" permissions on the folder so they can create and/or update the lock file.

For example, if your shared Access database resides in server share that is mapped to the Z: drive for all users and your Java code looks something like this ...

// (Note: Only for Java 7 or earlier.)
String url = "jdbc:odbc:" +
        "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
        "DBQ=Z:/test/myTest.mdb;";
try (Connection conn = DriverManager.getConnection(url)) {
    // do useful stuff here
} catch (Exception e) {
    e.printStackTrace(System.err);
}

... then you need to ensure that all users have "change" permissions on the "Z:\test" folder.

For a more detailed explanation see my other answer here.