Microsoft Ignite 2018 recap: Access team showcases new data visualization features

A typical model for the most massive of Access databases is to have an .accdb file per large primary table (for just those tables which approach 2GB in size), which is quite feasible to manage with Linked Tables (especially with the new Linked Table Manager in Office 365). This has been done with success (like https://social.msdn.microsoft.com/Forums/sqlserver/en-US/84a1bf0f-451b-48fd-8e2f-149bca96e8c4/msjet-engine-4x-databases-5-billion-records-10000-users-custom-record-locking-user-security?forum=accessdev, and in https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine) for a 1 Terabyte database with 5 billion records. Also, Access supports 255 simultaneous users (along with unlimited authorized users) which can be upscaled to unlimited simultaneous users, as even larger databases simply by converting Tables into Linked Tables pointing to a SQL Server backend, using automated tools like SSMA and https://www.PowerAccess.net/Microsoft-Access-Toolset-VBA-Framework-Excel-SQL-Addin to automate that.

In fact, such Access databases provide out-of-box support for datasets nearly as large as SQL Server Express, and have the ability to scale far beyond what SQL Server Express can support. SQL Server Express is limited to just 1GB memory use (as is especially important for SQL Server) and SQL Server Express 2008 is limited to just 4 GB for the maximum database size, or 10 GB for 2012+.

However, unlike Microsoft Access, SQL Server can't easily (or isn't usually configured to) work around such limitations by converting the largest Table(s) to "Linked Table + .accdb per Table", which effectively results in a "2 GB per Table" size limit for Access instead. Unlike SQL Server Express, Access supports 2GB  of memory, and supports 1 GB per recordset (or set of results returned by running a query, per execution per user), instead of being limited to 1 GB for all memory used by SQL Server (including all memory-based storage requirements and processing activities shared by all simultaneous user connections).

Also, unlike the severe 1 GB memory limit for SQL Server Express, Access 64-bit supports effectively unlimited (8,000+ GB) memory use.  Instead, those interested in working around those limitations, or the many other features not available with SQL Server Express, it costs $7,500+ (or per server + per allowed end-user PC) even just to license just SQL Server Standard (let alone Enterprise) edition for 2 CPU core minimum. That doesn't even factor in the major cost and complexity of SQL Server setup, hosting, maintenance (with even just basic install being beyond most users), and much higher cost (plus need to hire consultants or involve IT) even just to tweak a query/view calculation or add a field or user.

I've seen 1+ GB databases even reduced down to 3 MB after normalizing and optimizing the data/design, making Access more than enough (the best fit even) and far better performance than just moving as-is to SQL Server (or, in in a case such as this, even better than optimizing *and* moving to SQL Server).   The huge difference that basic Data Normalization, as well as manual optimization, can make should not be discounted.  In fact, Microsoft Access even provides built-in tools for this, such as Analyze Table and Analyze Performance under the Database Tools ribbon, with wizards to help you to normalize your data, simplifying splitting imported spreadsheets into multiple tables with lookups.

That said, I would suggest considering SQL Server backend for Access databases that large, or before approaching 255 simultaneous users.  But, even then, Access remains an excellent option (and sometimes the only viable option, if needs to be end-user editable/maintainable) for such databases. Often when I saw the term migration thrown around, such solutions still remain Microsoft Access database solutions, just with Tables replaced with Linked Tables pointing to data stored on SQL Server.