On Fri, 26 Mar 2004 16:48:50 -0700, in
<
[email protected]>, John Vinson
[...]
Putting the data into a Memo
field is inefficient, makes the database prone to corruption, and
makes it harder to search for specific values. Might this not be
better as a one to many relationship to another table, in which each
"row" of information is a new record? These could be stored in Text
fields (if each row is less than 255 characters) and displayed on a
Subform.
I've recently built an application (well, it's still a work in
progress, actually), using Access 2000, that the user is employing for
(inter alia) the creation of time and materials Invoices (and
occasional Estimates).
An essential component - indeed requirement - for the user (an
independent electrical contractor whose volume is probably about 25 to
30 invoices per month) is that he have the flexibility to incorporate
somewhat lengthy descriptive WorkDetails on each Invoice or Estimate.
(The Estimates actually have two memo fields: one for lengthy details
and a summary, the latter of which also gets produced in the Invoice).
After reading John's post, I did a spot check of the details in the
50+ invoices he's generated to date, and as far as I can tell, none is
less than 255 characters (including spaces) - some are over 700. And
that's the good from what the user has told me, it is
conceivable that on occasion this may run to more than one page of
text (in 8pt Arial).
I was not aware of this alarming (but undocumented?!) feature of memo
fields when I began this project. If only I knew then what I know now
( I'm not sure what I would have done, but I digress ...
The details are extensive and comprised of complete sentences. While
there is some repetition of phrases (for which he's using the
time-saving text substitution feature of ActiveWords, an app that is
far superior to MS Autotext feature, IMHO) each invoice is different.
Using additional text fields does not seem like a viable solution -
since I don't imagine that the user would be thrilled about having to
count characters as he formulates the text of his details section. The
details do need to be stored for historical purposes, but do not need
to be searchable. This being the case, are there any alternatives
and/or preventive measures one can implement - beyond using compact
and repair before exiting on each use - to reduce the possibility of
corruption?
One thing that occurs to me is to move the memo fields out of the
tables in which they currently reside and into their own separate
tables, (the lengthy EstimateWorkDetails currently has its own table)
but I've no idea whether or not this will help.
Alternatively, would it be feasible to call Notepad or Word for
entering these details and automate the saving, linking etc? If so,
could you point me in the direction of coding I would need to
accomplish this elegantly and seamlessly both from a data entry
perspective and from the perspective of incorporating the .txt or .doc
file into the actual invoice report?
Or does the low volume make this a non-issue? (She says wistfully!) If
so, at what point would it be likely to become an issue? FWIW, the
..mdb is currently at 5.23 MB
In short, is this application doomed (it's working so well for him,
productivity-wise as well as data-entry-and-mining-wise!) to
inevitable corruption or can it be saved?!
I posted here at the beginning of Feb. when I began this project, and
have lurked back from time to time. Many thanks, again, to John for
pointing me in the right direction initially - and to John and others
for the invaluable lessons I've learned from their posts in the
interim. And optimistically thanks in advance for any suggested
solutions to this particular problem.
hro
