It's not too dissimilar, that said, SQLite is much more performant than Jet (MS Access) for mostly read scenarios. Not to mention that computers and disk speed are much better today than decades ago, where Access use was larger. You can pretty easily hit tens of thousands of rps with SQLite and probably even Jet for mostly-read usage.

Most applications don't have even hundreds of thousands of simultaneous users, so SQLite can be a great fit. Where SQLite also shines in that it has clients in just about every platform/language you're likely to want to use.

Archival/backup/portability are also very nice use cases for SQLite. I've worked on projects where there are specific, time-boxed data input and had actively pushed for using SQLite per box and still feel it would have been better. Vs having a very complex schema with export/archive functionality as custom code. My idea would have allowed to simply copy a file as archive/backup and schema changes over time would not necessarily need to be accounted for as deeply.

YMMV, but it's definitely a decent solution for many problems. Much in that using PostgreSQL or another RDBMS is often a better solution over using a more scalable no-sql option for most applications. There has been a tendency to over-engineer things, and we're approaching a level of compute/io that is less and less likely to justify those efforts.

I would love to have something like MS Access but for web apps. I’ve tried various website builder products but nothing seems as straightforward as Access was.

Why is that? Especially if you have a read-heavy application, what is the scaling limit you're worried about?

I mean this works for Pieter Levels. Definitely will cause issues when you get above a certain threshold of users.