Actually, did you try converting that application from mdb to accDB format? The size of the file should not change in a signification way. (so, please try such a conversion).
In fact, to my knowledge the storage size is NOT any different. In effect a conversion is really much the same as a compact + repair. The overall resulting file MIGHT be a bit smaller due to code not being compiled. (the compiled binary part of code is removed during this process).
So, as a general rule, file size from Access 2000 onwards when using an mdb file, or an accDB file SHOULD NOT be different in size.
Of course converting from an access 97 file is a different matter. The pre-2000 formats did not support international character sets. And pre 2000 did NOT store text data as uni-code (but used ASCII).
So in theory upgrading from an older 97 format to 2000 and beyond could in fact increase your storage requirements by a FACTOR OF TWO TIMES. That means for each text character saved in the database will require two characters (that is how uni-code works).
To make up for this rather large increase in data storage requirements and without a correspond increase in the 2 gig file, then data compression technology was introduced in Access 2000 and beyond. This compression is NOT to be confused with compact + repair.
You can enable/disable compression on a column by column basis. Eg here:
So regardless of this issue, YOU SHOULD NOT be seeing ANY DIFFERENCE file size between a 2003 format file as compared to an accDB file size format. The storage requirements are the same, and the ONLY REASON why you are seeing a difference is due to the compact + repair and removal of extra "JUNK" that occurs during a conversion. (you can in fact get the SAME file size without a conversion by doing a C+R and also a de-compile and then another C+R).
So, for example, try converting the reduce sized mdb to a accDB – you notice that the file did not increase – and in fact as noted, you will OFTEN see a decrease in size during this conversion from that mdb to accDB. So this again is due to junk removal during conversion here – not the fact that you are using an mdb file as oppose to an accDB file AND THUS NOT DUE TO USING mdb vs. accDB.
As noted, the only exception to the above rule is pre-2000 format mdb. They do not have uni-code and thus text data should in theory use DOUBLE the storage with the NEW format. However, with the introduction of compression technology for those text columns, then in fact your store requirements don't increase – and in fact in some cases could be a bit less than compared to pre-2000 and not having compressed columns. The reason for this is that text data tends to compress (zip up) very well indeed.
As for what file format to use? I would use the default file format for the given version. So, in your case, I would suggest it best to use accDB.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
