The main problem with Access is where the work gets done.
When you have Access on your PC, even if both the front end and the back end are elsewhere, your PC is where the internals of the query get done. Mechanically, what happens is that when you open the query, Access on your PC looks at the tables named in the query. It gets for you the ENTIRE RECORDSET OF EACH RAW TABLE by using ordinary file READ operations. Then YOUR machine does the filtration and selection defined in the query by looking at each record and applying the criteria you defined. It also defines a recordset for the query. The ONLY work done on a remote system is its role as a FILE SERVER - not an application server. All that remote system CAN do is pass disk blocks.
When you have mySQL or SQL Server or ORACLE, or when you have a web-enabled version of Access running on a web server, the work is done on the remote host.
In the DB Server cases, the front end passes literal SQL over the network connection. The server executes the SQL, applies the criteria, and sends the resultant recordset back. The raw tables are scanned only on the servers.
In the Web Server cases, you have to have built the pages with ADO code embedded so that you are simulating the results of your query with explicit (well... implicit) code analogous to writing a VB application using DAO or ADO. This code runs on the Web server, not your machine.
The issue is that Access, being originally designed as a desktop utility program, uses desktop paradigms. Excel and Word also use small-system paradigms. That's one of the reasons that so many folks kvetch about Office. But they forget the history and original aim of the product. Trust me, designing a system with the current scope "enjoyed" by Office was NOT the first thing they thought about. The retrofits that have been made over the years expand Office abilities, but often at a cost befitting any retrofit operation.
One such retrofit is breaking the assumption that all data sets are local. The file server concept is merely a way to make the .MDB file appear to be local even though it is not. So it technically does not change the internal paradigms. Even if you split the .MDB into FE/BE combinations, all you are doing is opening another .MDB file on the BE to go with the other stuff in the FE file. But that is still just a file server situation. And that means that EVERY INVOLVED PART OF THE FILE still has to reach your PC to do you any good.
The ability to use Access as a decent front-end to other DB programs like SQL Server and ORACLE and the like - that is one of the REAL strengths of Access. The ability to scale up by scaling up the BE server. That plus the (relative) ease of migration of Access apps makes it a good choice as a starting point - but due to its, shall we say, HOGGISH treatment of network bandwidth, you probably WANT to outgrow Access ASAP.
