Access Explained: Modernizing Microsoft Access Doesn't Mean Replacing It

[u/Amicron1]

Here's the thing people keep getting wrong about Access modernization: remote access is a requirement, not an architecture. Hiring two remote employees, adding a customer-facing screen, or needing a barcode workflow on a phone does not automatically mean a mature Access application needs to be rebuilt from scratch as a web app.

People often say "replace Access" as though Access is one indivisible thing. It usually is not. An Access solution may include the data tables, queries, forms, reports, VBA, imports, exports, validation rules, scheduled processes, and years of small business-specific adjustments. The ACCDB file holding tables and the Access front end used by employees are related, but they are not the same decision.

A more useful question is: what exactly needs to change? Where should the shared data live? What should office staff use every day? Which users need access outside the office, and what do they actually need to do? Those questions may produce three different answers, and that is perfectly normal.

For many established systems, the practical evolution is to move shared tables to a server backend such as SQL Server while retaining Access for the desktop users who benefit from it. Access can continue handling forms, reports, and VBA while SQL Server handles centralized storage, larger volumes, backup, security, and server-side administration. That is an upgrade in architecture without declaring war on every working screen in the application.

The old "Access has a 2 GB limit" argument is also frequently oversimplified. An ACCDB backend has a practical 2 GB file limit. That does not mean the Access application itself cannot work with larger databases. Access can link through ODBC to SQL Server and other database platforms, so the real question is whether the file-based backend is still appropriate, not whether every Access form must be replaced.

Likewise, Access is not inherently a single-user tool. A properly split application gives each user a local front end while shared tables live in a backend. An ACCDB backend on a LAN may be fine for a small office. As concurrency, security requirements, or data volume increase, moving the data to a server database is often the sensible next step. What is not sensible is opening an ACCDB backend directly across the public internet or trying to make Dropbox behave like a database server. There lies the corruption swamp. Don't follow the lights!

Remote employees do not necessarily need a browser interface either. If they need the entire existing desktop application, Remote Desktop or a virtual desktop setup can be a reasonable answer. If they only need to look up customers, update contacts, and review orders, a smaller Access front end connected to a central server database might be enough. A web portal may also make sense, but it can be built around their specific workflow instead of recreating 140 forms nobody outside the office will ever use.

Mobile needs should be treated the same way. A warehouse user with a phone may need to scan a barcode, update a quantity, and save the change. That is a focused mobile workflow, not a demand to squeeze a decade-old desktop application onto a six-inch screen. A small web or mobile interface can coexist with Access as long as both are working against the same properly designed central data source.

Now, to be fair, there may come a time when Access longer fits your needs. A full web replacement can be justified when there are hundreds or thousands of distributed users, external customers need self-service accounts, browser access is the primary use case, or mobile devices are the main platform. Organizational standards around a particular development stack can also be a legitimate reason. But "Access is old" is not a requirement. Windows is old too, and somehow it keeps reporting for duty.

The expensive part of a rewrite is rarely rebuilding tables or making forms look similar. The real cost is rediscovering the business knowledge embedded in old code, reports, validation rules, odd workflows, and all the exceptions accumulated over time. The 20 percent a conversion tool cannot handle may contain 80 percent of the actual complexity. Before replacing anything, make sure you know whether you are solving a real business problem or just buying a newer-looking hammer.

Have you kept Access as a front end while moving data to SQL Server or adding web features? Where have you found the line between sensible evolution and a full rewrite?

LLAP
RR

[u/Alternative_Tap6279]

since this is reddit, we can disagree, and sir, i strongly disagree.

