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.com> wrote in message
news:Ki*******************@newssvr29.news.prodigy. 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.com> wrote in message
news:zy******************@newssvr25.news.prodigy.c om...

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@localhost> wrote in
news:40***********************@auth.uk.news.easyne t.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.com> wrote in
news:Ms******************@newssvr29.news.prodigy.c om:

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

[deko]

> 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

Is this what you mean:

In the table I have "memNoteText", and in the form I have an unbound field
"txtNoteText" I have an OK button with:

Me!memNoteText = Me.txtNoteText

That puts the contents of the textbox into the table.

And Form_Open has this:

Me!txtNoteText = Me!memNoteText

Which populates the textbox with the data in memNoteText

Seems to be working. I thought I had to have a text box on the form to do
that. I didn't know I could reference the table field with the "Me.Field"
syntax without a textbox on the form - thanks for the tip!

[Trevor Best]

deko wrote:

that. I didn't know I could reference the table field with the "Me.Field"
syntax without a textbox on the form - thanks for the tip!

Ooooh don't go down the route of the dot, imagine a field called "Name",
what do think me.name would return?

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

[Trevor Best]

David W. Fenton wrote:

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

Why?

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

[deko]

> Ooooh don't go down the route of the dot, imagine a field called "Name",

what do think me.name would return?

10-4 - dot notation should be dropped like a piece of used dental floss...
still, the cool thing is being able to populate a table field *without a
corresponding bound control* on the form - as in Me!memNote = Me!txtNote
(where Me!txtNote is an unbound text box and Me!memNote is a memo field in
the table)

As for all those controls hiding behind the "More Controls" button on the
toolbox toolbar (e.g. the RTF textbox control) - Are these used often? How
are they different from the other "regular" controls? I couldn't get that
Microsoft Rich Textbox Control to work. I noticed some chart controls in
the list that looked interesting. Do you know of any code samples that
demonstrate how to use these?

[Trevor Best]

deko wrote:

As for all those controls hiding behind the "More Controls" button on the
toolbox toolbar (e.g. the RTF textbox control) - Are these used often? How
are they different from the other "regular" controls? I couldn't get that
Microsoft Rich Textbox Control to work. I noticed some chart controls in
the list that looked interesting. Do you know of any code samples that
demonstrate how to use these?

They're different in that they're not actually part of Access, just
controls that are registered. Code samples, if there any would be
numerous and diverse, each control will have it's own properties and
methods. Apart from .top, .left, etc you get with all controls the RTF
control has a .Text and .TextRTF properties, use the former for a
similar function to a textbox without the 64K limit.

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

[David W. Fenton]

"deko" <de**@hotmail.com> wrote in
news:kd******************@newssvr25.news.prodigy.c om:

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
Is this what you mean:

In the table I have "memNoteText", and in the form I have an
unbound field "txtNoteText" I have an OK button with:

Me!memNoteText = Me.txtNoteText

That puts the contents of the textbox into the table.

And Form_Open has this:

Me!txtNoteText = Me!memNoteText

Which populates the textbox with the data in memNoteText

I'd populate Me!txtNoteText in form's OnCurrent event, and save the
changes in the AfterUpdate even of the unbound control.
Seems to be working. I thought I had to have a text box on the
form to do that. I didn't know I could reference the table field
with the "Me.Field" syntax without a textbox on the form - thanks
for the tip!

Well, I'd use Me!Field, but, yes, of course it works.

It has always worked, from the earliest days of Access.

That is, the form has a Controls collection and a Fields collection.
The latter is all the fields in the underlying recordset, not all of
which need appear in controls on the form.

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

[David W. Fenton]

Trevor Best <nospam@localhost> wrote in
news:40***********************@auth.uk.news.easyne t.net:

David W. Fenton wrote:

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

Why?

Because of DLL Hell.

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

[deko]

> That is, the form has a Controls collection and a Fields collection.

The latter is all the fields in the underlying recordset, not all of
which need appear in controls on the form.

Hmmm... I guess that means I don't need to use a hidden (i.e. visible = no)
text box on a form to hold stuff like ID numbers. I was in the habit of
putting hidden text boxes on the form whenever I needed to do stuff like
this:

Private Sub Categories_Click()
Dim intCatId As Integer
intCatId = Me!Cat_ID '<<== Cat_ID text box on form
CallSomeFunction (intCatId)
End Sub

I've been experimenting - even the Link Child/Link Master properties for Sub
Forms/Main Forms seems to work without the hidden text box - Is there ever a
time when I need to use a hidden text box on a form to hold an ID number?

[Trevor Best]

deko wrote:

