My back end, when compacted, it is about an eighth of the original size.
Is there a way so that the BE is compacted automatically when no users are accessing it? aside from waiting till everyone is done (or no longer) encoding data? should I save the be in mde format? (I have only made mdb files so far).
Normally, BE files don't bloat out too much to warrant a daily C&R... but monthly is pretty standard.
Hmm, there's an odd thing here then. A day ago, the backup file of the BE was around 8mb, when I to compacted it, it was only 1.8mb. (i forgot to check the size of the original be at that time)
The morning backup of the BE today (made around half an hour ago) and the original is only 3mb. So I'm taking into account the possibility that the original BE itself yesterday was 8mb in size.
While hard drive space isn't a big issue for me, I find it rather odd that the BE file would expand and shrink in no relation to the usage process (we're encoding data into the db every day, so I expected it to grow in size slowly, but not grow that fast, nor suddenly shrink again). What does compacting really do anyway?
As much as I'd love to attach the two files for comparison, the data contents of the BE are not to be shared.
Anyway, I guess I'll just stick to manually compacting the db. We have a weekly hdd to disc backup schedule anyway, might as well add that to the list of things to do.
During normal operation, is there temporary data being added to the BE and then removed? That will do it.
Is your BE only being used for data? No forms etc?
C&R essentially defragments the file. If you modify an object, the original is often just de-catalogued... the modified object is appended to the end of the file and the original is just a waste of space. C&R will reclaim that space. Well, actually, that's the compact part... the repair part does something different and doesn't really affect space.
When all is said and done, a back end is just an ordinary database which can be turned into a front end simply by executing a query. A query executed by a link is simply that. Reading between the lines on some research I had to Do to clear up a problem, I believe that there is quite a lot of caching and buffering going on and regular compacting is useful to reset everything. In databases with big tables or with many relationships (which generate informal indexes) there could be a performance issue if this is not done.