Memo Field Limits???

[MJ]

I am running into a problem with memo fields on a form.

A Memo field can store up to 65,536 characters, right?

Yet in a memo field in one of my forms, limits the field to 2,046
characters. Can someone tell me what's up? My guess is that this is a
"feature" of Access. Is there a way to store above this limitation and what,
if any, risks are there for going above it?

Thanks,

 

[Dirk Goldgar]

I am running into a problem with memo fields on a form.

A Memo field can store up to 65,536 characters, right?

Yet in a memo field in one of my forms, limits the field to 2,046
characters. Can someone tell me what's up? My guess is that this is a
"feature" of Access. Is there a way to store above this limitation and
what,
if any, risks are there for going above it?

A memo field can hold up to 1GB of data if updatyed programmatically, or
65,535 characters if updated via a text box (since the maximum length of
text in a text box is 65,535 characters). So this is not what's limiting
your field.

Are you applying any formatting to the field or text box? That has been
known to limit things to 255 characters, but I haven't heard of a
2046-character limit. How have you verified this limit?

 

[MJ]

Dirk, There is NO formatting applied to the text. Yes, I have verified this
strange limitation several times. In fact there are two similar memo text
fields on this same form that act in exactly the same way.

The user can enter as much text as they wish to, but once the text exceeds
the 2,046 characters on save, it highlights the text box and does NOTHING.
Normally my users are not that verbose, but I had one yesterday and of course
now they are reporting this as a problem that needs fixing.

 

[Dirk Goldgar]

(re-posting, as my original reply hasn't appeared)

Dirk, There is NO formatting applied to the text. Yes, I have verified
this
strange limitation several times. In fact there are two similar memo text
fields on this same form that act in exactly the same way.

The user can enter as much text as they wish to, but once the text exceeds
the 2,046 characters on save, it highlights the text box and does NOTHING.
Normally my users are not that verbose, but I had one yesterday and of
course
now they are reporting this as a problem that needs fixing.

This is puzzling. What version of Access, and what database format? I just
verified that, in Access 2003, I can enter more than 4000 characters in a
text box bound to a memo field with no problem.

Is this a split database? If so, what is the format of the back-end? Is
there any code behind the form that might be affecting the behavior?

 

[Linq Adams via AccessMonster.com]

Posts about this problem appear from time to time, here and elsewhere, and
sad to say, I've never seen a resolution for it! Memo fields are not actually
stored in the table they're associated with. The table actually holds a
***pointer** to the memo field, with the field itself being stored in an
internal Access table. These pointers (which are also used to handle BLOBs)
are said to be particularly vulnerable to corruption, and some have
postulated that this is the cause of the problem.

I suppose you could first try deleting and replacing the textbox, and if
necessary, doing the same for the memo field itself.

 

[MJ]

Dirk,

We are using Access 2003 and the file format is Access 2002-2003 file format.

It does happen to be a split database, with all the programming and forms in
the front end and the data tables in the backend.

This database has been in use for over 2 years and I do not know of any
other time where we have run into thie issue. I am recommending that they
take care of it at the user level for now. However, I also know that in the
future I may well have to rebuild/create a "new" databases to take its place
and I would like to be able to get past this issue before it becomes an issue
again.

Thank you for your help.

 

[Dirk Goldgar]

(re-posting, as my original reply hasn't appeared)

Dirk,

We are using Access 2003 and the file format is Access 2002-2003 file
format.

It does happen to be a split database, with all the programming and forms
in
the front end and the data tables in the backend.

This database has been in use for over 2 years and I do not know of any
other time where we have run into thie issue. I am recommending that they
take care of it at the user level for now. However, I also know that in
the
future I may well have to rebuild/create a "new" databases to take its
place
and I would like to be able to get past this issue before it becomes an
issue
again.

I think I would try these options to try to figure out what's wrong:

1) Build a new copy of the table, load it from the original, bind the forms
to the new copy, and see if the problem persists.

2) Build a new copy of the table, *don't* load it from the original, bind
the forms to the new copy, and see if the problem persists.

3) Save and then recreate the form(s) to/from text, using the hidden
Application.SaveAsText and Application.LoadFromText methods. That ought to
remove any corruption in the forms. See if the problem persists.

4. Build brand new forms from scratch and see if the problem exists in them.
Start with a very simple form, and if that works, add to it until it matches
the original.

Thank you for your help.

You're welcome, although so far it seems the only help I can give is the
reassurance that it's not supposed to be this way.