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

I expect you're right, but there are better ways (e.g. autocomplete) to achieve that.

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

Did this software company come from a company with a three letter name? :)

> 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...

> I forget was Adobe called it - but it worked rather well.

Livecycle Designer?

> 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...)

How frequent was actual human intervention required? Every few hours?

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.

Fun getting everyone to close their MSAccess processes so you can do some changes to the MDB.

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.

huh, i just posted before i saw this comment that I never saw anyone plan out their access database like this, kudos

I wonder if sqlite could become this in the future.

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]

[1] https://www.filemaker.com/products/filemaker-pro-advanced/

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...

I wonder how much effort it would take to build a desktop application providing a similar user experience to Access, using SQLite.

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.

Yup, ODBC still works: https://support.office.com/en-us/article/administer-odbc-dat... though because this is Microsoft and we can’t have nice things, it might not: https://docs.microsoft.com/en-us/office/troubleshoot/access/...

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...

But this requires admin access to install? No?

yes it requires admin access

Why would you do that though? The underlying database technology is not really one of the problems access has.

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...

cross-platform might be one reason.

Open/LibreOffice Base does have backends for MySQL and PostgreSQL already

I tried open office base about 10 years ago and it was almost but not quite impossible to use. Has it improved?

I wish it was this easy to connect sqlite

I tried doing this once with Go, hoping for a static binary, but SQLite library requires glibc.

There’s a huge need for a sql-compatible sqite-like database for this purpose in Go.

So, link in glibc statically?

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.

With sqlite3 package and Go this doesn’t look possible, or at least not easy.

https://github.com/mattn/go-sqlite3/issues/212#issuecomment-...

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.

In the future perhaps, but now Access has a big advantage for multi-user environment: fine-grained locking. In SQLite writes are basically serialized.

SQLite with a form builder would do it.

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 !

Man haven't heard "PMEL" in years - Precision Measurement Equipment Laboratory if I remember correctly?

yep.

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.