MLH wrote:
The reason I am exploring this topic is because I
was recently surprised to learn that a single memo
field in a single table in a database comprised over
half my mdb file size. The database has 22 tables.
Its initial compacted size was 42 megs.
I deleted the memo field data in the one table and
compacted the database again. The final size was
20 megs. Of course I had to revert to my backup
copy to restore the data to the memo field because
I do need it there. My heightened awareness of each
table's individual contribution to the total filesize has
resulted in me investigating other MDB's for tables
that may be housing data that isn't critical and can
be purged. I have LOTS of mdb's and many more
tables. Opening every single table in every single
MDB to make a subjective evaluation of the byte
count occupied by its contents is simply not an option.
Store this type of large object in its native file format outside of
Access and instead store its file and path so you can refer to it.
This is a common technique in Access to handle binary objects (large and
small).
Jet doesn't have extensive binary support needed to handle these type of
objects in the same manner as a server-based product like Oracle, etc.
Some seasoned pros might also recommend that Memo field values be kept
in separate tables or even seperate .mdb files since they are
susceptible to corruption and doing so may prevent corruption of all
related data and limit it to the memo field store. I don't have the
experience to validate that observation.
--
'---------------
'John Mishefske
'---------------