I've been experimenting - even the Link Child/Link Master properties for Sub
Forms/Main Forms seems to work without the hidden text box - Is there ever a
time when I need to use a hidden text box on a form to hold an ID number?

The LinkChild will work only with columns from the recordsource and not
textboxes AFAIK.

I usually have a hidden control on each form called txtPK, which is
bound to the primary key field. It just makes it easier that I know I
can reference txtPK rather than have to remember what the primary key is
actually called.

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

[Lyle Fairfield]

"deko" <de**@hotmail.com> wrote in
news:KE*****************@newssvr27.news.prodigy.co m:

That is, the form has a Controls collection and a Fields collection.
The latter is all the fields in the underlying recordset, not all of
which need appear in controls on the form.

Hmmm... I guess that means I don't need to use a hidden (i.e. visible =
no) text box on a form to hold stuff like ID numbers. I was in the
habit of putting hidden text boxes on the form whenever I needed to do
stuff like this:

The recordsource's fields are saved when the form is saved. If you change the
form's recordsource at run time you may very well have to use a hidden
control to interact with a new field. If the field is vital I always use a
hidden field to ensure that, if in some new mainfestation of the form I
change the recordsource, the reference to the field is always there.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Lyle Fairfield]

Lyle Fairfield <Mi************@Invalid.Com> should have written in
news:Xn*******************@130.133.1.4:

If the field is vital I
always use a hidden control

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[David W. Fenton]

"deko" <de**@hotmail.com> wrote in
news:KE*****************@newssvr27.news.prodigy.co m:

That is, the form has a Controls collection and a Fields
collection. The latter is all the fields in the underlying
recordset, not all of which need appear in controls on the form.

Hmmm... I guess that means I don't need to use a hidden (i.e.
visible = no) text box on a form to hold stuff like ID numbers. I
was in the habit of putting hidden text boxes on the form whenever
I needed to do stuff like this:

Private Sub Categories_Click()
Dim intCatId As Integer
intCatId = Me!Cat_ID '<<== Cat_ID text box on form
CallSomeFunction (intCatId)
End Sub

I've been experimenting - even the Link Child/Link Master
properties for Sub Forms/Main Forms seems to work without the
hidden text box - Is there ever a time when I need to use a hidden
text box on a form to hold an ID number?

Heavens yes!

However, there's one circumstance where you'll want to use a hidden
field -- if you're referring to a field in the parent from from a
subform. If you don't use the hidden control, in A2K and higher, you
may end up with the dreaded Error 2486 (Google on it, and my email
address with the ".invalid" and you'll see the hair pull I went
through to diagnose it).

That is, if in a subform, you refer to:

Me.Parent!FieldNameFromRecordSource

you may run into the error, which is actually due to a problem in
MSCVRT.DLL.

The problem does not occur if the reference is to a control with a
control source pointing you your desired field.

That is the only situation in which I ever use hidden fields.

Mostly.

I sometimes have ID fields on the form that I have visible during
development but hide for distribution.

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

[Dimitri Furman]

On Apr 27 2004, 02:10 pm, Lyle Fairfield <Mi************@Invalid.Com> wrote
in news:Xn*******************@130.133.1.4:

Lyle Fairfield <Mi************@Invalid.Com> should have written in
news:Xn*******************@130.133.1.4:

If the field is vital I
always use a hidden control

Haven't seen you post verse before. Sounds like an incantation. Nice.

--
remove a 9 to reply by email

[Bob Quintal]

"deko" <de**@hotmail.com> wrote in
news:KE*****************@newssvr27.news.prodigy.co m:

That is, the form has a Controls collection and a Fields
collection. The latter is all the fields in the underlying
recordset, not all of which need appear in controls on the
form.

Hmmm... I guess that means I don't need to use a hidden (i.e.
visible = no) text box on a form to hold stuff like ID
numbers. I was in the habit of putting hidden text boxes on
the form whenever I needed to do stuff like this:

Private Sub Categories_Click()
Dim intCatId As Integer
intCatId = Me!Cat_ID '<<== Cat_ID text box on form
CallSomeFunction (intCatId)
End Sub

I've been experimenting - even the Link Child/Link Master
properties for Sub Forms/Main Forms seems to work without the
hidden text box - Is there ever a time when I need to use a
hidden text box on a form to hold an ID number?

How about an unbound parent form with two subforms, selecting a
record in the first sets a hidden control which is the parent
link.for the second form.

[David W. Fenton]

Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:

If the field is vital I
always use a hidden control

Care to unpack your reasoning, there?

How could one field be "vital" and one could be, what, irrelevant?

If it needs a control on the form, it should have a control on the
form, visible or not.

But if there isn't a need for a control, I see no reason or benefit
from putting on there on the form.

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