Give us the ability to do a Partial Compress & repair on the current database
Give us the ability to request a partial Compress & Repair at the table level within an opened database.
It should not be impossible to compress database records and disk pages at the table level, bypassing and freeing up any space no longer needed for deleted data records and index entries.
Given Microsoft refusal to consider revamping the database Access database files to allow for larger databases, then giving us the ability to better manage the available 2GB of space by making incremental requests to compress & repair thins partially at the table-level could be a meaningful compromise. This could allow us to enable "paging-style" approaches with which to attack larger data files and import/export tasks which are presently beyond the reach of MS-Access databases.
Mark Burns commented
We need better database file formats, pure and simple. Whether it is because we really need:
1) Bigger FILE BASED databases (many enterprise users are restricted completely from being able to install even free local SQL Server instances, so your "go with a bigger, better server-type database for your back-end" is a complete non-starter for those departmental-level users who may have small(-ish) or transient needs for dealing with larger data files - without going through the expensive, time-consuming headaches of requesting actual server instances and sufficient space from their IT bureaucracy (never mind the whole budget-cycle conversation, too). So, there is still a need here!
2) My other previous requests for better security - enforced at the database engine level - including IT/data governance issues like: "do you have permission to create a new database?", "do you have permission to import or export this data?", "are you allowed to copy/paste to this table/field?"
So, with other reasons like those (and many more other fairly good reasons to improve the database files/formats), adding in a request like mine for allowing us the ability to perform partial (table-level) database maintenance/space-reclamation/index-rebuilding is a worthwhile addition to the feature list.
Should the Access/ACE Engine team decide to radically remake the database file format for any of the above reasons, we will of course have to go thru the growing pains of getting the new database engine and data files into a fully debugged/stable point. We can all expect the obvious issues and pain points associated with that. However, you forget that we have had issues with database files and formats in recent times as well (especially backwards-compatibility-related issue types), so there really is no escaping this kind of problem anyway. So your objection to this request in order to avoid these issues is really rather pointless, isn't it?
Alphonse Giambrone commented
Personally, I don't think C&R should be messed with. It has worked well for many, many years and if they mess with it, who knows what bugs may be introduced. Once that happens, it could take years before it is fixed.
With data size approaching 2GB, I would store it in a more robust server database, rather than a file share database that could so easily be corrupted by a network glitch.
I love Access, but like anything else, it has it's limits.