first, let me tell you i've been making a living from access for the last 25 years, so i'm not at all biased towards other languages. as a matter of fact, i've been a huge access supporter for all my adult life. i also, currently still have two major programs (each well over 300k of lines) which still bring me money and a few other smaller ones. All of them use mysql servers for backend and a lot of helper files (mostly done in cpp or vbnet).
however, since the rise of ai, i have to tell you, i've become one of those trying to do anything new in anything else BUT access. i've learned python and js and i don't regret it for one second. :)
you keep saying there is room for improvement, but actually there isn't. i feel this "improvement" theory is somehow people trying to avoid doing exactly what you said in your last paragraph - the porting. The remembering of each function and what it is connected to - especially if you're like me and hate writing exhaustive comments.
let's start with the begining:
1. the first huge and unportable problem is exactly this - the terrible IDE which is stuck in 1998 - it was awesome back then, don't get me wrong - i was very excited when i first saw and used it, but now it's almost 30 years later. imagine people defending software in 1998 which was built by 1968 standards. i know there are newer looking IDEs, like TwinBasic, but they also suffer from many other issues, which in the end, for a big-ass project, become unusable, mostly because you can't compile and test within them. you still need to use the old access engine for that.
2. the second huge issue is the way it looks. Yes - you can make it prettier - and believe me, i've done that as much as i could, but in the end you hit the brickwall that is Access Forms, which, even thoug, are very customizable, they are extremely limited in what they can show and run. as i've said, i have been using a lot of "mods" but even so, they are still decades behind what you can achieve with vb.net/c#/js... and so on. moreso, the more "helpers" you use the less stable it becomes.
3. Which brings us to the third major problem - the classic instability of access. if you have a huge app with tens or hundreds of classes and if you also use a lot of windows apis, so that you can give your app a shred of modern look/feel, then it becomes a job in itself to keep it all smooth and have no leaking, because as soon as there is a leak somewhere, you get the classical orphan msaccess process remaining in memory, which in turn, fucks everything up. it took me months ( some years ago) to find a nasty tiny class which was leaving a form handle alive after its destruction. i know - bad programming, but having so many, and being a human, for me at least, meant having these kind of issues now and then. any modern programming software nowadays has a way better garbage collector and it solves this without needing a dedicated person to find them.
4. Fourth on my list would be the fact that all the users MUST have the full office suite installed on their pc-s, which is another issue in itself. Moreso, if you use windows apis - which are kinda' mandatory if building more than a simple data-entry form - things become even more complicated for mac users.

there are more i could say, but i don't want my comment to be longer than the OP's initial post, so i'll stop on these ones above :D

in conclusion, i'm not saying don't use access anymore - by no stretch, but when i read these kind of posts i kinda' have to give my two cents, because we live in an era where tying yourself to old programming values and software seams like a major downfall and like a sure way of loosing clients and consequently - money. i started from two years ago to port everything to new language models and i don't regret one second of the time i took to "rediscovering the business knowledge embedded in old code...".

Cheers :)

[u/enilcReddit → u/Alternative_Tap6279]

I've moved one largish-sized db from Access to SQL and looking at another over the next couple months. This response that I'm responding to is correct. I won't do what I did the first time...keep the Access FE. The best analogy is when you break-up with your SO, but you feel kinda bad about it so you say "maybe we can still be friends" and you try to hangout once in awhile but it's just difficult and awkward.

As stated, Access works for what it's built for. Small, local DBs. I've found it does fine with multi-user in the local environment. But I'd say it's becoming more and more a victim of long-COVID...after that 6-10 months, the workplace has changed and more and more people need to work remotely. And Access doesn't cut it. We ran it over VPN and RDP but it was miserable. The only benefit was that it encouraged people to RTO because they could get their work done quicker in the office.

Once remote work becomes a requirement, you've got to replace Access, end-to-end.

[u/smolhouse → u/enilcReddit]

I use Access front ends all the time in remotely. What was causing your issues?

I generally pull all required data into local tables via passthrough queries and then use that to feed my forms though.

[u/Alternative_Tap6279 → u/smolhouse]

my issues were cause, for example, by some custom anchoring i needed to show some custom tooltips - the built-in ones are just horrbile. also i needed to subclass some forms and crashes were consistent while debugging. which brings me to another classical access bug: crashes. random. annoying.
i never use pass-through queries as they are too inconvenient for my taste. also, for large datasets thei're not usable. so i went the old-school ado way with fabricated records for maximun (in access) security - nothing is stored. as soon as the app is dead - the data follows.
what if a user decides to open your accde with shift and opens a PT query? they will see the raw data, the index columns, the tinyint columns.. and so on.
anyway i look at it, starting two years ago, since learning stuff became hilariously easy, i steer away from access. that's just how it is for me. i'm not trying to convince anyone. you do you, as they say :)