A number of years ago I was in the Navy and worked in an electronics shop on an aircraft carrier. We were responsible for calibrating and repairing all the test and measurement equipment for the entire ship as well as the squadrons that we carried with us. Easily over 10k individual pieces of equipment, each of which had to be calibrated on a specific schedule. Most of this data was managed centrally, and we sent/received DB updates a couple times a week. However, just managing our workload and operations efficiently required other data which was not part of this database. I had no choice but to build my own solution, and the only real option was MS Access given the barren IT resources available on a deployed aircraft carrier.
But man, was it a fucking lifesaver. Just for one example, we often had to send equipment out to different labs since we didn’t have the capability to test them, and shipping stuff required a standard military shipping document with various mundane pieces of information about the stuff being sent. Creating these documents was a tedious process of looking up the data manually and then typing it into a Word file. I just recreated the document as an Access form, with fields that would populate from a query. Creating the shipping documents went from 30-45 mins to essentially the click of a single button.
Being able to automate forms can absolutely revolutionize some businesses. Many business owners have no idea the extent to which automation is feasible here. I have seen fully manual paper process (printers/scanners/pen & ink/shredders/etc.) go into 100% digital realm and the impact it had on the business. It really is incredible the difference it makes. The most interesting factor was the fact that now that the inputs are captured in a computer, we can run business rules against those inputs computationally. I.e., prevent generation of forms you would have to otherwise manually revise and resubmit after the fact. Then someone figured out you could start plugging in other business systems to your rules engine and tracking state...
I think it is a fun and compelling challenge to see just how much of the business you can put "on rails" with computers and various integrations. I have some background in semiconductor manufacturing, and the degree of automation I witnessed there got to me in a really deep way. Experiencing a massive factory that can literally run for hours-to-days without a single human inside is something else. I feel a lot of businesses can strive towards similar goals. Being able to fully-automate manufacturing of silicon chips is an incredibly opulent affair, but if you can simply automate a paper-bound business process that is on a hot-path (I.e. used many times a day), it could potentially save a struggling business.
As a counterexample it's easy to get this wrong as well. In my EMT career we switched from doing run reports on paper to doing it on the computer. It was a nightmare. No attention to good UI principles had been paid. In the section on "What drugs did you administer?" we had to choose from a scrolling menu of perhaps 100 items for each drug we used (including oxygen which we used on everybody). Simply typing in their names would have been easy, but no. Every data element was like that. A report that took me 20 minutes on paper took 45 minutes on the web version. Classic enterprise software.
Edit: Above was an attempt at digitization while this thread is about form automation. Understood. My point is that there was a lot of automation that could have been employed but wasn't. With that and good UI principles, the org could have made us more efficient. They chose not to because what they really wanted was a searchable database of patients treated so they could get more funding. EMT productivity was never a consideration.
Bad UI is basically consistent in all descriptions of all healthcare software. After working for a couple first responder outfits, I'm increasingly positive the companies who make this software do not have anyone on staff who have actually worked in the field, nor are they willing to interview people who have.
There's a case in Australia where the scheduled claxane was not administered because the table which had the status was off screen because the table was too wide (it was scheduled, but was marked as "cancelled"). The patient died.
> In the section on "What drugs did you administer?" we had to choose from a scrolling menu of perhaps 100 items for each drug we used
I've seen a similar thing at a medical insurance company, not sure if this one was access (that they made IT take over) or winforms, but to make things even worse the list was un-ordered and semi-random. I only found out about it because the main user was bitching to me on a smoke break, but it had been this way for years and cost her at least a couple of hours a day. After the 5 minutes of work to slap an order by clause in there and the weeks worth work getting it though the bureaucracy (yay for that stupid process of billing internal clients) she was in tears.
Efficient and automated claims processing was the whole reason this company existed too.
I imagine this was partly an attempt at data normalization. If you'd been allowed to type it in they would have had to worry about differences in spelling, capitalization, etc.
I'm not saying the solution they used was a good one mind, but I bet that was part of the motivation to implement it that way
Oh most definitely. I didn't want to imply they went about it in an even half reasonable way, just that the motivation of designing the interface like that had nothing to do with optimizing for time spent filling it in
> Being able to automate forms can absolutely revolutionize some businesses
I once worked for an insurance brokerage whose in-house CRM had started years before as an effort to automate paper-based forms. The company's business model was based on having their sales advisers fill out insurance policy application forms on their customers' behalf and originally this had been done using pen-and paper, which was obviously time-consuming and laborious.
At some point, someone in the IT department had had the idea of scanning the application forms to image files and writing a simple PHP application to insert those images onto web pages with HTML <input> tags superimposed over the form fields, thus allowing advisers to fill in the forms using keyboard entry.
They quickly realised that once forms are filled in digitally, (a) you can store the form data into the database and retrieve it later for further editing, (b) some of the data can be filled in automatically from the customer's records and (c) when generating quotes across multiple insurance providers, the common parts of each insurer's form can be filled in from data previously entered elsewhere.
While the idea of superimposing HTML input fields over a scanned paper form isn't one I've encountered before or since, that company was turning over $3M per month by the time I joined, so there is clearly mileage in the idea.
Incidentally, it turns out that basing your entry forms around scanned paper forms completely kills dead any of the interminable bikeshedding that takes place around UX design with complicated, multi-section forms.
HTML is an amazing approach for fully-generated forms. But, for filling pre-existing forms, the mapping from HTML down to fixed layout on an existing document can cause lots of headache when it comes to getting really precise positioning of elements within dense forms.
We built an internal tool that effectively converts any business form into a PNG @ 300DPI, and then defines a fixed element layout on top. This allows for pixel-perfect positioning of elements you'd like to fill on top of literally anything you can get into a PNG. Each definition is effectively just a JSON model specifying where everything lives, and then it is fed into some C# System.Drawing routines to fill the template image with expected data. The output is then converted back into whatever format is most ideal for downstream delivery (i.e. merge PNGs back into pages in a combined PDF).
The downside of this approach is that rasterization of PDFs or other upstream forms items effectively strips them of any textual data that you could mine out of the document. So, there are certain use cases where this would be a bad approach. For most businesses, I don't think this is usually a concern, especially if you already have the data you used to fill the document in a good structure somewhere for alternate delivery approaches.
> While the idea of superimposing HTML input fields over a scanned paper form isn't one I've encountered before or since, that company was turning over $3M per month by the time I joined, so there is clearly mileage in the idea.
You can also do this with PDF files, if you have Acrobat (or at least, it was possible - last time I played with it was several years ago - 2009-ish).
You've probably seen "editable" PDF files - where there are fields you can type stuff into - much like a paper document that you'd put in a typewriter to "fill out".
Well, it's actually possible (in Acrobat) to set up a "form submit" (virtually identical - IIRC, it was an actual HTTPS POST message) that you could point to a web endpoint. Add a button to the bottom of the page to submit the form, and it could also capture and handle errors (like if the service to receive the POST is offline, or has an error, etc).
I forget was Adobe called it - but it worked rather well. I worked for a web app company who was contracted by a doctor's group to redo their website, and part of that was automating the forms. They wanted some way that a patient could download and use their standard PDF forms, to both enter and upload the information, or print it and fill it out manually and return it, if they were more comfortable with that.
They didn't want an standard web form, or anything like you mentioned - because they might change the PDF for manual copies. Instead, we supplied them a template and taught them how to use it (and not to change particular parts - the parts handling submitting/errors and such) - so they could continue to use it. The backend was a PHP/MySQL system, everything was HTTPS, and IIRC we encrypted the data on the backend. I'm not certain that it was completely HIPAA compliant, but it was certainly as compliant as "form filled out with pen and filed in folder).
So yeah - there's clearly a market for this kind of thing...
> Being able to fully-automate manufacturing of silicon chips is an incredibly opulent affair, but if you can simply automate a paper-bound business process that is on a hot-path (I.e. used many times a day), it could potentially save a struggling business.
That's funny. I work at a very large fab, and of all the places I've worked, this is the one that's most in need of the sort of in-office hot path optimizations you mention, and doubly worse is that it's the least susceptible to allowing someone to do anything about it.
(In fact, it's regressing. This month saw a fab-wide rollout of a document management system that has caused a ~10x increase in wall clock time for attaching files to emails or through forms on the intranet. Now consider what that means when the company exhibits a pathological form of the passing-MS-Office-documents-around-to-each-other mindset...)
Depends on lots of factors. Most maintenance is preventative in that context, so its hard to pin down a specific thing that would break down first or how long it would take. If something does break unexpectedly its usually treated as a big deal every time so it never happens again.
Huh, similar story here. The nuclear department on my carrier needed to produce this monthly report for training hours that would take them a ton of manual effort. There was no budget at all for any kind of automation but Access was on every machine.
I know what I'm doing with databases. I never want to use Access if I don't have to. But it has the enviable property of "no server administration required" which meant that we could backup the DB to a shared drive and people after me could modify the thing without being a DBA.
The amount of time that database saved really is mind-boggling.
My first real programming job was a summer project for a tutoring program at ASU where they managed their schedules in a shared access database. It got bad as it would get overwritten often. 10 students would show up with no tutor, or a tutor would sit and wiat for students who would never come cause they rescheduled.
We copied the structure to SQL server, built some stored procs ( only allowed way to access the DB at the time ) and fronted it with a bunch of aspx pages. They loved it beyond my expectations.
All of that was new learning as I had only ever done the LAMP stack at that time and this was all Microsoft stuff.
I partially fixed that by splitting the mdb into a front end and a back end where the back end was only tables (no UI, no queries) and launching the front end mdb from a simple command script that copied it to the users tmp directory first.
Then at least I could fix bugs in the UI and after a while that's where most of the bugs were.
All these responses demonstrate the typical issue with today’s dev mindset... i.e. wanting to develop a solution where there is no problem to solve, and very often resulting in an alternative with more issues than the original tool. There is a reason why MS Access and Excel endure: they are far from perfect and have tons of limitations, but they do solve the very basic function they were designed to fulfill.
An IT solution which is simple, robust and allows users to do their job without relying on tech support and maintenance is just a beautiful thing - that’s what technology should be about. Not, this endless envy to refactor what is working just fine for for the user... I love the examples presented above of areas where Access solves a real problem and helps them save valuable time, in difficult working conditions!
I don’t use Windows, so Access isn’t part of the MS Office equation for me (on a Mac). However, I’d very much like to have a simple database that I could use instead of tracking certain things in Excel.
So... I can completely understand why someone would be interested in making a more universal solution for this very common problem.
The Mac equivalent (for decades now) is FileMaker. Which, now that I look at it for the first time in a long while, seems to run on Windows and the web as well. [1]
Yeah, I’m aware of FileMaker. We actually use it a lot at my job, but I don’t because I can’t access it remotely from a Linux server. Not sure if that’s a quirk with the FileMaker server security we have or a lack of support.
I also tend to avoid it because it’s halfway in between Access and a real SQL database (Postgres, MySQL, etc). But also — have you seen how expensive it is? If you want an individual license, it’s >$500. Or you can get a subscription per user per month.
FileMaker seems like one of those applications that Apple has never really known quite what to do with.
Until the Access database grows beyond the understanding of the business types and they chuck it over the wall to IT for support. I worked at a company which was 5 or 7 years down the road from a management initiative to "decentralize" IT and back to "recentralized" IT. They'd had some kind of traveling Microsoft Access Training road show come by with trailers and pop-up tents to teach all the business units how to make their own Access databases.
This was before my time there, but I can imagine things were productive at first - the databases would have been simple and well-targeted to their business needs. However they were created without understanding of any database theory such as normalization or what makes a good key. Their authors had not been burned (yet) by the horrors that battle-scar devs, such as time zones, server moves, and decisions by the business to renumber identifiers one might naively choose as keys.
The quality of the database designs varied from bad to decent, but eventually most of the databases came to one fate: the original author left or got promoted, and their replacement knew how to add to the database but not how it worked. He/she came on board long after the Access Training Road Show had left, so hadn't had that training either.
A few business units noticed a sister unit had data they needed, and found it mutually beneficial to smush their databases together into one amalgamation. Lacking an understanding of both database theory and each other's database designs, much less their own, they simply built views, on top of views, on top of views. The resulting queries resembled a coral reef growing out of a subduction fault.
In due time the problems became too large for them to manage. New versions of Access would break things they couldn't fix. It bears noting that even if the databases had remained simple (which they didn't), the technology upon which they were built was emphatically not simple, which is itself a vulnerability. So the business had all these medium-sized systems they didn't understand running on a technology they didn't understand all catching on fire at once. It was the technical debt equivalent of having 100 balloon loans suddenly come due in the same 18 months after 7 years of low payments.
So then it became our problem (in IT). After multiple renumberings, correlating what physical location a store number (primary key) referred to required knowing or guessing the date when the row was created, so it could be looked up an historical cross-reference table.
Comparing date/times required knowing the location the row referred to. And every business unit had developed its own jargon, re-using trendy words they heard other business units saying but using the words to refer to other things - so you could never really be sure you knew what an identifier really meant. Because of the mergings you might have one database where table "Accounts" refers to corporate accounts, but "Accounts2" refers to user log-ins.
The Microsoft Access program has versions. The Microsoft Access database format also has versions. This leads to a matrix of combinations you have to keep in your head as to which version of the file they have versus which version of the program they're using, and whether it will be bug-compatible or beneficial to upgrade one or the other or if that's even possible with the combination and database code they have. Don't even get me started on reports generated by Access...
Maybe you can use Access as the front end to SQLite?
During my internship 25 years ago I build a system to register aircraft accidents and near accidents. This system used access 1.0 (Windows 3.1) as the front end, storing the data in Oracle 6, I think connected through Odbc.
For SQLite there’s https://github.com/softace/sqliteodbc but at that point I might pick a different DB for a more established ODBC driver. Or just use Access...
Sure it is ... the database underlying Access uses JetSQL which has its own fun syntactic quirks
It's also very bad at concurrent users if you do go down the path of dropping it on a network share if it's a normalized schema, since there's no server to cache queries or do optimizations... for strictly data entry against a simple schema you can maybe push it to low 100s of users, but backing up / restoring / rolling out new versions is a nightmare when the frontend and backend are commingled like that. You can deploy different versions of .mdbs if you have an external backend, though I've never seen anyone do so with an Access database backing an Access "UI" project...
I may be wrong, but sqlite is used a lot on Android which uses bionic, not glibc right? So it seems like there should be hope of switching to another libc if you need.
The hard part is the [nested] forms. Access and FileMaker have polished interfaces which would take a lot of man-hours to recreate. Maybe Electron or an SPA built in React.
Thats hilarious. I did something similar with comms equipment on my first deployment to Iraq, because as a lowly little E-3 I wasn't going to let some $80k piece gear I signed for disappear, and then pay for myself (although that was extremely unlikely anyway).
It ended up being useful enough that some folks up at division copied it and used it for tracking all of the laptops they were signing out, along with the paperwork, which you can't escape regardless of where you are.
My first serious effort to program (Access97) was an automated engineering log in Access that extracted my operating hours for the month and wrote them out in MS Word as a draft Navy message.
It really didn't have much in the way of error checking to ensure that the state of the equipment was consistent i.e., you didn't start a generator or main engine twice in a row.
Which became crucial when I handed my baby over to the FNG at transfer time, and went on house-hunting orders.
Came back, ran the report, and he'd gooned the data so bad that we ran our equipment for negative time.
Which was an outstanding business opportunity! Run the hours off of the equipment! Yes! We can name our price for this device!
Other than the part where time doesn't exactly work that way, the idea was great.
In the AF we called it PMEL. My anecdote is getting a 9 track tape of all of the test equipment part numbers and calibration intervals (TO-1-1-100 , maybe?). transfer the tape to a disk file, sort it and use binary search. a couple of years before ms access, but turbo pascal was king !
If the calibrations were on a schedule, shouldn't Sked have handled that? I was in the Navy as well. I've made countless databases, batch files, and VBScript just to automate and streamline tasking that the Navy refused to let you use non-gov tools like web apps and whatnot. I even created a fully finctioning VTC scheduling web application running on IIS and using classic ASP with a bootstrap/jQuery front-end for one of the bigger numbered fleets. This was in 2016. When they are all you reallly have, you can do wonders with the MS Office suite.
You’ll understand when I say this: our lab was part of AIMD, so we were governed by NAVAIR instructions. Sked was for shipboard maintenance, but we technically weren’t performing “shipboard” maintenance so we didn’t use it.
I have a special place in my heart for Access, its where I first started making money writing software and learned SQL and VB. Its where I really felt like I was making something that solved real world problems for people, quickly at that.
It was actually amazing how far it could scale, you could put a shared MDB file out on a Novell network share and have 30 concurrent users with no server app at all, users just doubled clicked the MDB.
I still think there is a killing to made on a modern day Access "Done Right". DB, Gui framework and printable report generator all in one sharing the same language front to back, top to bottom.
Salesforce, the core database/CRUD bit of it if you ignore all the stuff they've purchased and tacked-on in the last few years, is essentially 'Access in the cloud', in terms of the sorts of things you can do and the ease with which you can do it, and how far you can stretch it before it starts to get cumbersome. And yes, they are making a killing.
edit: And if that sounds easy, imagine how you would go about upgrading and patching and extending your access-like cloud platform twice a year without any significant breakage of any of your customers varied and extensive customisations, and without any significant downtime, and keep that up for 20 years. Thats the thing that Salesforce are good at, and not many people appreciate how difficult it is.
While I agree fundamentally with your premise, I would have to point out that MS Access was always software-as-a-service for developers. Ultimately, while it was accessible, the idea was to give powerful tools to developers to refine an interface/toolset for users.
SalesForce, on the other hand, seems to be more a platform-as-a-service targeted at the user with aspects such as SOQL instead feeling like a bolt-on to give developers access to the data.
So while they fill the same business case, they approach it from a completely opposite perspective.
(Source: software engineer that had to work with SalesForce and other CRM tools extensively while working with data engineers; and that has worked with pre-dotNet ASP + MS Access)
I'm not sure if a cloud version could ever replace access. Access is already paid for, works with on premise data, is easily deployed (no browser version madness), is ubiquitous, is easy to recover from bus factors (search the victims computer for .mdbs) and is easy to control access too.
Salesforce seems like a particularly bad solution, you end up with a small army of high priced, low skilled salesforce contractors and a heap of vendor lock in.
Yes you are right, one minor point though, when people build things in Salesforce the 'source code' is all there and visible (apart from installed third-party packages) and so 'bus factors' are about as easy to recover from as with Access - and there are plenty of Salesforce contractors around. I've seen some organisations kick big consultancy firms off of their salesforce system and then just continue building it themselves.
ServiceNow is a lot easier for admins/developers to use for workflow automation. In my opinion it’s a little easier than Salesforce but it could become bloated like Salesforce if it want already. Quickbase is another one I worked with but I was migrating workflows off of it. Quickbase was good for non IT people but not much better than Access or Excel.
A lot depends on what is actually implemented on top of these systems. I absolutely loathe servicenow because of multi page multilevel forms with horrible, ugly, no good UX I have to deal with.
Yea not the worst UX I’ve seen but far from the best. They give you enough rope to hang yourself and most companies do. They have a new agent workspace to help improve usability.
I loathe Workday. Performance reviews and taking time off have unneeded steps and comments in weird places. The flat and colorful UI looks nice but it’s hard to find stuff.
The people who implement ServiceNow are usually, to put it mildly, idiots. It's usually combined with a religious conversion to some spin of ITIL, which is almost always by default implemented to facilitate a lowest common denominator operational outsourcing engagement.
The nasty forms are always a result of internal politics and bad governance. One client that I worked with had 8 different ways to represent a physical location. Each operational director in the organization had a unique system, and nobody mapped them.
If you put a real development team on the platform and the leadership gets it, it's an amazing tool.
I was curious about ServiceNow and googled it and was excited as they offer demos. But they require registration and a lot of information about myself, so i stopped. Is it not meant for small businesses perhaps?
As a developer who is forced to use a service now interface for interacting with any other part of my organisation it's fucking awful in terms of user interface and friction. Unsure if that is because of our workplace setup or if it's intrinsic to the platform.
At least at my org it's awful because dev teams wrote / spec'd their own UX for "their forms."
Which, as you might imagine, resulted in a huge number of multiple choice, similarly-named properties, with all options also being inscrutablely named.
Try the developer site, link below. You’ll need to register but it doesn’t look too invasive. It is expensive for small businesses. The main part is an ITSM suite but I’ve built workflows for call centers and account managers.
My first real paying gig was Access 97 (I'm old). They actually taught it at my university at the time in the MIS/CIS path. I built a credentialing system for a small HMO. I was able to get it to support over 100 people, including report load. I split out the database from the front end (forms/reports) and put the database files on an NT4 share. When we grew and it started to buckle under load, I created an additional read-only share and replicated the read-write database to the read-only database every so often. This allowed me to split the users' load between those that needed read/write and those that needed read only.
Talk about a rapid design tool of both forms and reports (and schema), Access was tough to beat. I think Corel/WordPerfect had a similar concept as well.
I made my first system for a client 20 years ago on MS Access, and the latest one was again (they asked, I didn't say no) I created a mini-MIS for another client. Reports, forms, tables, everything MS Access. I won't be insulted if MS renamed it to "SQL for dummies". Access just works!
My manager was a huge fan of Progress, such that it became the de facto tool of choice whether it fit or not.
I was less of a fan, but just checking now, it still exists!
I'm curious about this one blurb from their web site though, which proclaims, "Progress Named a ‘Strong Performer’
in The Forrester Wave™: Low-Code
Development Platforms For AD&D
Professionals, Q1 2019"
I'm not sure how a database applies to sitting around with dice and figurines, but I've never really understood that whole pro gamer thing.
Hi! I'm working on a project called Retool (https://retool.com), and it's basically Access, in the cloud. We connect to any sort of cloud database / API, and let you build UIs quickly. It's good for building forms that write back to APIs, tables that read data from databases, etc.
I'd really appreciate HN's feedback on this -- if anybody has thoughts -- I'd love to hear them! I'm also david@ if you'd rather email me anything. Thanks!
Hey David - love what you’re doing with Retool. I think it’s a good solution for the front end piece of this challenge. Please correct me if I’m wrong, but doesn’t Retool focus on working with existing enterprise databases and APIs? If so then I think the missing piece is a DBaaS with great guardrails.
Access (and to a lesser extent, Excel) empower anyone to model their data and manipulate it without asking permission. The problem is they don’t scale well, there are no backups, they don’t show up in a data catalog anywhere, no integration with enterprise SSO, etc. They do benefit from file-focused solutions for these issues (eg the Access file lives on a network drive which is backed up) but they fall far short from what you expect from a production database.
So my challenge is: how can I let anyone in my organization create a new database, with all the backup/authentication/discovery/scalability out of the box, for them to connect to Retool?
Access has had a decent MySQL connector for a long time so the scalability, availability and other network factors can be a non-issue if Access is used purely as a GUI.
I was never more productive than using Access. Once wrote a full budgeting, cost estimating tool. Supposedly impossible. (Access' VBA had an 'eval' function.)
We lost something in the switch from "workgroup" (dBase thru Paradox) to client/server.
Spoiler: ODBC broke the tight coupling of code and persistence. Spawning monstrous hacks like ORMs and ActiveRecord. What Ted Neward famously called The Vietnam of Computer Science.
It took me a very long time to figure out what went wrong. Even longer to fix it.
Cliff hanger, I know. Someday I'll post polish and publish that fix. Using my real name.
The thing that was lost quite frankly was most of the value that desktop productivity software used to provide before Microsoft consumed the market with Windows. It's not that client/server couldn't provide the same (or even better) value, it's that the companies left standing (think Microsoft, Oracle, IBM etc.) had no interest in providing maximum value to customers. They wanted to extract maximum value from customers.
Access was created to compete with and replace those single-user and workgroup dBase-era software solutions from the 80's and early 90's. It helped them to cover the bases of the 'professional' desktop use cases as they won people over with Windows. With that mission complete and all competitors long gone, Microsoft now needs to kill off Access to get more people migrated over to SQL Server solutions which will allow them to extract more value from their customer base.
It's interesting to see that, given that in modern times, Microsoft is the only tech giant that can still build user-empowering software. Not Windows, that's getting dumbed down UX-side. But Office suite still includes Access and Excel, the latter being probably the single highest productivity booster in modern companies (to the chagrin of IT departments).
And you're essentially saying that this is already another order of magnitude worse than what came before Microsoft. Now I wish I was able to see that older software in action.
If you look at how things have progressed from the GUI desktop->web->mobile you get a reasonably good flavor of the trade-offs that have been, and continue to be, made. Compare the richest, most powerful spreadsheet application you can find on the desktop to the same on the web to the same on mobile... there you go ;-) (and it isn't just the relative maturity of the platforms: once one player dominates a platform, things start to stagnate)
If history is any guide, the mobile spreadsheet will never do some (probably much) of what the desktop version did[1] and the desktop version will never get some of the new capabilities of the mobile version[2]... yet over time the less capable, but 'better', mobile app will become the new normal for many. The good news is the mobile version will still be more powerful than whatever platform comes next.
I'm greatly over-simplifying and glossing over some things (esp. re: the dynamics of the text UI and early GUI eras) but I think the basic idea still holds.
[1] It's 'good enough' for the majority of use cases. Some would argue that the lost features were cruft anyway. Which is fine unless you needed that cruft...
[2] The profit motive is no longer strong enough to warrant the needed investment in structural changes to the desktop application
> [1] It's 'good enough' for the majority of use cases. Some would argue that the lost features were cruft anyway. Which is fine unless you needed that cruft...
I don't like that "good enough" argument, because it misses the fact that features shape use patterns. Features are enablers.
To give a random example: I once visited my wife's office, and was given a task I could help them with while waiting. It was a task they did very frequently, and it boiled down to specifying a search query, and then adjusting some data in each of the result. They thought it'll keep me occupied for an hour or two, manually adjusting each of the hundreds of results, but I looked around the software and found a way to do a bulk update hidden somewhere in the UI, and was done in 3 minutes.
I taught the people in that office the trick and they were grateful, but it made me wonder - what if one day the software vendor removes bulk update switch because it's rarely used? It's only rarely used because it's not easily discoverable (but the telemetry will not tell you that). Wondering even further - how much money and opportunity to growth do companies lose on office tasks that are inefficient only because software was developed for the lowest common denominator, and offers no advanced features and no path to mastery?
I don't have a thesis about the business (software publishing) aspects. Well, I do, but it's a tangent: Microsoft's failure to provide the much promised ramp from Access JET to SQL Server (which I also love) alienated me as a dev.
Here I'm only commenting on the impedance mismatch between code (biz logic) and persistence (eg RDBMS) introduced with ODBC. (Specifically, type information in the db schema is lost to the client code.)
Okay, writing this out now, Microsoft (and everyone else), probably didn't appreciate what was lost with ODBC, so didn't consider how to continue the Access style of CRUD to client/server. So we ended up with nonsense like LINQ.
That would mean using a server for the database, which means lots of meetings getting buy in only for it to be prioritized to get done 9 months later.
You also have to deal with DBAs who like to say no and permissions every time you have to make a change.
Even if you broke down all the bureaucratic barriers, Access is still quicker to make a simple to make a simple CRUD UI than Java swing.
Sometimes worse is better.
Not saying I would use Access today but that is because I became a software engineer who has more authority to use the proper tools. When I started out as an data analyst, Access was the only thing available I had to get the job done and it did it well.
> I still think there is a killing to made on a modern day Access "Done Right". DB, Gui framework and printable report generator all in one sharing the same language front to back, top to bottom.
Reminds me of Airtable, Monday, Notion or one of these all-purpose productivity tools
Airtable is really good for what it does : makes data-driven tools available for folks who don't want to mess with writing queries or being a DBA, and also has a reasonable API for automations.
For heavy stuff, I'll take Postgres anyday given the choice, but Airtable is great for small user-maintainable tools + dashboards, etc
Yes, but those formulas are scoped to the row, not the worksheet (you can't pass in a range of cells; each row essentially treats its own columns as local variables). SUM() means something completely different in Airtable than in every other spreadsheet out there. I've built out some beautiful Airtables only to find I had to switch to using Google sheets since I couldn't total up the value of a column, something I've been able to do since Lotus 123 in the 90s.
You can aggregate across multiple rows if you use a separate table to aggregate related records. I've done this for a small nonprofit to aggregate event registration data by year by linking the registration records to a new year table. Sounds kludgy, but it works.
> I still think there is a killing to made on a modern day Access "Done Right". DB, Gui framework and printable report generator all in one sharing the same language front to back, top to bottom.
I don't know what the actual market [that will pay money] is for it, but I can certainly say I've never seen anything quite like it.
In high school, I worked as a sysadmin/misc. programmer for a company whose whole business was software built on top of Access. I was the weird Linux person who ended up having to do a lot of stuff in Access (97).
It had its issues. It was not perfect. But the simplicity with which you could bind a visual tool and query designer to an ODBC/SQL backend--I think--goes unmatched 'til this day.
There are many things I cram in a spreadsheet these days that would be way more flexible if they were instead built on a quick and dirty Access DB.
If there are equivalent tools out there, please, let me know.
>you could put a shared MDB file out on a Novell network
>share and have 30 concurrent users with no server app at
>all, users just doubled clicked the MDB
You could be describing myself. However... I beg to differ on it's scalability... There were 'issues' with the 2.0 Jet engine when accessing MDB files on network shares that would often result in 'missing data'. Lots of fun. (Lots of work up-sizing to actual SQL back-ends as well though)
Crystal Reports worked great for me. Even had ActiveX and ASP versions. Only problem was Seagate Software was in Canada and CR was quite buggy. So I got docked in my paycheck for calling Canada. It is SAS now I think.
Modern-day Access: Filemaker Pro was the contemporary competitor and still lives and has usergroups today; Airtables and similar (all SQL-based form managers) are the modern options.
Years ago I worked for a company that claimed to have largest Filemaker Pro database in the southern hemisphere. One day there was some sort of disk issue and filemaker silently failed, then for months it kept pretending that everything was fine, it just wasn't writing to disk. This lasted until a power outage one day when the company discovered it had lost months of customer data, no backups of course, the backup system was working fine but there was no new information being written.
Data loss isn't forgivable, especially in the non-technical user space that filemaker is aiming for.
I'd put that on the DBAs. Once a week they should have been pulling down backups and testing for problems. They'd have also discovered the problem with the backups themselves.
90+% of the good use cases for filemaker are at places with no DBA's, it's meant to be a database tool for non-technical people and small businesses. If you had DBA's they'd just want to lock it down and eliminate the remaining 10% anyway.
If a company is claiming to have the largest database in the Southern Hemisphere, they'd have in-house developers (who double as FM dbas).
I'd be interested in knowing who the company was, and the metric they used to judge their database size. I worked on one that was 4GB data and >40GB binary files (photos, documents). Interestingly, when all the data was removed and the file was optimised it came to less than 2MB.
FileMaker, in my experience, attracts a certain smug developer who simply cannot believe that there are more industrial-strength database systems and that FileMaker can't solve every possible problem in the world. Access devs, at least, don't seem to have that particular quirk.
I wouldn't rag on it or Access too much because it serves/d a purpose: app and data design for less technical users who are busy doing a million other things. I supported several dozen FMP apps at Stanford Student Housing (R&DE) as it was easy for analysts to make something quickly and worked at the scale needed. Later, they used proper databases, reporting, and app-ish tools. FMP ran on a half dozen early Xserves which used goofy, consumer-grade parts inconsistently while looking Apple over-designed.
I'm not talking-down either one. They're both great tools for their purpose. I've used Access a ton.
I guess I'm talking about a cultural difference. It's been my experience that Access devs know when a problem is mismatched for Access (and move to storing data in SQL Server, etc). FileMaker devs seem to press on even when it's clear that the platform isn't capable of supporting their needs.
As one anecdote (of several): I support an instance of a FileMaker application w/ several 500MB+ "FMP" files, each of which take _hours_ to "integrity check" when the "FileMaker Server" process crashes unexpectedly. We talked to the dev about putting at least some of the data into an RDBMS (MSSQL, Postgres, etc) and they scoffed at the idea. It simply didn't occur to their mind that the platform might not be a good fit for the purpose.
“(Almost) the same SQL as the big boy toys" was the most redeeming quality of Access. It somehow managed to be beginner accessible and productive without dumbing down, reinventing the wheel skillset lock-in.
Had Microsoft gone for a more custom interface like Filemaker instead of just nicely packaging up more general tools, my entire life might be very different and probably not better, even 15 years after having touched Access for the last time.
> DB, Gui framework and printable report generator all in one sharing the same language front to back, top to bottom.
Printable reports is so 90's. Back then you printed reports and shared reports on paper. In meetings everyone gets a printed copy of the report. Today web is the way to share reports, and you project reports on to a big screen in meetings.
Not to mention, Access won't randomly break because Microsoft isn't likely to Move Fast and Break Things or suddenly get acquihired. And it will work over LAN even when you lose your Internet access. And, all your data - including schemas and forms you've made - is in actual file.
Plus, it's the normal shitty SaaS model for something that isn't enterprisey enough. So it's easy pickings for budget cuts.
Access is like a cockroach. It never dies as long as some Microsoft stuff is still around. The only thing that will kill it is subscription licensing -- Microsoft eventually take it away for Azure AirTableClone.
Access running on your PC is because it comes bundled with MS Office suite, which is installed for you to have Word, Excel, PowerPoint and perhaps Outlook available. It's a great thing Microsoft didn't make it easy to unbundle them all, otherwise lot of places with Access wouldn't have it.
really? I mean, I get the aircraft carrier examples above but I reckon most business have internet access now.
I reckon (in the UK) loads of one man bands run everything off their phones. They're probably more likely to have access to the internet than MS Access.
I've only played with Access once in Uni so it's a bit hard to compare for me, but it sounds pretty similar.
I use it as my own version of Shadow IT for my wife's business when im just too time constrained to make her proper web apps to do stuff, and she's too reluctant to spend any money on things, I just whip up a solution in Airtable. She has a CRM I made her for managing her students and their balances. Works well and was super fast to put together.
It's pretty crazy capable as Airtable has various kinds of built in functions for math and string manipulation, plus it can do various kinds of relations, joins, aggregates and rollups. Heck the resulting table even exposes an API!
The only thing I can't really seem to do is business rule validation stuff that a typical CRUD app would contain.
Yup. I built a networked invoice database with FileMaker Pro (2.1??) on Macs at a university in the 90's. It was still there when I went back a few years later, running on an ancient Mac server with an external hard disk duct taped to the top. It had just enough functionality that the department could keep track of how the monthly budget was going while the month went on, as the official Oracle Financials system could only tell you once it was too late.
Later, I realised that it had a Y2K bug in it, but oh well.
The reason that both Access and Excel use is so prevalent in corporate “shadow IT” land is because there are many parts of the business that have problems for which only a negative or marginal business-case can be made for IT to solve it (given the “get out of bed” costs of most IT departments). It’s a barrier-to-entry problem. Excel and Access are cheap enough and fly under the corporate IT radar (no involvement needed), that these marginal problems can be addressed in a self-serve manner.
I’ve worked in organisations that have tried to kill these tools off but unless you can lower the cost-to-play or offer a “better” self/serve alternative, you will fail.
Access is the perfect tool for an intelligent, technically-minded person with limited programming experience to create an application to replace spreadsheets.
There are a lot of those kinds of people out there, and they're extremely useful in introducing minor optimizations that other people wouldn't be able to find. Access is for that guy who says "I know there's a better way to do this," but doesn't have access (no pun intended) to a team of programmers and a project manager.
I didn't major in programming in undergrad but I've taken classes here and there, so in my first job out of college I replaced a really awful system of spreadsheet-jockeying with an Access DB.
I considered other options, but that it's self contained and NOT a web application is a feature, not a bug. I couldn't get access to the corporate database, so I just ran the Access DB on a network drive. It's still probably there, ten years later, running happily on its own.
Honestly, it seems like the fix for Access... is a better version of Access. It fills a very useful niche, between spreadsheets and full-fledged applications designed by programmers. It's so much easier to make a quick app that works for your organization than get an external team of programmers involved, who will probably tell you "no" or remain unconvinced that you're worth helping.
With Access, you don't need political clout, you don't need years of experience, you don't need a title. Just build an Access application and get kudos from everyone in your team for making their lives easier.
These are excellent points, I think it’s underestimated how difficult it is to get approval and budget for a small project that doesn’t directly contribute to the bottom line or have huge savings. These small but impactful solutions using Access act as both a stepping stone to greater skill sets, a way to build POCs for non-tech or low-tech people, and to avoid months of politics in big organizations.
I’ve had projects that were red-taped before getting off the ground due to resources being placed elsewhere. I then take those project ideas, and instead build them in Access as a POC using shared drives, splitting the database, etc. By the time I have 100 users saying how useful the application is in a couple months, other bigger projects haven’t even finished a project plan. Meanwhile we’re then ready to scale the solution using an appropriate stack and basically can just say “replicate our POC features but improve performance, security, accessibility, etc”. So far that’s worked pretty well for me.
In summary: find people’s Excel files with a mess of VBA and formulas —> see if the use case should be expanded —> build POC in Access without permission/budget from a bunch of people —> see how it goes and then plan to scale with the evidence you’ve gathered from your POC.
I taught myself Access 97 when I was about 10 years old and helped a teacher set up a small database to register library books. I couldn't have done that with a real SQL database, but Access was somewhat discoverable. Never underestimate the empowering power of tools that can be learned without any external help! I hate Access, but I love it too.
I know you say that being something other than a web application is a feature, but we have Microsoft's replacement for access in Office 365 and it's PowerApps combined with Flow. I haven't been able to dive too deeply, and it definitely has a learning curve, but so did access. Further, data is actually backed up and permissions can be applied.
I also have fondness for Access, but I think that we have a chance for something like that again.
O365 still gives you desktop versions of the Office software. It does makes me a little worried, but I'm hoping Microsoft isn't suicidal and won't pull the plug on desktop software, leaving us only with the half-featured web versions.
Flow is interesting, but AFAIR it requires extra clicking in the administrative interface to get running, so it might not work for shadow IT as well as Access does.
I am hopeful that between MS's financial commitment to the suite, improved JS performance in the browsers, and a set of practices coming around, it will reach parity in the next few years. I run Linux and while I miss features, I can do my job in an O365 org rather than having to have a mac or run a windows client for office suite.
On the one hand, I'd like it too. I use Linux as a daily driver - but I recently replaced my sidearm machine, and I keep it on Windows, so that I have a computer on which I can run desktop Office.
On the other hand, I'm convinced that once Office on-line reaches feature parity with the desktop, they'll quickly drop files, and turn Office into just another SaaS that holds my data for ransom. I don't want that.
I couldn’t agree with you more. I’m currently involved in a small startup where loads of administrative, transactional data is contained and manipulated by hand in an ever-growing Excel spreadsheet.
I’m a Mac user but there’s plenty of Windows machines with Office lying around, and my first gut instinct was to start throwing something together and importing the Excel file to populate the DB, and construct ‘robust’(er) queries & scripts for interacting with and getting reports from the data.
Perfect? No way. But within a couple of days I was able to get something working more reliably and (albeit temporarily) “shored up” a major calamity waiting to happen. Am I proud of my work? Absolutely not. But it was quick, cheap, and cheerful. Eventually when urgency has lessened and there’s more budget (in terms of mind-space, time, money) we can start looking for developers that will take my kludge and make it into something more bullet-proof.
For now, the leap from Excel to Access has been a huge leap forward (the kind that the physically illiterate offhandedly describe as a “quantum loop”, not realising that quantum leaps, though discrete in nature, are tiny).
I think Developers also underestimate IT Setup Costs for a DB since its so easy to do it locally. Setting up a simple RDBMS like MySql or Postgres and skinning with with a basic, out of the box UI is an afternoon of work if you need to look up commands, or minutes if you have done it recently.
But setting up something for a Team immediately opens questions like Access Rights (Can we limit this just to the team, or even have profiles?), Security (Needs to plan nice on the VPN/intranet), Scalability (in theory easy, but provisioning/managing VMs is hard), and Recoverability (where are the backups stored and who makes them). Those are all hard problems from not just a technical perspective, but also a business one.
But that is just the technical hurdles, not the process ones. In a well (overly?) managed system it can take a handful of people multiple meetings and possibly hundred of man hours to add a new field to a form. Adding the field is trivial, but deciding all the business rules around it is hard. If you run your own system, you can ignore any established process.
So its two fold. Its not just IT start up costs, its also getting everyone to agree on changes to an existing system, versus running your own.
I would argue many of the costs associated with migrating from Access to a "real" DB, is not the actual migration script, but the reassessment of all the business decisions that went into the design of the Access schema. Usually a committee evaluates if they are valid, and what to do with existing data to make it conform to a new schema.
I've worked in multiple organizations that run small Access apps here and there and for us the main cost is just sitting down and doing it.
My last job was at an HR company and things were historically done through Access and spreadsheets, until an IT guy came along and started writing C# web apps, rose in prominence and now runs a small internal software team. I worked for that guy.
It's not terribly politically difficult to convert existing applications, but it's mind numbing work and it's bottom priority. There's not a huge need to change something that already works, and the only reason you do it is you know it can't stay like that forever.
Yeah exactly. Working in an IT Dept when someone said "ah they've built a spreadsheet to do it, but now they want us to add that functionality to the main DB" we'd all roll our eyes because it was another lashed-together thing that we would have to unpick and re-implement. But the fact is, if they'd come straight to us and asked us to do it instead of doing it in Excel first, they would have had nothing, for years and years.
“But the fact is, if they'd come straight to us and asked us to do it instead of doing it in Excel first, they would have had nothing, for years and years.”
Very true. As soon as us professionals take over the dev speed drops drastically and costs go up.
I killed a project a few years ago that had spent about $8M on some project to implement a pretty straightforward business process. No end was in sight. The catalyst was Access 2003 going end of life, and this little app doesn't work.
I don't know why one of a 3 BAs couldn't figure out WTF was happening and fixing it. We had an intern futz around with Access for a few weeks and it's been running fine to this day.
Access, as concept, is great. Is exactly what many need.
But it have a lot of small deficiencies that make it disliked by developers. I have used FoxPro, and it have almost all the right things instead (Fox is Access, but goooood!... except a bit less user friendly).
I wish I could dedicate to build this. Is weird to me why this kind of tool have so little support?
I really like the idea of developing a modern Access alternative, but why are you trying to create a new language? Aren't QBE and SQL sufficient?
Why not something that is like a cloud SaaS version of Access, but can be extended with some procedures and later used as a GraphQL backend like Hasura?
Salesforce is (among other things) basically a cloud SaaS version of Access. And people pay a lot of money for it. See my other comment https://news.ycombinator.com/item?id=21403393
I don't disagree, but isn't it a sign of much deeper issues if this "Shadow IT" land exists at your company in the first place? Why is it that experienced programmers can't efficiently solve a problem that a business user can handle in Access?
Are they under-staffed? Neck-deep in spaghetti code? Lacking business knowledge and the opportunity to acquire it? Bottle-necked by a lack of business analysts or testers?
I hear about this too often and nobody seems to think it's a major problem. IT departments can have a queue that's 2 years deep and people don't even bat an eye, they just think "Oh, that's how IT works!" And that's not even taking into account all the requests people aren't submitting because they've given up hope of getting any dev time.
The problem is most of the people writing code in Access (or monstrous Excel macros or in apps like MatLab etc) are not trained programmers. I think the article sums it up well by using "power user" to describe them - technical people with no formal programming training. Think Engineers (the non software kind), Accountants, Plant Technicians these kinds of people.
In my org (I'm an Engineer at a manufacturing plant) a long time ago someone senior made the decision our company was not in business of hiring programmers so any coding is done by contractors.
So yes people have implemented things (Engineering models for example) in MatLab, or Access, or as an Excel spreadsheet etc as "prototypes" ostensibly intended to be rewritten by a real programmer. At some point these models invariably find their way into production environment and suddenly you have some kind of mission critical thing written in Access (or VB6 or Matlab or Excel).
Then IT has minor panic a contractor is given the job of trying to translate the "prototype" into "real" code and has a battle on their hands.
Often programming contractor doesn't have technical understanding of what model is doing, regardless of choice of language the code engineers write tends to look like Fortran (i.e it's essentially all math formulas), even comments probably don't help when they say stuff like "/* Ergun's formula to calc Delta P */" that doesn't mean a lot to someone without a chem eng background. I've seen contractors produce bugs by doing things like misreading a spec and adding a variable called Fe203 (i.e Fe "two hundred and three) instead of Fe2O3 (Fe "Two Oh three" - a chemical formula). You can't really blame contractor he doesn't know anything about chemistry - he was just hired to rewrite this monster in java based on a spec.
So I don't think its a simple as turning problem over to an experienced programmer and expecting success.
I have seen all of these symptoms at numerous large companies. The backlog is years deep and the costs associated with engaging the professional programmers could be staggering. The truth is, not all solutions need anything more robust than what Office can provide.
Agreed, I think it's because developers like to over engineer everything and the managers like to over engineer the processes.
So many of these access excel solutions should be a days worth of work, simple perl cgi scripts with a minimalist UI deployed by rsync. Instead we have to use our super "productive" modern frameworks, split everything into a thousand files (god forbid you embed an sql query in the only place it's called), add unit tests, etc. There's certainly times for the later approach, but most businesses need much more of the former.
So because developers don't have a reasonable platform to pinch off random little projects others step in.
"So many of these access excel solutions should be a days worth of work"
I don't know why you think a lot of VBA would be replaced by a little perl. Of course, my perspective has a lot to do with the fact that it was essentially impossible to get a new perl module installed where I used to work.
But developers often don't appreciate the importance of presentation (and other) details in reports for managers. Your reference to "a minimalist UI" is telling.
Even though Access and Excel can be buggy, unstable, and annoying, it doesn't make much sense to use anything else if you are automating a report that was previously assembled by hand in Excel, and needs to match precisely.
And often IT types like to exercise power by gatekeeping - if you aren't doing "real" programming, you don't need a Turing complete solution, so Office ends up being the only option. I've been told that if I can select a list of columns from a dataset, and some filters, by pointing and clicking, that's all I, or my managers, need for reports.
Honestly, I think a lot of people find fulfillment in their work through being the person who can say "no" to people, particularly managers that are theoretically higher ranking. And also by expressing themselves through creative decisions when others fail to specify details. I think that using Office/Access/VBA may be correlated to rejecting the value system of most developers, rather than a technical judgment.
> But developers often don't appreciate the importance of presentation (and other) details in reports for managers. Your reference to "a minimalist UI" is telling.
We're talking access here, at best it's ugly and at worst you've got a gaudy background image(1) and a color scheme that would give the disability compliance officer a stroke. Throwing in bootstrap or a more minimal css framework is a huge step up in terms of presentation.
> Even though Access and Excel can be buggy, unstable, and annoying, it doesn't make much sense to use anything else if you are automating a report that was previously assembled by hand in Excel, and needs to match precisely.
I'm thinking of scenarios a bit more complex than that. Access apps generally have a few data input screens, multiple users, etc. Not complicated but not as simple as reports.
I'll admit that I do run away from anything to do with reports, but usually that's because they've installed some "easy to use, no developers required" reporting system that the non-developers can't use and makes life 10 times harder for the developers. If I can just write sql to shove data into an html table or excel template (where we can have the best of both worlds) I'm more than happy too.
1. I actually think of some of these old access programs when I look at windows new built in mail app, who the hell adds a background image?
"We're talking access here, at best it's ugly and at worst you've got a gaudy background image(1) and a color scheme that would give the disability compliance officer a stroke. Throwing in bootstrap or a more minimal css framework is a huge step up in terms of presentation."
I'm talking about creating Excel reports based on pulling stuff from (possibly a random assortment of) databases. Where you can use any feature of Excel. Not that the Access application is distributed to people who care what it looks like.
It seems like you can't even imagine a complex report that isn't an interactive application. So I think we're just talking different languages.
Honestly, I was just talking to someone in the organization I work in with the same lack of understanding. He was like "you have a point and click interface that lets you choose some columns from a table and some filters using simple boolean criteria, what else could you (or your manager) want?"
I want the ability to define all the business rules to produce the formatting and munge the data, I guess. And to structure the code in such a way that it's flexible enough to handle major changes. I need regular expressions. I need to run a diff algorithm on text. I need to use XML and REST to talk to SharePoint. I need to scrape information from a system that I only have access to through a web browser.
Basically, I'm using Access/Excel to do what I used to use Qlikview for, or just plain Perl, and it seems to be less of an "impedance mismatch" as people like to say. Also it doesn't cost as much as a car as Qlik licenses did.
Re: Why is it that experienced programmers can't efficiently solve a problem that a business user can handle in Access?
Because existing web UI standards suck rotting ass to hell and back. The standard was not meant for CRUD and still isn't even if you add gajillion layers of (buggy) JavaScript to emulate a real GUI.
I agree that if you tune it for shop conventions and know its warts well, one can indeed be productive in it. But getting to that point is not necessarily short or easy.
Sometimes it is budget or just needing someone to drive it through a heavy process.
It is also not always worth it if you cannot show a real business case. The thing might help you and it might be worth it if you hack something together yourself but if IT is suppose to build something that is available, has support, has disaster recovery, is patched etc. it might no longer be easy to find the business case for it.
Exactly, to get professional IT resources you need to demonstrate a business case, go through many decision boards and budget allocations, defend it for months, and be ready to be pointed out if it doesn't reach its promises.
So in the end people hack it in Excel or Access. No need to ask permission, no budget issues, no blame if it's not great.
Also: little or no waste happened if it doesn't pan out. Your Access prototype doesn't seem to make you and your team work faster? Copy out the little data you've put in there to Excel, SHIFT+DEL the Access file, and you're done. You've wasted few hours. That beats wasting several man-months of IT time only to not use the result in the end.
Access is agile in the truest form. It lets you prototype and refine in an extremely tight feedback loop, until what you have is a working solution for your specific problems.
I used to teach students MS Access, as well as the entire Office Suite.
At the time I questioned the validity of teaching them to use Access, as I was pretty sure that it was little more than a toy program, and would never be used in the wild by proper businesses.
Then at a mate's wedding one of the guests turned out to be a database admin at a large financial institution. I mentioned my opinions on teaching Access to students, and how pointless it was, and he laughed at me.
His response was that he'd often be asked to build a database to do xyz by management, and his reply would normally be something along the lines of...
"Sure, we can get a secure database, that talks to the other systems and does everything you need, with a web interface in a week or so."
Management would give him disapproving stares.
"Or I could knock you up an Access database in a couple of hours that'll do the job."
As the author points out, Access fills an interesting niche where Excel isn't quite enough, but a SQL database and all the stuff that goes along with it is way too much. On top of that, it's completely local so you don't have to pay for licenses or worry about data policies. That niche is big enough to sustain Access pretty much indefinitely.
I think the lesson here is that business users more often than not want something that 1) just works, 2) they can actually understand, and 3) doesn't require learning and setting up a bunch of other stuff to maintain or upgrade. It's easy to forget how big of a step up it is from Excel to a SQL-based application (or similar other business needs elsewhere) and how big of a barrier that is.
Access also generally comes bundled with Office, so for a lot of people it's the tool they already have at hand. And people are always going to reach first for a tool they already have at hand, if one's available that looks even remotely like it will suit their needs.
There are probably better database solutions for just about anything you'd want to build with Access, but getting something new requires doing research, getting approval, maybe allocating money, etc. Whereas if you have Office, Access is already sitting there on your computer, just waiting for you to pick it up and start making bad decisions.
I had No Idea it was installed on my machine! We pay for an Office licence per-seat here and, sure enough, I looked at installed programs and there it was.
Another key niche for Access was that MDB was for too long a time (decades) the only single-file DB format that people could reliably ship around as files / store on network shares / etc. Many a VB6 app used MDB as the underlying storage internals of their file format because it was convenient.
SQLite has only relatively recently started to be an option in that niche of single-file databases.
I'm not clear on why you are referring to Access as an alternative to a "SQL based application".
Access's SQL dialect is extremely annoying sometimes, but using SQL is a very significant (if not the most) reason for utilizing it rather than Excel.
I went around and around trying to find a way to query Excel "tables" with SQL or something similar, but eventually gave up. There is something called DAX, but it seems different just to be different, and nothing is orthogonal and logical - particularly external data is not on an equal footing with tables within your spreadsheet.
I use DAX in Power BI which I believe shares a common data model with excel.
Dax is powerful but sometimes frustrating. When you need bidirectional relationships you end up making custom measures to keep the whole model performant. Depending on the scale of the problem being looked at it becomes a mess.
Based on my limited experience, Power Pivot has a maddening lack of smooth integration with Excel. Power BI is a separate install, I believe, so not currently an option.
I'm stuck with Office 2013 for the time being and can't upgrade or install anything. If I could use anything, I'd probably prefer to go back to SSRS. Maybe Informatica, or OBIEE or something.
I'm biased by spending so many years writing SQL, but I found it extremely cumbersome to do basic things in DAX even after I figured out how, and haven't seen any obvious advantages. But I do know for a fact that software newer than 2013 adds some crucial features that would make it better.
DAX and SQL are qualitatively different languages. They serve different use cases entirely.
DAX is designed solely for analytical queries using dynamic context (so you don't have to code for every user selected filter or relationship dependency in your model.) Its meant to be written once and then used interactively by your users. It has tons of helper functions like time intelligence, iterators and generators ..
SQL is obviously much broader in scope and functionality, but to match the level of built in context driven dynamism of DAX would require a frankly unmaintainable mess.
So yeah if you just need an answer to a single specific analytic query, just write a SQL query.
But if you need to create a measure to show weighted average pricing for any user selected combination of equities, time periods, markets, vendors, and 500 other dimensional attributes - and then create 5 more measures on top of that to show year to date, year over year, performance vs. overall market, simulated demand at a 5% decrease in price, and price excluding large institutional purchases - DAX is your friend.
Ya it's tough if you're stuck on 2013. Sometimes even random commands in excel are great and you find out they work at home but not at the version at work, or require office 365 (?!).
Access?
I recommend watching some youtube power bi videos for dax tricks. Lots of dax stuff on youtube. Or radacad.
I used to work as part of a "Shadow IT Group" within a fortune 50 defense contractor. We built fairly complex solutions within MS Office because we were denied the tools to do the job properly. The most complex program I ever wrote in access was controlling electrical arrangements. This database was synchronized nightly across 3 different domains and would routinely have in excess of 50 concurrent users. I think the largest table had around 500,000 rows. The beauty of development in Office is the price tag and the time from concept to implementation. There was very little we could not accomplish with a combination of Access, Excel and sometimes a little wsh.
Is there a modern and/or open alternative to this? E.g a SQLite + electron or local web client thing where you could build a simple inventory or similar but you should also be able to scale it to client server when the need occurs 10 years down.
Note that any number of cloud startups don’t count as an alternative to access. When these things start it’s as an excel sheet with data that no one will go through the enterprise hassle to get cleared for anywhere but their own hard drive. This needs to sit above excel but still local. You need the entire business case and business value while it’s still in a local directory.
Arguably Saleforce. One aspect of the product is point and click editable database tables, reports, forms.
Actually I think the lesser known parts of office 365 'PowerApps' have various features for point and clicking your way to a cloud database, forms and reports, and even ETL from your existing data sources.
However, people don't think of these options like they think of access. For MS and Office 365, they could put more effort into marketing the features. Maybe they don't want to. For Salesforce, I guess they could try to get their product into non-computer people IT class syllabuses?
Like ... crazy push. They're running free App in a Day workshops in hundreds of cities, and the "Power Platform" (Power BI, PowerApps, and Flow) is their key foot in the door sale in FY20.
Low code/no code is always a Modern Workplace linchpin.
NSBase[0] markets itself as Access replacement using sqlite as backend. It's built using Lazarus IDE and has lua scripting. It's free and works on Windows, Linux and Mac. I haven't taken it through the ropes to see how good it is.
Thanks for correction. I cannot edit my comment, too much time has lapsed. I was reading up on Lazarus IDE about the time I discovered NSBase and somehow got it in my head that NSBase supports same platforms as Lazarus IDE. Apologies to those I have misinformed.
As soon as you bring HTML and JS in, projects choke and die. There is just too much goldbricking and fiddling and fussing and yak shaving around possible, and almost none of it is in service of actually making anything. The friction binds the gears up horrifically.
Something like WinForms with SQLite that didn't use Entity Framework would be far better. But really all we want is to get back to VB6 on top of Access. Maybe a few less footguns...
I have a good amount of experience with both winforms and modern web development. I would pick winforms over vanilla html and js. However, I would easily pick vuejs (or any comparable component based web framework) over winforms. Having maintained legacy VB apps with multiple 5000+ line forms, inscrutable multidirectional event chaining, and tangled up business logic and view logic, I've become a huge fan of forced separation of concerns.
The big problem is that there is much more of a learning curve. The first useful software I wrote used VB6 and winforms. Getting started was easy and intuitive, with a gentle learning curve. With virtually no experience, I was making neat things in hours. Within days I was making full applications. That says something about the power of that platform.
To be truly effective with modern web development took me a year or so. That was after years of professional experience.
At this point I can build web applications faster than I can build desktop software, but it took a huge time investment to reach this point, and I had to build multiple tools to make it possible.
I agree that there's a niche that needs filling. I want all the benefits of modern web development, and I want a learning curve like VB6 with Access.
> As soon as you bring HTML and JS in, projects choke and die.
True! I wonder whether making JS event handler in little textarea boxes would actually help. Select “Even handler for field ‘Name’ on ‘click’”, then program your event. Obviously the end result is a js file, but they user is guided...^W prevented from doing something else than modifying output before submission.
> but you should also be able to scale it to client server when the need occurs 10 years down.
YAGNI. Why not just go with a process that you can't scale to that hypothetical use case, and once that process reaches maturity (stops being adjusted every week) and grows to the point scaling becomes a real consideration, and not just theoretical one - only then hire resources to migrate it to a new system that's scalable?
I think that Open/Libre-Office have options for this. You can also use Access as a front end for another db (odbc) backend pretty easily.
I'd probably do a web app myself, but that's just me. I know a lot of people that cut their programming teeth on Access apps, including distributed ones.
People often develop smallish CRUD apps in MS-Access in two weeks or less. It's up and going without fuss and muss and without fiddling with servers, containers, DBA's, etc.
You have to admire its nimbleness.
Yes, the database "crashes" fairly often, but it's easy to make frequent round-robin-style file-based backups using Windows Scheduler and DOS scripts.
Most databases cannot be properly backed up with file-based techniques because of syncing of internal pointers. But MS-Access's separate "lock-file" based technique somehow facilitates file-based backups.
Such MS-Access apps are far from perfect, but if you factor in everything, they seem to be a net benefit.
I'd probably do a web app myself
If follow-on maintainers don't know the web framework used, it can be hard to maintain. Web apps are rarely nimble to do without an involved framework on which the developer is familiar with. MS-Access avoids that problem with ubiquity and a light drag-and-drop learning curve. (It did get harder to learn when they went from coordinate-based to an HTML-esque flow engine around 2006.)
Let's face it, desktop GUI IDE's are usually much easier to learn than web stuff. You don't have to deal with the web's lack of state, and CSS/DOM/JS headaches/bugs/inconsistencies.
When I first learned MS-Access, I barely had to read the manual. I could discover most functionality by clicking around. With Base, I had to constantly dig in the manual and still couldn't get it to do many ordinary CRUD idioms.
It's a clunky front-end with a fragile back-end. While it superficially resembles Access if you squint your eyes, it can't provide more than a small fraction of its utility currently. Too many missing/broken bits.
I spent a couple decades as a power user of the MS Office suite and find that I can generally make Calc do what I need as a spreadsheet and even Writer as a word processor. However, pretty much every time I've tried to develop a solution in Base it turns into a fight and I end up throwing up my hands and going with a different database solution. Calc doesn't even work all that well with it... and it's one of the few applications that works with it at all.
Running on? Wait, you mean I have to get IT to set up a server for me? Who's responsible for maintaining that? Backups? They're going to do that for something used by only 10 people that saves us maybe 20-30 hours a week? How much are they going to charge my department for this?
A company like Atlassian famously often had their first install at customers performed by employees on a rogue box under-the-table. Seems like it’s how one introduced software in the 2000s.
I wonder whether today’s equivalent could be made on Raspberry Pi. It requires a few tricks, but there is so much demand from managers who could afford paying 50€ out of pocket.
Of course, having a rogue box plugged to the internal network with a Wifi and Bluetooth chip on board is not IT-approved. Can it be bypassed?
LibreOffice has Base which can use either embedded database system or talk to proper RDBMS like postgresql. KDE has Kexi which afaik is pretty similar. I have no experience with either, nor have I ever heard anyone to use them.
Yeah it doesn’t feel like something more than an access clone. I’d like something that is as easy as access but can also be migrated to a modern web+db app with ease. Even if it requires some minimal coding ability it would be cool to be able to make an Elm crud app deployable as a container, starting from an access-like set of tables and forms.
This. Its interesting to read this thread mentioning DB and systems issues that are solves in Oracle APEX. We built an ERP system for a oil&gas client, including in the requirements, apps for monitoring gas values at the plant, by simply connecting to the mesurment equipment API. All that on Oracle XE, free version that comes with APEX. Three weeks to build apps, five months to implement in the company. I'm thinking software is not the problem, its people, politics in the company, resistance to change, really everthing but tech. We're solving business problems, helping the company cut costs, increase speed and streamline processes. Tech is a tool - there are so many other obstacles to solving problems other then tech. Thanks for mentioning Apex, and Oracle, in a thread about DB-s that fails to mention the company building databases for 40 years, that run for instance at CERN, to name but one.
Not that I've seen. You figure with file base databases it wouldn't be hard to put something together (sqlite?)
Libre office has "base" but its just a front end. My boss uses access, she like the query builder. I've got my mysql instance and some front end tools that do the same, but I'm a developer.
Filemaker is another of these applications, but not open source. The thing these stand alone apps have is direct printer access for printing forms and labels. It makes it hard to get off.
(Our get off filemaker solution is to download the data from our website and use a stand alone print app to print the labels. One extra step, but its not completely ideal.)
I would love to build one some day. I have ventured into building a complete personal finance dashboard that was purely local, but lost steam in the investigation phase. I am guessing there is a market for desktop applications that can do these things once privacy becomes more important.
I think Python could be a good alternative. It has modules in stdlib to interface with SQLite and Tk if you want to do an (ugly) user interface quickly. It does lack a good user interface to manipulate the database, though.
I'm convinced that Access could replace 90% of the business/enterprise software being written today in 1/3 of the time. It's especially powerful if you use a connector to a real database and just use access for its forms and reporting capabilities
While I don't really use Access, I think there's a LOT of power there, even as a basic front end to any other ODBC capable database. And while I'd probably reach for SQLite over Access, there's a lot there to really like.
In the end, it's relatively easy to get started with. As TFA mentions, it's really a great option for Power users. A relatively skilled business person can get up to speed with it quickly enough extending from Excel knowledge.
I cut my teeth on dBase II back in the day, and built my first consulting and development company on the back of that, then moving on to Clipper, FoxPro and Clarion and other tools over time. I always managed to skirt around Access whenever it came up in client meetings ("Oh, we have this FREE database thingy that came with our word processing and spreadsheet tools - why don't you use that to write our stock control app??").
It has been many years since I have seen Access, and I thank the database gods for that. Nowadays I run an HR SaaS company, but just yesterday we landed the biggest contract of our fledgling company with THE biggest manufacturer in the UK of a certain household product. All the excitement was sucked out of me when they asked if they could integrate our cloud HR system with their internal job costing & time sheet database... which is written in Access!
I remember the first Access version. Wasn't cheap (for my budget at the time) and I got it as a birthday gift. Wasn't my first contact with database (dBase III Plus, anyone?) but came with 5 huge manuals that covered a lot of relational databases, and was where I first heard of normalizations rules.
If it was easy for a teen in the first months with a computer, must be easy nowadays for any non-tech person who doesn't receive enought attention from IT because building a system would cost too much for each sprint/function points/hours.
You are right, it was 2 then 3 then clipper for me, it has been a while. The compiling with clipper took around 45 minutes, we always went to the cafe to play some billiards in the waiting time.
To be fair, they probably mean dBase II and dBase III. Although they could mean Vulcan and dBase II, which is less likely. I started my "database" programming career on dBase II in the early 80's and never bumped into Vulcan.
dBASE III and dBASE III+ were different enough that they are generally considered two different "big release" products.
I used to automate a lot of grunt work by putting code in dBASE tables. It's real easy to do that. You can make a sophisticated menu system using mostly just tables with code embedded. I was quite productive back then without having to type a lot.
I remember when dBase III+ shipped, there were some nice new features though we moved over to Clipper when that launched and completely avoided the "joy" of dBase IV. I did also try out another couple of compilers called Quicksilver and dBXL, but Clipper ate their lunch.
I wrote quite a few tens of thousands of lines of code in Clipper (using the BRIEF editor) all the way up until ~1998. By then I'd more or less switched to VB+SQL Server.
Gotta say I really loved working with Clipper, it was a really nice superset of dBase but had many of it's own specific design features.
I will admit a love/hate for Access. Namely, from years of teaching Microsoft Office as Intro to Computing. Access was always the section that was hard to convince students they'd ever need and worse yet outright made the class logistically harder for Mac users.
I will say, however, Access has SOME benefit to Information Systems education, especially in a general IS class. These students are marketing, accounting, etc. and so Access offers these students a quick dip into the world of DBs to understand how they operate without requiring they set up a local server or import some complex SQL statement.
This is a super niche group though, one that does not warrant much support. If someone were to develop an online Access-themed learning environment that taught those same basics, it would have the exact same benefits without the tangled Office requirement.
My university used it to teach relational database design, querying, form design, and report design concepts. It was really useful to have all that in a single software package.
My senior project was to create a complete project plan in MS Project for a system (I chose a video rental point of sale system), then build it out using Access. The project plan had to be a semester long plan, including features and assigned assets. The system had to have a proper ERD, forms and reports. It was a two semester course and taught me a ton about how to actually build software in a business environment.
I wonder how many people became actual programmers due to Access. I feel like MS can't remove it just like you can't remove the second rung of a ladder.
Even if MS feels they can't remove it - the fact they haven't made any significant improvements to it (even those to keep it usable on modern computers) means it will die a slow death and leave people stranded - the same way they did with VB6.
For example, the SQL text editor in Access is so broken if you copy and paste tab characters they're displayed as zero-width and break text rendering (if you click+drag to create a text-selection the selection will cover the wrong text) and you usually get a syntax error if you make any changes to the query after pasting it (but if you don't modify it then it's fine). Never mind the lack of syntax-coloring or auto-completion. Microsoft says they are not planning on making any changes to the SQL text editor: https://access.uservoice.com/forums/319956-access-desktop-ap...
There's also a fun bug where the outermost LEFT OUTER JOIN in a query with 2 or more other joins will fail if the right-hand table has zero rows (but will work if the right-hand table has zero matching rows).
I managed to corrupt my database and make all of the code irretrievable just by pasting a line that was too long, if I remember correctly. Luckily I was making daily backups.
I also ran into a bug where if you read a long calculated text field from a view/query through VBA, it corrupts everything after 255 characters, even though you can get the data otherwise through the interface or save it to a table. Googling suggested this problem had existed for 10-15 years.
Also, a lot of times your query will return neither a value or null, but "#error".
And I really hate the way it destroys all your formatting whenever you save a query. Every time I need to change something, I end up copying to a text editor and formatting by hand in order to find whatever error I just introduced.
The SQL text editor in Access is so frustrating! As soon as you switch the tab you're looking at, it will change your carefully-formatted query into a condensed unreadable mess.
When I was 14 or 15, my dad paid me to do work for him over the summer. He was in real estate and wanted to have a file/database entry for each of the floorplans and properties he was selling or building. Because I was so young, I used Access to create a CRUD app of sorts that kept details on all his projects. He used it for years.
It’s embarrassing for me to admit how long it took me to make the connections between what I did in Access (and later FileMaker) and MySQL and other database systems.
When I look back, I’m half-annoyed with myself for not just using a better SQL tool, but I’m equally pleased/impressed with what I built as a kid.
Airtable is probably the closest we have to “modern” Access - but I agree with the other comments that point out the value and potential of these types of tools.
Young experiences are how you learn, and make a lasting impression! In high school I tinkered with computers a lot. I setup SMB shares, ftp servers, created basic websites, some basic query work. I developed a pretty solid mental model of how "computer systems" work and talk together.
Now that I'm in industry, I'm one of the people building these "shadow IT" solutions. I can clearly see how those late nights paid off.
I also act as a liason between the business and IT at times, and can help bridge the communication gaps.
This is the question I always wanted to ask, I almost wrote an Ask HN...
Who use Microsoft Access in 2019?!
An obvious case is creating a glorified/enhanced Excel for some specific office tasks, another case is that some applications use ".mdb" backend.
But that's all? edit: What I'm interested in is cases of using Access for something other than a specific Excel-like office task - it seems Access is still used for some serious business in many businesses (pun not intended).
Also, if your office does use Access just for Excel-like tasks, but has overused it so much, please comment as well, I'd like to hear your story.
I'll bite:
We use MS Access as a thick client for a PostgreSQL database. I appreciate it for is reporting, forms, productive scripting language, and query designer.
What I don't like about it is that Microsoft hasn't really improved it in the last six years - but there isn't anything remotely like it: Xojo, FileMaker,and AirTable are close
but not quite there. For us AirTable is a non-starter because you rent it as a service.
If I had someone hold a gun to my head and jump to alternative - it would probably be Lazarus/Borland Delphi
An acquaintance of mine is an accountant. He uses it when his spreadsheets get too big/busy/complex. He's not "technical", but he is quite intelligent and is very familiar with Excel, has even written a few VB macros here and there. It's perfect for him. I assume the thousands of people just like him are the answer to your question.
Thanks for the story. I can imagine that, as I said
> creating a glorified Excel for some specific office tasks
What I'm interested in is cases of using Access for something other than a specific office task - it seems Access is still used for some serious business in many businesses (pun not intended).
Well, my point is that I think it's entirely possible that the only real use for MS Access is as a glorified Excel spreadsheet. That seems like a fine use case with a pretty significant market share! When you get right down to it, that's all most small businesses that use "real" relational databases do with them, either.
My first job out of school was fixing someone's bright idea of writing a few dozen ecommerce websites with Access as a data store. They stored the CC# so it could be run later as a card not present purchase. It was nightmare fuel, this was in ~2006.
U.S. based brick and mortar casinos use a lot of older tech and at many of them, Access is still a vital component. One I contracted with recently had sort-of a data warehouse (a SQL Server db primarily consumed by Tableau reports) but the daily ETL process to move data from the CMS (casino management system) into the warehouse was 100% Access. Via macros, temporary tables and views, some individuals believe Access can be an effective ETL script writer. In reality it crashed regularly and when it did complete, best case scenario was it took 3-4 hours. A rewrite in Python reduced that to 12 minutes, with far fewer errors and additional computations along the way. Besides ETLs, Access is also popular in generating mail files -- a casino may send hundreds of thousands of physical mail pieces each month, the address and offer data is often passed to the printers via CSV which was generated by an Access macro. The industry has numerous well-paid people with "Database" in their job title, whose sole experience is with Access, and couldn't write a single line of SQL.
Guilty. I have an AccessDB right now that reads from 3 different management systems to create tasking for a small office and about 450 external independent contractors. The first system is via a web scrape because the API costs too much. The second system is via a SOAP API and the third system is based in MariaDB. It reads data from each system and restructures it for tasking then analyzes and reports on the performance of the completed tasks. It also sends out nagging emails for tasks that are lagging behind and does a million other little things. To say it is a mission critical application is an understatement.
I have been a "developer" or should I say, I used to be a developer since the COBOL/DB2 days. I have used a vast array of systems and languages over the years but I have always had a love for the simplicity and power behind Office. But, the times they are a changing. Right now I am learning Go with the intention of marrying it with VueJS so I can at least pretend to be somewhat current.
Because the vast majority of Access users have it installed locally on their PC already as part of Office, and have never heard of SQLite. And even if SQLite was installed, you'd also need a different program altogether to actually view or input any data. Access includes the engine, the GUI, the query tools, the form builder in a single program.
Access is both a database engine and an UI and forms builder. You can easily design a database and make queries in the UI, and you can create custom forms to the extent that is doesn't look like a database anymore but just a custom desktop application. You can do that without any programming.
This is like saying why would anyone use a Toyota RAV4 when they can get a 400+ hp LS3 crate engine from Chevy. Is it because of the quality of the dashboard plastics?
I really like this analogy. The question you were responding to has been asked - in one form or another - so many times on this thread that its clear that those developing software and business tools are completely out of touch with users and power users of those tools.
I think at some point, anyone who used a MS-based operating system at work may have indirectly stumbled upon MS Access.
No matter how better other tools can be nowadays, there's no denying the fact that there are businesses who still have smaller MS Access apps that runs critical business processes.
I have helped a small company (~9-10 years ago) who had an MS-Access based application where the source code was locked as well as the database. Took a while to unlock both the source and the database and up until today, that application is being utilized fully in their operations.
The point though is that this isn't Access's fault. If Access didn't exist, those people wouldn't be magically replaced by expert DBAs who knew how to set up a perfect solution in Postgres or whatever. They'd be the same people, except hacking together some Rube Goldberg machine in Excel or Google Spreadsheets, with even worse functionality and none of the AD controls that Access at least gives you.
I'm not really arguing with the article, just commenting on my experience with the undead. Access is a brilliant piece of work for what it enables. I think it's a little unfortunate it isn't better supported, and that more software isn't built like it.
I still use Access from time to time for cleaning up data before importing it into SQL Server. Importing dirty data into Access is so much easier than with SSMS. Once cleaned up, SSMS gladly gobbles up the cleaned data from Access.
Ugh, what a completely appropriate topic for the week of halloween. Zombies, nightmares, and a lot of late fucking nights saving people from themselves.
At a previous job an operations analyst built out an Access database because our IT group never got around to helping him build something more robust. It actually worked pretty well.
Once we started getting to scaling issues we imported all the tables into MySQL and had Access use the remote tables that existed in MySQL.
This worked really well. All the forms and various things were in Access and the tables in MySQL and this scaled out pretty well to many 10s of users.
A nice side benefit is that I was able to reuse a lot of that data in an internal web application.
I'll put in a vote on behalf of access. I work in a consulting industry and as part of my work I have to track thousands of companies, individuals, tenders, and projects. It became obvious early on that a nice little DB would simplify the work. MS Access was there for me, already installed as part of the minimal software set they make available to us. Months later I offloaded the data to a Postgresql database (elephantsql.com) and continued using Access as a front end using the postgresql ODBC component. It's been perfect, and because all my data is offsite, it goes when I go. I have yet to find a better solution that Access as a front end to a postgresql database hosted elsewhere. To the person who suggested sqllite, I'd respond I'm reasonably technical but have no idea how to do that, so it's no solution to my problem. I used filemaker for a while on Apple. Linux has no GUI database front ends worth the while - Libreoffice Base is a weak substitute for Access. Kexi doesn't work as simply a front end. Excel isn't a database and we spend a lot of time laughing at people who use Excel when a DB is the proper tool. Any SAAS solution is out of the question; having a server dedicated to my/our use at work is out of the question. What's left is Access. Long may it live!
This reminds me of a job I had back in the mysts of time (...or more likely around 2003ish).
I was brought into a project where a seniour data scientist was working with MS Access but was runing out of room as it, at the time, had a database size limit of 1Gb on disk and our data set was about 2.5Gb in size.
We could have ported the whole thing to SQLite3, MySQL, PostgreSQL, MSSQL or even Oracle as we had site wide licences available.
Nope. MS Access was the favourite tool of this guy so in MS Access the data had to stay and I had to write code to juggle data between multiple simulataniously connect databases on a single machine.
This taught me a lot about how scienitst and accademics approach software engineering. My boss was both brilliant and really dum at the same time. He just wanted to get from A-to-B as "simply" as possible. He wasn't interested maintainablitly or assethics.
To this day the whole business still brings me out in a cold sweat of cognative disonance because on some level I know he may have had a point but on the other hand... I STILL have the flatspot that work gave me on my forehead from all the pointless wall banging involved.
I still beleive his work saved the company involved millions if not billions over years since it was presented.
(...and I have never accepted another contract involving VBA or other Microsoft technologies since.)
I work for a small finance firm. Before I joined everything was maintained ad-hoc in spreadsheets with no defined structure or consistency. I developed an Access database that now acts as our data warehouse and reporting engine. We are talking here about tens of thousands of rows of data not millions. Access has forms that allow non-technical users easy access. SQL queries can run more complicated reports. Excel can also easily import Access queries and tables.
Programmers often don't understand that Excel and Access are valuable because you don't need a programmer, simply a professional with some technical knowledge. Developing a fully-fledged solution would slow you down and increase your developer headcount by 1. If any modifications need to be made you could waiting weeks for an IT/Developer team to react. IT often acts as a blocker for us trying to get work done with limited data sets.
There is nothing else quite like Access on the market. Air table is but a toy for those of us in business that need a serious tool that can integrate with Excel and has aggregate and SQL querying capabilities.
I've just been teaching some basics of DB to high school students; wanting to introduce the concepts of tables, forms, reports and queries, but without need to know too much technical stuff, administration etc. Access should have been perfect (I hadn't used it previously, but have programmed, used and administered a number of 'real' DB systems), but it just didn't make sense to me. I'm sure I could have learned it, but the workflow just wasn't obvious. As well as being ugly as hell. So I went looking for alternatives, and they're thin on the ground.
Went with Zoho Creator in the end, but even some of the concepts in that aren't intuitive (it treats forms and tables as the same thing).
Having done quite a few things with anvil.works in recent times as a 'modern Delphi' I'm left wondering when the 'modern Access' will arrive.
> a special crowd that’s rarely targeted these days: technical people who aren’t serious coders
Flash had a bit of this too, especially in the ActionScript 1 days - albeit from a design rather than a business background. So too did every 8-bit micro that booted into an adequate BASIC. It’s a shame that we’ve largely lost this.
When I started, they used Microsoft Access almost exclusively. They would put an MDB file on a shared network drive and every agent would open it to input caller data via the forms. Maybe 100 people with the same MDB open. Mental. No backups, lots of (data) corruption!
I was once (or maybe still am) one of these power users. Started out with writing VBA macro's in excel to automate some work, probably created some monstrosities in excel. Then migrated over to MS Access and eventually taught myself proper (MS-)SQL which I have been developing in for over 9 years.
One of my crutches is exactly that, that SQL is more or less the only language I have mastered and when I while I feel like I am skilled enough to develop complex databases, I am always lost at creating a proper frontend for end-users. Quite often I will resort to MS Access as a light frontend (holding no data and minimal coding), just a bunch of forms.
Does anyone know an alternative to this? If I just want some forms but don't want to dive into web-based applications, what alternatives to access exist?
If you don't need it to be on the web, Python + Sqlite isn't too bad. Then a GUI like pyqt could do the trick (with UI designed in Qt designer). It still requires some coding, though.
If you need it to be on the web, php + Mysql is probably one of the easiest to learn. The cool kids might have moved to something else, like node or django, though. It also requires learning done basic HTML, but the complexity is reasonable.
I've dabbled a bit in Python here and there and indeed I feel this is most likely the route I will take, before web (as this is not a requirement for me as of now).
I was checking it out just now but I feel quite happy with the SQL-Server side of things, I am basically looking for a more modern way to present forms to the user to interact with my database, without having to learn C#/VB.Net or web dev. Or maybe that's just what I need to do to move away from MS Access as a frontend.
I think Microsoft Access has a lot in common with Javascript. Both are Lovecraftian horrors that no sane person would (clean-sheet) design in their current form. Both derive their ubiquity from having been the only or nearly the only option in their domain. Javascript being the only way to write web-native programs and already there in the browser, and Access being the only database available to users without admin rights on their computers and already there in the Microsoft Office suite. Both are tragedies of opportunity cost for the history of computing by pre-empting or delaying the emergence of any replacement built on superior technical foundations.
I, too, still have solutions in use as LOB apps based on Access. I have looked at other tools, being an officionado of Python, I started there. Got sidelined/sidetracked by Django, came back around to plain Python 3. Then I have to pick a GUI, currently playing with QT4, then get the DB connection working, hopefully against MySQL/MariaDB so I can have the solution multi-platformed... And so on.
I already have my annual revenue needs met by a service business, this is now all a side-hustle thing, but it has been a slog.
I do appreciate QT's visual Designer app, it makes creating forms more like Access/VB.
Don't run things on an unmanaged box on the floor in your office anymore. No more "My S.Q.L." no more flat file DBs saved in CIFS. If you have to ask these questions you're not the power user whipping up production code in a few hours with no framework or managed service you feel like you should still be.
Hmm... this is the classic worst case, sure. But there are lots of value for a local small charity to have a simple database to manage work schedules for volunteers. So, again, given a somewhat power user who is not a coder, how should they "whip up" a simple database app?
Access is the "too much for Excel, not enough for an RDBMS" solution. If it's too much for Excel, it's way too much for Google Spreadsheets, which is far behind Excel in functionality.
My organization recently adopted Office 365. One of the new apps was "PowerApps", a web-based GUI app builder. I popped open an example app inspected a button and, to my horror, I found the program logic was Excel syntax one-liners. When I checked the data binding for the app, it pointed to an XLSX spreadsheet on OneDrive. Time is a flat circle...
With 64-bit Excel capable of working with up to 4GB files, it sometimes feels like Excel itself ate/replaced Access for certain classes of business and shadow IT use.
Years ago an acquaintance was running a MICROS POS for his restaurant, and lamented the heavy tax burden the local government levied on him.
I was able to create an external software application (we called it "CookBooks") that would selectively skim off cash transactions (ignoring credit card transactions) from the Access database.
This allowed him to reduce his tax liability and pocket a substantial amount of cash on a nightly basis.
This was in 2002 and I recall that it took no time at all to gain update access to the underlying proprietary Access database powering the MICROS system.
I don't get asked about working with Microsoft Access that often, except when it comes to interfacing an online store with a POS or Accounting system.
MYOB Retail Manager, which you will find in many bricks & mortar stores as their POS system (often with multiple registers), uses MS Access as its database.
Why they never switched to SQLite or MSSQL Lite I don't know but it's there, does the job most of the time (except when it corrupts itself) and there's no value in moving away from it, and going all-out online is a big scary change for them.
Since I haven't seen anybody mention it yet, another old contender in this class was Lotus Notes/Domino. NoSQL before NoSQL was cool.
I worked with an ex-Lotus guy once, and he always raved about how easy it was to build out LOB apps with it and have a pretty capable little database in a single .nsf file.
IBM has washed their hands of it, but I know of a lot of big corporation, and especially militaries, that are still running applications on top of Notes.
I've brought up Notes in the past. My encounter was with a synthetic DNA company that was spread all over the planet; two US states, two European countries and Japan. Notes delivered reliable, distributed databases to all of these sites using marginal hardware, intermittent network connections and without much maintenance.
My Bride use to work for one of the big 'third bucket' credit card companies. Someone had discovered the northwinds tutorial database, changed the labels, and reworked the business processes to fit the queries - all tables were left as found. The best part was eventually, after acquisition, they had a directive that all databases must be ported over to Oracle... and some soul got to see the horrors of what was done and convert it.
This has to be my favorite story on this topic today. Triumph and tragedy in the same instance - tragedy that the person mentioned didn't learn how to use Access properly and triumph in that the person overcame, adapted, and innovated to find a solution.
The second sentence of your post contains both joy and heartache and - in my opinion - could stand alone as a short story for the tech crowd almost on par with the record holder for shortest short story ever.
These are some of the golden nuggets that I used to read Slashdot for 15 years ago and now find on HN.
The three characteristics in OP reminds me of PHP. 1) It was aimed at people who aren't that much of a programmer, 2) it made them feel empowered, and 3) it just works in a relatively simple setup.
(I'm posting only because I thought that someone must have mentioned this for sure, but couldn't find one.)
Here's a little known fact - the Jet storage engine behind Access is alive and well. It's used by Active Directory and many other products at Microsoft. At my previous company, we built a distributed key-value store on top of it (much like using sqlite in a distributed manner).
I once worked with a piece of 3rd party financial software, that I won't name.
We used their reporting functionality to extract data to import to our firms systems. Access was their intermediary data store. SQL Server -> Access -> CSV.
I had to support this, so I was one of only 2 people at the firm that was allowed to have Access installed.
Not necessarily Access's fault, but these reports failed routinely. Ended up with a Python script wrapping the whole process and retrying so I didn't get called at night. probably not Access's fault, but still leaves a bad taste in my mouth. Access is regarded as a toy, even more so than MySQL.
I think Access is more like Paradox, with code and data bundled together (for better or worse), rather than numerous PRG, DBF and index files that have to be “deployed” together.
Yeah, I did a lot of dBase / Clipper back in the mid 80s for rent money while I was in college. Liked the ease of use, but index corruption on a multi user network killed it off. And you have to write code, vs allowing users to accrete stuff on the screen with a GUI.
Our project's central database atm is an Excel sheet that different people interact with in different ways, Manually, Using macros and using Python (openpyxl and Pandas). It works well. Would Access be a step up?
Reading these comments really brings me back! If I remember correctly, developing a simple project reporting system based on Access, and using forms, was my first foray into professional software development (I had actually trained as a Mech. Eng.) - a career that took me from sleepy Surrey, in the UK, all the way to Silicon Valley! One thing that sticks in my mind is tab ordering of input fields - the difference from an occasional user to those who would use a system heavily every day.
Interesting that the pie chart in the article omits Oracle - which was actually the #1 DB in the survey results. Not that I'm a fan of Oracle at all, but it makes me wonder.
One great thing about Access is the graphical query builder. Not only is it easier than writing SQL, you can use it for learning SQL by changing view from graphical to SQL.
Access is still in the "Intro to Computing" classes in a bunch of lower level US Colleges. I suppose because they don't want to update their materials.
Forms over linked tables from MySQL or Oracle are a perfect use case for Access even in 2019. Initial setup is messy but it can be automated for end users.
>Clearly, there are people still interested in Access, even if it’s only because they’re trying to untangle the mess left for them by a previous generation of hobbyist programmer.
My job function entails extending and maintaining an MS Access database that our small company still uses as its primary tool for data entry and reporting. It started on Access 97, moving up through a few new releases until about 2010, which we stayed on until just this year. It's now working with the O365 edition. It was first developed by someone with no previous experience, referencing a copy of Access 97 for Dummies. I learned on the job just by poking around - which is now, I think, the biggest pain point for how we use the software: how exposed everything is. Prior to this role, our company would contract out for development: we'd come up with a big list of things we want, and it would be done and deployed within a couple week's time, although it usually took many revisions to get right. Now that I am able to do this development work in-house, things go much more smoothly as I also work with the day-to-day processes the tool is used for, and I have a grasp on how systems operate within our office. It's very important to have database tools with a low barrier to entry, so I think there would always be some market for this; where it really shines is its straightforward reporting and form editing capabilities, along with its user-friendly query designer. Being able to generate complex datasets without having to think about SQL (though still being able to write SQL!) is powerful.
(as an aside, I feel that I'm ready to move on from my role, but my abilities with Access don't seem exactly desirable or hireable, and as the article describes, there's always a looming threat of it going away someday. I was given a title of "Database Administrator" from higher-ups who think of Access as some esoteric ability, although gambits for pay raise so far have been fruitless. I see it more like ability in using Excel. I have some experience with MySQL via personal projects and programming in PHP, but I wouldn't call myself a dba if I'm being honest with myself. I feel a little stuck by not having the abilities to match my job title when searching for new positions, and if I'm going to the trouble of getting a new job, I don't want a lateral move with the same compensation. The wise thing to do would be to learn competence in proper database tools. I'm young, without a degree, and any advice would be welcome)
If you want to learn to do similar work (CRUD apps that talk to a DB) in "real" languages, then I would recommend learning Ruby on Rails[1] or Django[2]. The overall concepts should be really familiar, because they're similar to the workflow you'd use in Access, but you'll learn web development and a marketable programming language along the way. You'll probably also pick up details about how to structure a database that would be useful for your work in Access. Of the two, I think Rails is easier to get started with, but Python is probably more marketable.
If you want to do a deep-dive into Computer Science and transition to a full-time software role, then you might want to look into Lambda School[3]. I don't have personal experience with them, but several people I trust claim their results are excellent.
In polish high schools, Access is still a part of the computer science curriculum (for those who take CS). It doesn't have to be Access specifically, but it is in 99% of cases. You can't really avoid it, as it's required on the Matura exam. I've heard rumors that some teachers even teach the point and click interface instead of SQL.
To be fair, if you're gonna be in access, their SQL query writing interface is a steaming pile of issues. In my experience:
1) You can't do multi-line queries. After executing, it'll smash it all to one line again.
2) You can't add comments. After executing, it'll delete them out.
3) It doesn't do any syntax highlighting.
----
Years ago I worked for a company who had tons of manufacturing production data. Someone built an interface to query the data - used by just a couple people. Mind you, this isn't Access, it was homebrew software. Eventually word spread about the data you could access, and dozens to hundreds of people got access.
They never got to the top of the priority list to make it multi-user, separate profiles, create permissions on queries, etc.
There was a mandatory 8 hour training session with a test before getting access to the environment. Dev queries were automatically deleted 90 days since last run. On prod, 365 days. This was to reduce clutter. Query names were terse. You had to know people who knew what queries did. Comments were used sparingly. Anyone could edit and view any query available.
I copied a query, made edits, then executed. Got weird results. It took 1.5 days to figure out the parser was messed up! First, line comments using ' were excluded. Next block comments using /* */ were excluded. However, if a macro was called inside a block comment, it was executed.
That was the most frustrating bug I've ever debugged. Literally I'd copy the query, it'd run successfull, I'd comment out a couple returned values, and it'd fall on it's face because of how macros worked.
I'm still working on a replacement for a still-in-use a2k distributed database front-end (moved the data to mysql a few years back), it's taking forever, it's a huge project I spent over ten years on, and now another ten year trying to replace it.
It feels like th true replacement for Access right now is Excel/Google sheets. Not nearly as versatile, but it serves the same audience. I wish we had a good general purpose widely available Access replacement that's just a level above spreadsheets
Any database system that has a 'Repair' function is a liability.
Access should be taught as a primer at schools and universities on what not to use.
It's the BASIC of databases - considered harmful.
Then again, you can use a connector to connect it to MSSQl, MySQl, Oracle and others. And you still have a very powerful tool for reporting, forms, queries etc.
Still, the database engine was/is a liability as long as the locking policy and the transaction support messed up your data.
As a one-user only system it was fine, however people developed critical, multi-user business systems with this toy engine.
It allowed uneducated cowboys to develop systems fast that failed horribly.
My first job was to finish building a website that used MS Access as the database. I only learned years later how bad of an idea that was. Guess that’s what you get when you hire a programmer for minimum wage.
I made my MVP v2 in Access. Currently testing it and getting it checked out by a few founders, then I will release it, for free. Well, almost free, email collection.
It took me 10 months, which is fast considering the Excel version (MVP v1) took me 4 years, but then the Excel version has the whole concept from beginning to end, and there was a lot to learn, still a lot to learn really. The Access version covers only the first 9 steps. What I made was a startup system that guides founders from idea to early traction.
Access was not my first choice. I thought after the Excel version that I would use either a no or low code solution, a RAD tool, or some sort of visual development thing. As you have guessed I am not a programmer. I did dabble in BASIC, well a bit more than dabble, when my dad got me a Speccy. I enjoyed it, there is beauty in logic no matter how Phaedrus cuts it, but it was a means to an end really. From Draw and Beep through to Deluxe Paint 3 and OctaMed, it was about expression. I am not saying programming isn't a form of expression, but it is the long way round to me.
A few years later, I had to learn some Turbo Pascal, in order to make a database I think, talking 24 years ago, so I can't actually remember. But I do remember being bored out of my mind. So, I quit my computing degree. It was strange, I had an Amiga I could make things on. My college had just got some multi-media computers, yet in university, we had these stiff monochrome 486's. They didn't sing, didn't dance, looked awful but they did go a long way. Sheesh Kitkat.
Second time round at university many years later, I had to face programming again, this time it was Java, JavaScript and Object Orientated Delphi. OOD I liked, the other two I tolerated, for it was just a programming module rather than the whole thing.
After I had finished the Excel version, obtained feedback, tweaked and modified this and that, it was onto MVP v2. I looked at a shed-load of stuff, Bubble, Kexi, Lazarus, My Visual Database, Delphi 10.3, App Builder, Zoho Creator, Cross UI, Airtable, to name but a few. There were issues with all of them:
1. Not enough control and/or functions
2. Cumbersome and tedious to do anything
3. A sense of detachment as if I was having an OBE
4. Database; I don't care about connections, stacks and what-not, just sit in the background, and save stuff.
5. Process, and output what I tell you to do, and send outputs so they are inputs in other places
6. Questionable documentation
7. Customer support non existent, unresponsive or just prone to sending me back to the documentation I checked before hand
8. Tutorials and lessons showing how to make clones of well-known startups, or rudimentary apps
9. Holding me and anybody I share it with over a barrel
10. Unnecessarily complicated
So anyway, I decided fudge it, let me take a look at Access, it's been sat there for years. Many other people have it, although not Mac users. I am not a complete novice when it comes to Access, I know a little about 1,2 and 3 step normalisation, although I didn't strictly stick to it. Yeah I had to learn a bit of SQL and VBA, and any questions I had were already answered countless times on forums, although usage and disagreement of ! and . was annoying.
I was obviously unable to make what I really wanted, but I was able to make something close enough for what I deemed as necessary at this moment in time, in order to at least give a user a taste of what I propose. The minimum part of the MVP had to cut across the board, like a slice of cake, bit of everything.
I got asked a few times why am I using or used Access. I could have mentioned the above list, but the crux of it was, to me it was the tool for the job, with a support network around it, with very few chains and shackles, at a stretch I could do it on my own. I couldn't give a hoot about stacks, dependencies, libraries, etc. The latest languages, scalability, etc, whats that to do with an MVP?
I am proud of what I made. I won't be pulling out my who gives a toss but me violin in a public forum, but it's a tough lonely slog. It was my idea, and I had to use what I could to bring it to some sort of fruition. A small stepping stone in the right direction.
Not really. The Blogger/Blogspot UI is a special case of steaming garbage. It's a heavy-weight, bloated webapp whose primary task is to display a few kilobytes of static text on the screen. I think they should be used as a case study of what can go wrong within a team for a blog engine to look like that.
But man, was it a fucking lifesaver. Just for one example, we often had to send equipment out to different labs since we didn’t have the capability to test them, and shipping stuff required a standard military shipping document with various mundane pieces of information about the stuff being sent. Creating these documents was a tedious process of looking up the data manually and then typing it into a Word file. I just recreated the document as an Access form, with fields that would populate from a query. Creating the shipping documents went from 30-45 mins to essentially the click of a single button.