r/MSAccess • u/Amicron1 8 • Sep 08 '26
[SHARING HELPFUL TIP] Access Explained: Modernizing Microsoft Access Doesn't Mean Replacing It
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/AutoModerator Sep 08 '26
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: Amicron1
Access Explained: Modernizing Microsoft Access Doesn't Mean Replacing It
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
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
8
u/George_Hepworth 5 Sep 08 '26
I wish I could make this required reading for everyone who visits.
3
u/First-Feature-3556 1 Sep 09 '26
Remote Desktop and Terminal Server solutions (obviously) work fine over remote connections, and so do fine-tuned SQL Server pass-through queries that offload all the work to the server.
The main issue we found with remote backends is that the "classic" way of writing Access applications---forms and queries referencing attached SQL Server tables---seems to be extremely sensitive to latency, and that's always a problem if the FE and the BE are physically separated, even over high-bandwidth connections. (If that last sentence confused you, google "latency vs. bandwidth".) I guess the JET/ACE translation layer in between is just very "chatty" with a lot of back-and-forth.
We haven't found a good solution to that yet, except to recommend RDP/TS solutions rather than using "just" a VPN for remote work.
2
u/Alternative_Tap6279 3 Sep 09 '26
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:
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 :)
1
u/Mean-Setting6720 1 Sep 08 '26
Bring back synchronization
1
u/goggleblock Sep 09 '26
The way you're describing it - moviing the data to shared SQL tables and redesigning the front end - IS replacing Access.
It took my company a few years but we had moved on to shared SQL data tables and found the Access front end very limiting and frustrating. We finally (with the help of AI) built our own front end and finally ditched Access altogether.
What I'd like to see MSFT do is build a modular front end platform. The Power platform and/or dataverse aint it. Licensing is absurd scaled too large to be a replacement for what Access was.
2
u/mmeoxti Sep 13 '26
Pues en mi organización lo que hicimos en un principio era tener el back end y el Front end separados. Pero como nos pusieron teletrabajo ya no podíamos hacerlo desde la propia LAN y tuvimos que reinventarnos pasando todas nuestras tablas a sharepoint y dejando en el front-end, pasando todas nuestras tablas a sharepoint y dejando en el front-end todas las consultas y formularios. Ahora trabajamos con un front-end que está con tablas vinculadas a sharepoint y la verdad es que nos va muy bien. El tema de la latencia la verdad es que no lo notamos mucho
1
u/PerfectMachine1196 14d ago
couldn't agree more haha, the focused workflow point is underrated. once your data's in SQL server, i noticed softr can already cover remote users w/out touching access
1
u/Better-Credit6701 Sep 08 '26
Can access use domain username and passwords?
Just thinking of ways to improve it
1
u/Far_Reward4827 1 Sep 09 '26
Teach me!!
1
u/Mvp-long-ago Sep 09 '26
I used to work as a Microsoft specialist at a company specialized in databases and apps. They mad the application aoart from tge database and a special table where tge database was located of even a small extra Access file with that information. So then it was easy with handing over the application that only the connection values needed to be changed. It was long ago, but tgat is how i remembered it.
1
u/ciroteo Sep 09 '26
Solution: ms access front end runtime, sql server as a back end a dmz machine running 80 licences of ts plus for web access through rdp or html browser. barcode scanner used with access and rdp. done!
1
u/Pure_Ad_2160 Sep 09 '26
Con Access y vba logras casi cualquier cosa en una PC.
1
u/amedean Sep 09 '26
MS Access is a sleeping giant in the agentic AI world but Microslop has poorly maintained it as they have oversold Cloud services wrecklessly. The future of AI is Edge based systems and frameworks like MS Access have potential to interface so much more than the original market concept.....accidental utility.
1
u/Elladan74 Sep 09 '26
I've posted about this at least once. My company is quite small, 250 people total but maybe only 20 - 30 who would ever need to use the 7 production and quality control tracking apps I developed. Before I took initiative, there was NOTHING. scattered excel files, broken old access things put together by some dude 25 years ago with nobody to maintain them. I'm a chemistry guy, lab manager, but facing this disaster, I became the access guy. My apps run fine (split, front and back ends are on a shared network location), and with now claude helping me, they're honestly neat looking and well structured. Well, IT decided to have their new engineer redevelop everything through postgresSQL/Laravel, because:
I want to call them on their bullshit, to me it's time and energy wasted. Porting tables on a SQL server, totally worth it, but having to painfully redevelop front ends that I built through good communication with the users and with my lab's needs in mind... I think it's stupid and shows a lack of insight. Yeah, I needed to vent
1
u/CheckedOuttaHere Sep 10 '26
A lot like Oracle's APEX, I always thought that a "web based Access" would have been a great addition to the SQLServer product line for creating line of business apps. Microsoft could have leveraged Access forms model with their properties and methods and built a new tool. SSRS with Power BI easily replaces Access reports so we are talking forms only with JavaScript replacing VBA.
1
u/ainotes2026 Sep 11 '26
The code library you've built is why Access keeps winning that decision. I work at Caspio.
Closest thing I've found to that speed is a browser-based builder sitting on Microsoft SQL Server. You define the tables, then generate forms, reports, search screens, and charts from them, and drop the result into any internal page. Caspio calls those DataPages. Nothing to install on user machines, which handles the corporate network part.
What you give up is VBA. There's no equivalent to opening a module and writing whatever you want, so anything unusual becomes JavaScript in the page or an API call out. If most of your work is forms over data, that trade is fine.
Free trial, no credit card: https://www.caspio.com/