Increase the maximum file size from 2GB upwards
I know that this idea has been floated twice before and I also note that the Access team have responded and indicated that this is not part of the development and I also note that its been closed so more votes cannot be taken on it. Thought it was important to re-open because its actually quite a popular idea. I understand that SQL Server / SQL Azure or SQL Express are the way to go if users are wanting larger backends but for quick and dirty working for ETL projects or random data analysis of large datasets individuals may not wish to have the additional overhead of enterprise grade backend database install. Remember a basic SQL Server install will require SSMS and the SQL engine ontop of Access - For individuals in large organisations this may require petitioning central IT for a number of weeks or months. I would always recommend using SQL as the backend for even smaller projects but increasing the max file size would just make Access even more flexible. Anyway keep up the good work MS
Thank you for providing more info on this topic.
There is no change in our previous recommendation to use SQl/Azure SQL as a robust backend with unlimited storage.
Mark Hammer commented
Yes, it does seem like Microsoft's "ankle chains" affects this issue as well. Ever since ACE I'm sure that it would not be a herculean task to go well beyond 2GB.
What a shame :-(
John Tsioumpris commented
If you really need more space you should consider using other embedded database engine like embedded Firebird which with the use of few .dlls it works nicely with Access without any deployments and setups...
This requires a much bigger picture of Access and what users need.
Microsoft Access should continue development as a 2-tier system: embedded database + frontend=single file. MS SQL should be extended so that it fully supports a MS Access frontend where all MS Access objects (incl. macros & code) are stored on MS SQL. The MS Access database backend could be morphed into a lite version of MS SQL server (MS SQL LocalDB) which imitates ACE in several ways. An MS Access/MSSQL should create the required data files embedded into a single user file and grow 4MB at a time (like vhdx). A web server based application server could be created to fully support serving the Access frontend based on the MS SQL backend.
MS has added other options and combinations before (eg. ASP to MSSQL, sharepoint, Lightswitch) but nothing has lasted that was suitable for migrating an MS Access database+frontend to anything else without losing functionality (ie. database is migrated but frontend is left behind).
I say "nothing suitable" because options fail due to one or more problems: cost of solution; complexity of solution; obsolete software/features/services; recreating the frontend (GUI) with new code to support business rules and automation; slower performance; editing queries and recreating reports.
We want a long term roadmap where users can create, upscale and update deployments to grow with a business without radical redesigning or dead ends. We don't like >2GB software installations just to read 100MB of data where a 60MB program used to work. Aim for quality not quantity.
Mark Burns commented
The actual reasons for the 2GB file size limit on databases has little or nothing to do with the physical memory addressing or segregating data & code spaces in memory at all. Instead, it had everything to do with the limits of the ORIGINAL MS-DOS/WIndows FAT Filesystems (which was originally 2GB max size for a HDD volume). The JET (Blue and Red versions - which were both "property" of the Windows OS development group internally at MS, by the way) Engine's internal architecture was mapped to the same limits. It was simply never re-architected in any way to follow the larger limits of next-gen FAT or NTFS filesystems. Ergo, the 2GB limit persists with us still today due to the 2GB pointer-space limits of the old FAT filesystem; much like an insect stuck in a too-small exoskeleton from its earlier days.
Beyond this basic factoid, the reason it was never addressed was a business decision made long ago by Sr. MS Managers to prioritize SQL Server as the database platform of the future, and "freeze" the capabilities of JET where they were. With the ACE engine, the MS-Access Dev team COULD now, finally decide to do something about this issue, but they likely have the same set of ankle chains on them as they've been forced into those many, many years ago.
So far, nobody has been able to twig the more senior-level managers above them that there are new realities and dynamics in the marketplace today, and larger local file-based data stores than 2GB are becoming increasingly needed - WITHOUT incurring the administrative overhead of an MS SQL Server installation (especially a problem for enterprise customers who have seriously locked-down software installation rights enforced by their Domain Admins and policy settings).
Three cheers to the brave MS-Dev-Team soul who is able to finally take that debate with more-senior-level folks and finally win it for us all!
I posted some forums to get support from the idea - and many of the knowledgeable individuals contributed to discussions on the suggestion. One particular post explains the hardware issue related to the suggestion. I can see why Microsoft might find this unattractive but still think it would be a great change which will probably have to happen at some point.
I agree with the above. Whilst SQL Server is appropriate for many purposes, not all my clients feel confident enough to deploy it. For them, Access is still the favoured option. Larger capacity would be useful
A note that I should have added to this initial statement - my back of the envelope analysis suggests that if you add up the previous 2 big request polls for the same idea it comes to 6th in the ranking. I know populism isn't always a great measure of validity but....