0

I am giving maintenance on an old, decentralized, and local application that each of our Clients uses, with each app having its own independent .mdb (MS Access) file as a database. We are developing a new, centralized SpringBoot Web application that uses MySQL as a database. The problem is that we can't just throw away the data on the already existing Access databases, therefore, one of the pre-requisites for the new application is to add a function to migrate all the old data from those .mdb files into the new database.
In detail, using an HTTP call, our client should be able to send a .mdb file through a SpringBoot controller. The Server should then read the tables and its contents, validate a few pieces of information, and insert the data read on the new MySQL database.

A few notes:
The most recent version of the database structure -the MySQL- is, of course, already known, but the structures of the older Access databases may vary, as not all of the Clients have the most up to date version of the old App/MS Access. That's why, if possible, the reading process should be dinamic and independent of a pre-determined structure. The good news is that, in general, the newer versions only add new tables and fields (no deletions nor editions), therefore when migrating a very old .mdb, a few tables and fields could be missing (shouldn't be a problem, except it's 'NOT NULL'), but at least there will be no "new", unknown or extra field.

The HTTP Controller, the validation, and insertion of the data are OK, but I couldn't find how to programmatically and dynamically read the .mdb file. Any help would be appreciated. Thanks in advance!

Milan
  • 1,743
  • 2
  • 13
  • 36
Guilherme Barboza
  • 567
  • 5
  • 22
  • You should be able to create odbc connections to the mdb file and use that from Java. Doing this on Windows is probably the easiest approach. – Marged Jul 25 '20 at 22:54
  • A different approach is depicted there: https://stackoverflow.com/questions/6251308/java-connect-to-ms-access-database-mdb-or-mde – Marged Jul 25 '20 at 22:56
  • 2
    Current Java versions doesn't support the old JDBC-ODBC bridge anymore, which is good, because it had several flaws, like problems with MEMO fields, run only on Windows with a 32-bit JRE, etc. The recommended way is the [UCanAccess](http://ucanaccess.sourceforge.net/site.html) JDBC driver. This is pure Java, OS independend and runs on Linux, too. – vanje Jul 26 '20 at 00:37
  • You may find [this blog post](https://medium.com/@tobias.strg/accessing-ms-access-with-hibernate-5-and-spring-boot-1b58f26ebb7d) to be of interest. – Gord Thompson Jul 26 '20 at 18:01

0 Answers0