How many MEMO fields allowed in a table?

In Access2002, what is the maximum number of MEMO fields that I can have in a
single table, before getting an error?

Thanks!!

255, which is the maximum number of fields that you can have in one table.

--

Ken Snell
MS ACCESS MVP


"Dennis" wrote in message
...
In Access2002, what is the maximum number of MEMO fields that I can have
in a
single table, before getting an error?

Thanks!!

Dennis

Why do you ask? Is this a hypothetical question, or were you planning to
use multiple memo fields? If so, please describe why you think you
need/want multiple memo fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
In Access2002, what is the maximum number of MEMO fields that I can have
in a
single table, before getting an error?

Thanks!!

Unfortunately, my user is requiring that a table which is using many TEXT
fields (all set to 255 bytes), needs to be able to be more verbose in all of
them. Since 255 is my limit, my only choice is to use memo fields. However,
I've run into certain situations where I generated a "record too large" error
when using some number of them (in another application). That necessitated a
complete database redesign. I am HOPING to avoid that here, but I'm kind-of
doubtful.

"Jeff Boyce" wrote:

Dennis

Why do you ask? Is this a hypothetical question, or were you planning to
use multiple memo fields? If so, please describe why you think you
need/want multiple memo fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
In Access2002, what is the maximum number of MEMO fields that I can have
in a
single table, before getting an error?

Thanks!!

Access has a limit of (?)2,000 characters per record ... that would be why
multiple text fields at 255 characters each would exceed the limit.

If you'll provide a bit more specific description of the domain you are
working in, the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
Unfortunately, my user is requiring that a table which is using many TEXT
fields (all set to 255 bytes), needs to be able to be more verbose in all
of
them. Since 255 is my limit, my only choice is to use memo fields.
However,
I've run into certain situations where I generated a "record too large"
error
when using some number of them (in another application). That necessitated
a
complete database redesign. I am HOPING to avoid that here, but I'm
kind-of
doubtful.

"Jeff Boyce" wrote:

Dennis

Why do you ask? Is this a hypothetical question, or were you planning to
use multiple memo fields? If so, please describe why you think you
need/want multiple memo fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
In Access2002, what is the maximum number of MEMO fields that I can
have
in a
single table, before getting an error?

Thanks!!

I understand this. However, MEMO fields (at 65,535 characters max each) are
not handled the same way as other field types. So my question stands: how
many MEMO type fields can I have in a table, before I'll get the "record too
long" error? If no one knows, then I'll have to experiment, but I was hoping
for a quicker answer than that.


"Jeff Boyce" wrote:

Access has a limit of (?)2,000 characters per record ... that would be why
multiple text fields at 255 characters each would exceed the limit.

If you'll provide a bit more specific description of the domain you are
working in, the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
Unfortunately, my user is requiring that a table which is using many TEXT
fields (all set to 255 bytes), needs to be able to be more verbose in all
of
them. Since 255 is my limit, my only choice is to use memo fields.
However,
I've run into certain situations where I generated a "record too large"
error
when using some number of them (in another application). That necessitated
a
complete database redesign. I am HOPING to avoid that here, but I'm
kind-of
doubtful.

"Jeff Boyce" wrote:

Dennis

Why do you ask? Is this a hypothetical question, or were you planning to
use multiple memo fields? If so, please describe why you think you
need/want multiple memo fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
In Access2002, what is the maximum number of MEMO fields that I can
have
in a
single table, before getting an error?

Thanks!!

Feel free to post back with what you discover.

You might also try checking on-line with search terms like "MS Access Memo
field".

It seems you've already determined what solution will be used.

I wasn't asking to be nosy. It may be that the "solution" doesn't match the
"problem"...

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
I understand this. However, MEMO fields (at 65,535 characters max each) are
not handled the same way as other field types. So my question stands: how
many MEMO type fields can I have in a table, before I'll get the "record
too
long" error? If no one knows, then I'll have to experiment, but I was
hoping
for a quicker answer than that.


"Jeff Boyce" wrote:

Access has a limit of (?)2,000 characters per record ... that would be
why
multiple text fields at 255 characters each would exceed the limit.

If you'll provide a bit more specific description of the domain you are
working in, the newsgroup readers may be able to offer alternate
approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
Unfortunately, my user is requiring that a table which is using many
TEXT
fields (all set to 255 bytes), needs to be able to be more verbose in
all
of
them. Since 255 is my limit, my only choice is to use memo fields.
However,
I've run into certain situations where I generated a "record too large"
error
when using some number of them (in another application). That
necessitated
a
complete database redesign. I am HOPING to avoid that here, but I'm
kind-of
doubtful.

"Jeff Boyce" wrote:

Dennis

Why do you ask? Is this a hypothetical question, or were you planning
to
use multiple memo fields? If so, please describe why you think you
need/want multiple memo fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Dennis" wrote in message
...
In Access2002, what is the maximum number of MEMO fields that I can
have
in a
single table, before getting an error?

Thanks!!

On Thu, 13 Sep 2007 11:38:01 -0700, Dennis
wrote:

Unfortunately, my user is requiring that a table which is using many TEXT
fields (all set to 255 bytes), needs to be able to be more verbose in all of
them. Since 255 is my limit, my only choice is to use memo fields. However,
I've run into certain situations where I generated a "record too large" error
when using some number of them (in another application). That necessitated a
complete database redesign. I am HOPING to avoid that here, but I'm kind-of
doubtful.

Memo fields will PROTECT you from the "record too large" error. It's triggered
when the total size of a record exceeds 2000 characters - but memo fields
count only 16 characters toward that limit, no matter how large the memo
field.

HOWEVER... memos have disadvantages. They cannot be indexed; sorting will
truncate them to 255 bytes; they can't be used in relationships; searching can
be slow.

The fact that each record "has many text fields" and that you're pushing the
"record too large" limit worries me, though! It's quite possible that you have
a one (record) to many (comments) relationship embedded in each record of this
table, and that a one to many relationship to a Comments table such as:

Comments
CommentID Autonumber Primary Key
ParentID foreign key to your table
Comment Memo
CommentDate Date/Time, default Now()
CommentBy text, default CurrentUser()

might be preferable.

John W. Vinson [MVP]

If so, then Memo fields must be excluded as I have records with two
Memo fields each in excess of 3,000 characters.


On Thu, 13 Sep 2007 13:26:52 -0700, "Jeff Boyce"
wrote:

Access has a limit of (?)2,000 characters per record ... that would be why
multiple text fields at 255 characters each would exceed the limit.

If you'll provide a bit more specific description of the domain you are
working in, the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Why would I want memo fields to be indexed? Why might I want to sort
a memo field? I havealmost no practical experience, and I really am
wondering because I have read this many times before. I have a
newspaper obituary database with 4,000 records and two memo fields,
one for the German text and another for the English translation. I
did a fuzzy search for records matching parts of three words and it
took less than a second. SQL Server Express took just slightly
longer. I suspect that my tables are just too tiny to detect how
slow a memo field search can be.

On Thu, 13 Sep 2007 16:30:02 -0600, John W. Vinson
wrote:

HOWEVER... memos have disadvantages. They cannot be indexed; sorting will
truncate them to 255 bytes; they can't be used in relationships; searching can
be slow.