This is state government,
Bless you. I can't say anything good about working on projects for the State of Connecticut including that they always found creative ways to waste my tax dollars.
It sounds like my best option is to leave as is and work on massaging the data so it contains what we need.
Start by cleaning up the data. Since RI was not enforced, you are likely to find plenty. Then make sure the infrastructure is sound. Check the backup strategy as well.
Regarding future plans, there is no reason that the Access app could not be the future unless you need a web app. So, that should dictate now much of the state's money you spend on fixing the app to your liking. If the app is being replaced, there is no point in fixing anything that isn't actually broken. I also used to tell my programmers when I was managing a department - I don't want to attend a code walk through and be able to identify YOUR code. When you modify an existing application, your code should look like it was written by the original author. It should not stand out like a sore thumb no matter whether your standards are better or not. Writing new programs is a whole different situation. For those walkthroughs, I was a stickler for standards and consistency.
Access gets a lot of bad press about its limitations so you might get pushback from your IT department if you suggest that Access can solve the problem for the state, not just your department. The thing is that the people who write about how bad "Access" is are actually writing about Jet (.mdb) and ACE (.accdb) the desktop database engines that power access vs. SQL Server. They are not writing about Access the RAD tool and they don't understand the difference. Right now your app is using Access the RAD tool as the Interface and it is also using Jet (.mdb) as the desktop database engine to hold the data. Because Jet and ACE are desktop database engines, the size of the database is limited and so is the number of concurrent connections. In theory the concurrent connections are limited to 255. In reality, most Access apps that use Jet/ACE for the BE rarely support more than 50 users. Once you switch the BE to SQL Server, the downside pretty much disappears. Your concurrent user count now becomes the number of seat licences you have for SQL Server. Your data security is hugely increased also. The only limitation you have left is if your management is willing to live with a client/server application rather than a web app. The upside to developing with Access the RAD tool is the speed with which you can develop. The downside is that it is close to impossible to have a development team larger than 2 people so the application size limit becomes whatever 1-2 people can produce in the designated time frame. I'm not saying that Access is a universal development platform but for the types of applications it is suitable for, there is no better platform. Any replacement app that is web based will be quoted in the millions of dollars and potentially years of development time. Access can produce the identical application as client/server in less than 30% of the time and money and probably with better features and integration with other apps. But remember - not all apps are suitable for Access. It is just that when they are, there is nothing better.
If you are in a position to evaluate the state-wide need and how it is currently being handled, you should have a better feel for whether Access with a SQL Server BE would be suitable. Remember, if employees will need remote access, Citrix hosted by the state will be an excellent alternative to a web app and due to COVID, they may already have Citrix set up. In house people would work on the LAN. Remote people would log in via Citrix and would access the same LAN. One of my apps was hosted in Farmington CT ( a suburb of Hartford) but my users were up and down the East coast and spreading to the west coast as well as being in London and Paris. The Farmington folks used the LAN, everyone else used Citrix and the remote people got response times as good as and sometimes better than the local folks. Some of the data used by the app was hosted in SQL Server on the Farmington LAN and some was hosted in London because the app interacted with some existing applications.
When the schema is bad, that is the biggest hurdle to overcome. Are you going to try to fix it or are you going to live with it until the "new" app is developed? If the future is Access, you can develop a new database as you are making fixes to the existing database rather than dealing with cleaning up the old one Of course, you not being an Access developer, will mean that you are no way as fast as an Access expert would be but if you follow my advice to let Access be Access and stay out of the way, you'll be much more successful in a shorter amount of time than if you attempt to make Access bend to your will.
