memo fields - asking for trouble?

[deko]

I have an mdb (2002 file format) that uses memo fields extensively. I've
read a lot about how problematic memo fields can be and that avoiding them
is a good idea. But I'm stuck with them and was wondering if there are any
best-parctices for optimizing or mitigating the risk of using them. Here's
what one table looks like:

NoteText - Memo
NoteDate - Date/Time (indexed, dupes ok)
NoteBrief - Text (length = 254)
Entity_ID - (Long, Foreign Key, indexed, dupes ok))
Note_ID - (Autonumber, Long, Primary Key)

This table is getting awfully big - would putting the NoteText field in it's
own table (so that's the only thing in the table) help, or does it really
make a difference?

[Allen Browne]

Don't worrry about it.

While there is a slightly increased chance of corruption, use them when you
need it. Avoiding them is about as useful as building a car with doors,
since cutting doors in the side weakens the frame of the vehicle. If you
need them, build them in.

The table you describe is not a very big table, unless you have millions of
records in it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <de**@hotmail.c om> wrote in message
news:Ki******** ***********@new ssvr29.news.pro digy.com...

I have an mdb (2002 file format) that uses memo fields extensively. I've
read a lot about how problematic memo fields can be and that avoiding them
is a good idea. But I'm stuck with them and was wondering if there are any best-parctices for optimizing or mitigating the risk of using them. Here's what one table looks like:

NoteText - Memo
NoteDate - Date/Time (indexed, dupes ok)
NoteBrief - Text (length = 254)
Entity_ID - (Long, Foreign Key, indexed, dupes ok))
Note_ID - (Autonumber, Long, Primary Key)

This table is getting awfully big - would putting the NoteText field in it's own table (so that's the only thing in the table) help, or does it really
make a difference?

[deko]

> Don't worrry about it.

Thanks - hopefully there is some added stability with the 2002 file format
as well.

[Allen Browne]

I'd be surprised if there was much difference in the stability of the A2002
format w.r.t. the A2000 format.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <de**@hotmail.c om> wrote in message
news:zy******** **********@news svr25.news.prod igy.com...

Don't worrry about it.

Thanks - hopefully there is some added stability with the 2002 file format
as well.

[Trevor Best]

Allen Browne wrote:

I'd be surprised if there was much difference in the stability of the A2002
format w.r.t. the A2000 format.

I'd doubt it, it's the same version of Jet.

One thing I used to do was to have a table without memo fields then
another table linked on a 1:1 relationship with the memo field in, that
way if the recored ever got lost to corruption I'd only lose the memo
data and not the rest of the record.

Although I'd pojn t out that memo corruption was only really a problem
for me in versions 2.0 and 97 (I didn't use 95 at all) but then by the
time I got to 2000, I was using SQL Server back-ends so I don't know if
Jet4 is any better or worse for that sort of thing.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[David W. Fenton]

Trevor Best <nospam@localho st> wrote in
news:40******** *************** @auth.uk.news.e asynet.net:

Allen Browne wrote:

I'd be surprised if there was much difference in the stability of
the A2002 format w.r.t. the A2000 format.

I'd doubt it, it's the same version of Jet.

One thing I used to do was to have a table without memo fields
then another table linked on a 1:1 relationship with the memo
field in, that way if the recored ever got lost to corruption I'd
only lose the memo data and not the rest of the record.

Although I'd pojn t out that memo corruption was only really a
problem for me in versions 2.0 and 97 (I didn't use 95 at all) but
then by the time I got to 2000, I was using SQL Server back-ends
so I don't know if Jet4 is any better or worse for that sort of
thing.

The original release of A2K was prone to it. I think it was Jet that
was to blame. By Jet 4 SP6, it was no longer an issue.

The easiest thing to do for anyone worrying about memo field
corruption is to edit them unbound. That is, use an unbound control
for display/editing, and use the AfterUpdate event of the unbound
control to update the underlying field.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[deko]

> The easiest thing to do for anyone worrying about memo field

corruption is to edit them unbound. That is, use an unbound control
for display/editing, and use the AfterUpdate event of the unbound
control to update the underlying field.

But doesn't this limit the input to 255 characters? The reason I'm using
the memo fields is because users want to dump in pages of text. Can this be
done in an unbound field?

[Trevor Best]

deko wrote:

The easiest thing to do for anyone worrying about memo field
corruption is to edit them unbound. That is, use an unbound control
for display/editing, and use the AfterUpdate event of the unbound
control to update the underlying field.

But doesn't this limit the input to 255 characters? The reason I'm using
the memo fields is because users want to dump in pages of text. Can this be
done in an unbound field?

No reason why not, I thought a textbox's limit was 64K. I get a lot
morte in there usinbg an RTF control and the method David pointed out
although it's updating another table as say on a purchase order I will
have several long texts configured to the client's requirements and of
course they want to be able to pretty the text up a bit too, which the
RTF control allows. Actually I use Daniel Paull's Richy
(http://www.vb4all.net/products.asp?ID=2) which is based around it with
a lot more functionality.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[deko]

> No reason why not, I thought a textbox's limit was 64K. I get a lot

morte in there usinbg an RTF control and the method David pointed out
although it's updating another table as say on a purchase order I will
have several long texts configured to the client's requirements and of
course they want to be able to pretty the text up a bit too, which the
RTF control allows. Actually I use Daniel Paull's Richy
(http://www.vb4all.net/products.asp?ID=2) which is based around it with
a lot more functionality.

Thanks for the tip - I looked at the Richy control. It's a bit more than I
need right now, but sounds good.

As for using an RTF control, can I use the Microsoft Rich Textbox Control,
version 6.0? I found this by clicking on the "More Controls" icon (last on
the right of the toolbox toolbar in Design View). Lot's of controls here
I've never used.

So I dropped that RTF control into a form - still experimenting.. . I assume
it's supposed to behave differently than a regular text box? In any case,
if I use an RTF control with David's suggested method (unbound control), how
do I update the table?

DoCmd.RunSQL ("UPDATE tblNotes SET [NoteText] = " & Me!rtfNote & " WHERE
Note_ID = " & Me!Note_ID)

I tried this with a standard text box - it trys to put the entire contents
of the textbox in the SQL statement, which obviously won't work with the RTF
control....

[David W. Fenton]

"deko" <de**@hotmail.c om> wrote in
news:Ms******** **********@news svr29.news.prod igy.com:

In any case,
if I use an RTF control with David's suggested method (unbound
control), how do I update the table?

Using a rich-text control of any kind is asking for trouble.

Unbound controls do not have the 255-character limit you assumed, so
there's no issue with using a plain old unbound text-box.

And I wouldn't do the updates in SQL, unless they were stored in a
different table. All I'd do is include the memo field in the form's
recordsource, and just not bind the control. For updates, something
like this in the control's AfterUpdate event:

Me!MyMemo = Me!fldMyMemo

I've done it many times in the past. It works just fine.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc