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.