Upgrading from MS Access

Question

My company uses an old MS Access program to generate xml files. There are two users who perform maintenance/updates on this system, and a handful of others who use it to generate files.

The program does the following:

1. User enters info in a couple "search" fields on an Access form.

2. Program pulls data from external databases using ODBC connections and mySQL OR pulls data from an xml file instead (so that we can manually enter data if it isn't in a database).

3. Pulls data from tables internal to the access database.

4. Uses VBA subroutines and functions to do calculations, error handling, etc. Uses collections.

5. Complies final data, writes to and saves a new xml file (currently just writes it as a text file, but I realize there are better ways).

6. Displays a pop-up showing some basic information about the new file.

We lock down the "user" versions of this program and have a batch file that creates a temporary copy each time the user opens it. Nobody ever writes to the external databases. The users never write to anything, only read. Admins write to the internal access tables manually.

As far as scale, I can't imagine us having more than 3 admins, and maybe ~30 users.

While there's nothing particularly "wrong" with this program, I'd like to know, if we wanted to use some newer software, what we should move to. Currently, I am only familiar with VBA, but I'd love to learn a "real" programming language (particularly interested in python, but would try most anything), so that isn't necessarily an issue. MS Access can be kinda clunky sometimes, and it just looks old, so I'd love to know if there's something smoother and shinier that would make sense for our uses, and it would be fun for me to have a little pet project and learn new languages/software.

Any suggestions? Thanks!

Answer 1

If you want a centralized server with web clients, I would use Java.

Backend

You would use JDBC rather than ODBC. Many JDBC drivers exist for MySQL and many other databases.

Java has excellent libraries for reading and writing both XML and JSON.

Java has excellent libraries to make multithreaded concurrent programming easier.

Java has so many other excellent libraries as well, to handle most any practical problem. For example, the industry-leading java.time classes for date-time handling. And Java is fast, reliable, secure, and well-documented.

Database

I would use Postgres instead of MySQL for many reasons, mostly for the rock-solid enterprise-quality reliability.

This is just my personal opinion. From your description it sounds like either Postgres or MySQL would work well for you. You might also want to consider using the H2 database written in pure Java.

User-interface

I would use the Vaadin Framework for the user-interface. Vaadin uses pure Java to write and execute your business logic as well as your user-interface (layouts, fields, labels, buttons, charts, data-grids, and do on) all running on the server. Vaadin automatically renders that Java-defined UI into the user’s web browser using Web Standards technologies (HTML, CSS, DOM, JavaScript, WebSocket, Push). So all Java on the server side, but no Java running on the client, yet you needn't learn about all that HTML web stuff as Vaadin takes care of that.

Vaadin is designed for business-oriented app development.

Answer 2

I would suggest taking a look a Pandas for the database accessing and processing, (the "Back End"), and for a more current look putting a web "front end" on using Django.

Pandas has a lot of tools for accessing data from various sources including databases and would give you a lot of choice as to output formats.

Django is one of the fasted growing web application frameworks aimed at rapid development.

Both are python based, free, gratis & open sourced, and have very large & helpful user & developer communities.