1

The question is pretty self-explanatory, but below is some more info about the situation:

I am building a Java program that will be replacing a program that consists of an Excel user interface with an Access database. The Excel program connects to the Access database and communicates with VBA. But, so far there has only ever been one user at a time. Now that the program is due to expand, we need many users to be able to write to any table at the same time.

Access allows multiple users to connect at once, of course. This is not possible in HSQLDB, which is what prompted the question. Obviously, this is better accomplished with a server, but the plan is to build the program using the current database and then accomplish the transition to a server later.

Thanks in advance

corbfon
  • 135
  • 1
  • 13
  • I am reaching back in my archives here, so somebody with more recent experiences would be a better source, but I could have sworn that in previous systems I have designed that I was only able to have one connected to the Access DB at a time. – Rabbit Guy May 12 '16 at 20:07
  • *Can multiple users connect to a Microsoft Access database at the same time using Java?* **Yes!** – Parfait May 13 '16 at 01:18
  • Make sure you are **not** opening it for exclusive access (https://www.connectionstrings.com/access/ is a good reason). Also: the processes (or users) that are accessing the file will require **write access** to the file in order for multiple users to be connected at once. Otherwise you'll run into arcane errors when the lock file is created. – C. White May 13 '16 at 01:53

2 Answers2

1

In order to support multiple concurrent users (processes) writing to an Access database you must use the Access Database Engine. The options to do that from a Java application are:

  1. Use Java's own JDBC-ODBC Bridge and the Access ODBC driver. (Note that the JDBC-ODBC Bridge was removed from Java 8.)

  2. Use a third-party JDBC-ODBC Bridge and the Access ODBC driver.

  3. Use a third-party JDBC driver that works with the Access Database Engine (if such a thing exists).

Note especially that the UCanAccess JDBC driver does not use the Access Database Engine and therefore does not support multiple concurrent users (processes) writing to the Access database.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • What about multiple (2) processes writing to the database, but not simultaneously? Like in while migrating from an old Visual Basic app using a Jet database the same user on same pc will run both the old VB app and new Java app simultaneously, but won't do simultaneous updates. Will immediatelyReleaseResources=true;openExclusive=true do any good? – jon martin solaas Jan 20 '20 at 22:37
0

You can do it. I have a similar application that I use. In version 1.8 of Java, the ODBC bridge was removed, so you'll have to look into using a separate library to connect, assuming you are using 1.8 or above. For me, it's way slower, but it does work. check out Removal of JDBC ODBC bridge in java 8 I use "Ucanaccess" for my program, which is one of the suggestions in that question.

Community
  • 1
  • 1
Mark_Eng
  • 443
  • 1
  • 4
  • 12