Give us LARGER DATABASES
The 2GB file size limit is becoming more of a problem. Given the current size limits on filesystems, and the increasing use of larger and larger datasets, this size limit is beginning to cramp the style of Access Developers and customers alike. SQL Server's local file store supported 10GB file sizes - why not make Access do the same?
Thanks for the post.
SQL back-end is how we support larger databases – Access can still be used for the forntend with all it’s goodness and value.
Michael Krispin commented
I use also Access for an analyze tool with Text File Sizes of 2GB++ as an export of a discrete control system (DCS) . I put already all tables in own DB and use one DB as main with all tables linked. I don't mind the performance, but I often hit the 2GB Limit and I was surprised to see this even under 64Bit. SQL Light might work but have other problems with administrations.
Another problem is: How to copy/exchange to other PC a project with SQL Light as BE?
Why is there a limit of 2GB even with 64Bit and 16GBRam++ ???
MS Access is the right tool for my application, even to build just some other queries as the predefined from my program for specific searches is very easy.
As datasets get increasingly large I think Microsoft should think about increasing this limit. I understand that SQL Express is the sensible way to go for some individuals in working environments they will not be allowed to install work with SQL Server an increased file size limit would assist in such circumstances and give them more time to lobby for proper solution.
One final point to be made here:
In some enterprise situation where local admin permissions are normally restricted, installing a local SQL Server instance may also NOT BE POSSIBLE, be it freely available or not!
Ergo, another alternative may yet be needed.
Not to beat a dead horse, but there is one final point to be made on this issue - even the FREE SQL Server STILL DOES have a Local Storage file size limit of 10GB, and there is no means I'm aware of to estimate the sizes for incoming data for some import processes (taking indexes into account, at least). Ergo - even SQL Server can surprise one with "out of space" types of errors.
Yoshiki-Tojo from Tokyo, Japan. commented
I could not understand the 2GB. In Access Web on the Share point online , that limit is
1 GB. Is this limit on the desktop Access ?
DAVID SAVAGE commented
Thank you for mentioning that there is a free SQL server BE. I hadn't heard of a free version of this, so what web site can I go to (or email address of someone I can contact), to download ? If this SQL server BE really is free, then that would be really awesome to eliminate the 2gig limit. I first discovered the Access UserVoice just today.
Making SQL Server the default data store would solve this.
Matrix Development, llc commented
Increasing the number of "table handles" (Number of open tables) from 2048 would also be appreciated.
I have had occasion in my previous positions to write an application to scrape data from some VERY LARGE mainframe inventory reporting into Access for certain analysis purposes. Depending upon the initial queries, the results could easily overmatch the 2GB file size limit, so I had to create a means of estimating the size of the result set(s) and PAGING the results across multiple access database files. It worked very well, but sometimes there were SEVERAL resulting database files to support the required analysis queries. SQL Server/Oracle/MySQL et. al were forbidden options for this situation, so this was a necessary solution at the time.
I point this out just to underline that different folks use Access differently, and there ARE situations where the 2GB size has negative impact.
What we lose by splitting into different Back-end .accdb files there is the automatic enforcement of Relational Integrity across the data model. With larger database files (and I am sensitive to your concerns around compaction, believe me), we could more easily accomodate both larger datasets, and RI enforcement (as well as some other wishlist items I noted).
Alan Cossey commented
Using a .accdb back end of anywhere near the current limit of 2GB is, I think, rather brave of people. I would go nowhere near that limit for a file-based back end. At even a quarter of that size I get nervous (and have to start fielding problems with customers). Compaction sometimes goes wrong. Horrible.
This request also meshes with my other request for a Security and IT Governance API toolset that integrate with the ACE Database Engine. The Security data could be a part of the larger databases - still contained in the same database file in an encrypted section of the file.
I agree that a larger file size limit would be useful.
Splitting the back end into multiple files, as another commenter suggested, has significant drawbacks.
1. Relationships with referential integrity cannot be enforced between tables in separate databases, THis makes it more difficult to maintain data integrity.
2. In my experience, queries with joins between linked tables from separate databases run much more slowly than if the source tables are in the same database.
Richard Gray commented
Another work around this is split the Front end / Backend and Use Multiple back ends.
For some of my applications I have 1 table in an MDB... while others are home to many. It speeds up data access and virtually eliminates the 2gb issue. Hope this helps
Anders Ebro (TheSmileyCoder) commented
Personally I feel like the 2gb limit makes sense in more than 1 perspective.
Allowing people to go beyond that limit seems to just be asking for trouble. If you really need that much space, I think its time to consider a SQL server installation.
Frank Rotolo commented
@Mark, a free SQL Server BE solves that problem. Users love the Access FE, not the native Jet/ACE BE. They're only useful when no other options exist.