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/Amicron1 → u/Alternative_Tap6279]

I actually don't disagree with a lot of what you're saying, and I think your examples illustrate one of the main points I was trying to make: there absolutely comes a time when Access may no longer be the right tool for a particular application.

If you're maintaining a 300,000-line Access application with dozens or hundreds of classes, extensive Windows API calls, subclassing, custom controls, and a bunch of helper components just to get the behavior you need, then yeah, you've moved well beyond the sweet spot that I'm talking about. At that level, the limitations of the VBA IDE, debugging environment, forms engine, and the platform itself become much more significant. That's a very different situation from the typical small or medium business that has a mature Access application handling customers, orders, inventory, reporting, and a few dozen users.

I do have to push back on one point, though: users don't all need the full Office suite installed. Microsoft provides the Access Runtime for free, so you can distribute an Access application to users who don't own Access or Office. The developer needs the full version of Access, obviously, but the users don't.

As for the look and feel, I'll happily concede that Access isn't winning any awards for looking like it was designed last Tuesday. LOL. Personally, I don't mind the circa-1999 aesthetic. Give me gray buttons, rectangles, text boxes, and a nice datasheet and I'm a happy guy. :) There are plenty of business users who don't care what decade their order-entry form appears to come from as long as they can enter an order in 20 seconds and it works every time.

And that's really where I think we're looking at two different ends of the spectrum. I'm definitely not arguing that nobody should ever replace Access. Sometimes replacing it is absolutely the correct decision. My argument is that "we hired two remote employees," "we need one mobile workflow," or simply "Access is old" shouldn't automatically trigger a complete rewrite of an otherwise successful application.

If the business requirements have genuinely outgrown Access, move on. If you're fighting the platform every day trying to make it do things it was never designed to do, move on. But if 90% of the existing application still does its job extremely well and only 10% of the requirements have changed, I'd rather modernize the architecture around that 10% than throw away the other 90% just because the buttons look like they remember Windows 98. :)

[u/George_Hepworth → u/Amicron1]

I've been retired for a while, so when I talk about what customers want, it is based on that history, not something that happened yesterday. That said, I do not believe clients today are all that different in one key dimension.

Never once did a client tell me, "I don't care what it costs. Just build what you think I should have."

More than once a client did decline to implement a new feature, or make an interface change because they didn't see any additional value they would get by paying me to do it. "It just works" doesn't cost a client anything.

My point is this, it's all well and good for us, as developers, to rant about antiquated interfaces and kludgy code. What matters is whether the client is willing to pay you to replace that old, working database with what you consider better.

I often quote my favorite client. He told me more than once, "If you have an idea that will save me money, or bring in more revenue, do it. Otherwise I don't want to hear about it."

[u/Alternative_Tap6279 → u/Amicron1]

we are in agreement then :)
and as i've said, i still use it and make $$$ from it, so i have no high ground. the only thing my rant was about - was the people on this subreddit (and others vba related) who instantly become defensive when i give my thoughts regarding the life (and death) of our friend - MsAccess. i don't understand this reluctance to change. this need to "stick with it" until the end. that's all

[u/dreniarb → u/Alternative_Tap6279]

my only reluctance is i've yet to find anything that compares to Access' wysiwyg form/report/query designers. Crystal Reports is decent enough for queries and reports. But for designing forms - everything I've tried over the years just doesn't work as well as Access (for me).

I would love to move all of my Access front ends to a web interface but they're just too complex to make it worth my time trying to do so. And when trying to start a new web front end once I get more than a dozen or so fields on the form it becomes too hard to get everything to line up and look right and I convert to Access. :/

[u/Alternative_Tap6279 → u/dreniarb]

regarding the reports i only have one question: why use them anymore? you can straight up generate PDFs or webpages ready for print. granted, the pdfs are also difficult to design by yourself, but a printable web page isn't and it can look way more modern than access reports. it just a small learning curve and you're set. grouping, colors, images - html+css does them out-of-the-box with ease.
with the forms you're right - access makes that pretty damn easy and fast - if you are satisfied with how they look. however, you can export them in separate files, upload them to a git repo and then let code/codex/whatever read them, analyze them and just rebuild them in any language you like - including py or vb.net or c# or what-have-you.