Access to MS Access hosted on an Ubuntu server with Java in Windows-based VPS?

Question

Hi, I have this little big problem. I have a VB6 desktop application that connects to an MS Access database hosted on a local Ubuntu server machine, which is shared by the local network as an SMB share, and I have a Tomcat web application in Windows based VPS this

Requirements

• Read the information from the MS Access database and show it in webapp (at the URL and almost in real time).

• Update the MS Access database with information obtained through the website.

Data

• 20Mb <db size
• Together with 15 ~ 25 users.
• Constant local update and request.

The file size is small, because it truncates as the tool grows (> 100 MB) Now I know that the architecture is not the best and that MS Access is at its borders, the transition to full Java and MySQL is on the way, but it will take a lot of time. ... in the meantime, I need a way to implement this feature, here are my options.

---

Option 1

Access db on ubuntu server via VPN directly from webapp.

vs

• Is it possible?.
• Slow connection.
• It can block db MORE often, as it happens quite often locally.

Option 2

Try running the local web server on an Ubuntu server that provides db as a REST API, so updates will be handled by the local webapp.

Minuses:

• It is difficult to use MS Access in a UNIX environment, looking at unixODBC and FreeTDS , but so far I have not been able to use it.

• Well, recording the entire application and protecting the server.

Option 3

Any suggestions?

Thanks if you read this, any help is really appreciated.

Answer 1

If I have not missed something in my description, I think that you can mislead the differences between the API and the library. Basically, ODBC is an API that is implemented as a library and is commonly used on computers running Windows through additional data access libraries such as ADO and ADO.NET. I mention this because you referred to unixODBC as a solution. This would not be a complete solution, as there would be more to it than just an API.

Simply put, a database file created using Microsoft Access is a flat .MDB file database (well, it’s a bit larger, but from the point of view of treating it as a database, that’s all that matters here). If you know how the .MDB file structure works, you can write your own library that reads / writes to it. Of course, this is not trivial, but on Windows platforms it is provided by Microsoft using the libraries included in the OS. This is also called the JET driver and database. JET is a database format that implements a .MDB file and is used by Access and other applications using the appropriate JET drivers.

So, to find an equivalent option for platforms other than Windows, you need some kind of library that knows how to directly read / write to the .MDB file directly. If you are trying to use the .MDB file from the Access application at the same time, you need to make sure that the library of your choice supports simultaneous user access to the database.

In a quick search, there are some solutions that I can find. The first shows that this may have some functional limitations. The second is a commercial product.

MDB Tools

Easysoft Jet / Access Driver