how would you explain the 2GB limit to the uninformed newbie
When Access first came onto the scene, the only choice was the 32-bit version. What do we mean by that? It derives from something called a "memory model" used by PCs. Last I looked, there were at least five different models having to do with address sizes and ways to establish pointers to entities. (Talking in generalities here, not "entity modeling" concepts.) When you write a program that allows arbitrary internal data structure building, you have to allow for ways to point to those structures at very fine-grained levels. We are talking "address" variables here.
Recordsets are based on tabledefs or querydefs that each contain a fielddef item, which in essence is a descriptor of each field in the record using byte-oriented offsets from the beginning of the record. So once you have established a buffer for a recordset and a record is read into the buffer, the offsets contained in the individual field definitions tell you how to find the data for a given field. The key is that since you can address BYTE variables (Boolean, Byte Integer, and Char data types), you can't "play games" with address pointers without wasting address space, so that means that you are limited to addresses with 32 bits in them.
On any model based on a 32-bit architecture, you can play tricks with addresses, but here is the absolute cold bottom line: 32 bits equals about 4 Gb worth of discrete addresses. So the absolute upper limit for a 32-bit program is to be able to address 4 Gb at a time within the address space it can claim.
The next question that contributes to the answer: I'm going to have code and data structures (representing printed or graphic forms and reports) in memory, and I'm also going to have potentially large data tables in memory. How will Access keep them separate? Answer: By dividing the address space of the program between GUI structures and actual data. The EASIEST way is to take the address of 32 bits and divide it in half. One half goes to the actual MSAccess.EXE program itself and to the forms, reports, macros, modules, and query definitions, and to the table definitions used to get to the tables. The other half goes to the actual tables.
That means 1/2 of your 4 Gb is Access code, VBA code, and GUI-related and print-related stuff, including libraries (.DLL - see "References") and such, or 2 Gb. The other 1/2 of that address space is your data tables and as such, is 2 Gb.
The next obvious question is, how do I differentiate between tables in two different databases? The answer is that memory management hardware allows you to have multiple database segments open at the same time - but you only address the data segments as you need them. Clever (no... downright diabolical) dynamic manipulation of the memory management pointers allows a part of that data space to be used for one BE file and a different part for another BE file. But NOTHING can violate the hardware rule that says the total amount of memory addressed at once by a 32-bit version of Access can never exceed 4 Gb. In essence, Access makes each MDB or ACCDB file a VIRTUAL address space (and you can look up using VBA to open a virtual file; it is instructive and relevant to this discussion). I believe there is a limit of 16 possible database files because there is (probably) a fixed number of virtual file data structures allocated within Access. But this is an educated guess; please count it as such.
Please also note that with the advent of 64-bit Access and the "PtrSafe" declaration, addresses can now be 64 bits long, leading to memory spaces in the Tb range and higher. However, the internal structure of Access, as far as I can tell, still has not fully caught up and, as a native Access BE, still has that size limit. Using SharePoint (I am told), programs CAN use 64-bit addresses and thus can break the size limit. But I am not going out on a limb regarding what SharePoint does for you other than break that size barrier.
In any case, to summarize the answer, the 2 Gb database limit derives from a needed sub-division of the address size used by the largest PC memory model available when Access was first created.
