How many MEMO fields allowed in a table?

[Dennis]

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

Thanks!!

 

[Ken Snell \(MVP\)]

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

 

[Jeff Boyce]

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]

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]

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]

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]

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

 

[John W. Vinson]

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]

 

[Michael Gramelspacher]

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

 

[Michael Gramelspacher]

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.

 

[fredg]

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

From Access Help + Specifications + Access Specifications + Tables

Number of characters in a record (excluding Memo and OLE Object
fields) 2,000

 

[John W. Vinson]

Why would I want memo fields to be indexed?

I can get by perfectly well without indexes on them... but the question comes
up surprisingly often in the newsgroups.

John W. Vinson [MVP]

 

[Jamie Collins]

Why would I want memo fields to be indexed?

I have almost no practical experience, and I really am
wondering because I have read this many times before.

One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns.

One government agency schema I must work with has a wide key; let's
say it's up to 1000 characters. In Jet, this column would be best IMO
as a MEMO column (yes I know it's blob data but surely that's better
than scaling my own NCHAR/NVARCHR columns?)

Values are unique in the domain so naturally I'd want a UNIQUE
constraint in my db. The SQL DBMSs I work with all use indexes to
implement such constraints, as does Jet. Also, I have to search, join,
etc on this column so why _wouldn't_ I want to index it if possible?
Hint: they agency chose such a wide key because the number of entities
in the domain is vast (they have since realized they didn't need such
a wide key but the seed has been sown) and users could have literally
millions of values in the db...

...SQL Server Express...

I'm glad *you* mentioned that <g>. In SQL Server I can create a
calculated column to store a hash of the key value with a constraint
to ensure the hash is a match for the value, and put an index (dupes
allowed, obviously) on the hash column. I can then use both the value
column and the hash column in a trigger to ensure uniqueness and the
index on the hash column yields acceptable performance. Obviously, the
wise may use both columns in regular SQL JOINs, WHERE clauses etc to
ensure the index on the hash gets used. This is partially achievable
in Jet (e.g. the hash column and index), more so in Access (e.g.
validation rule to ensure the hash is a match) but not everything
(e.g. the trigger to ensure uniqueness).

Jamie.

--

 

[Jamie Collins]

memos have disadvantages... sorting will
truncate them to 255 bytes

Picky, I know, but yours is a misstatement. To test:

CREATE TABLE Test
(
memo_col MEMO NOT NULL
)
;
INSERT INTO Test (memo_col) VALUES
('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890_JohnV')
;
INSERT INTO Test (memo_col) VALUES
('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890_Jamie')
;
SELECT memo_col AS result1, LEN(result1) AS result1_bytes
FROM Test
ORDER BY memo_col
;

result1_bytes returns 306 for both rows. Conclusion: sorting has not
caused truncation.

Jamie.

--

 

[Michael Gramelspacher]

Jamie, a single-column key that is 1,000 characters wide seems absurd
in my mind.

NVARCHAR and NCHAR are not recognized by most people as Access
datatypes. And in SQL Server their length can be 4,000 characters
according to Books on Line. An Access Text datatype is limited to 255
characters.

I guess my vision does not extend much beyond the Access world. And I
am still left wondering why I might want to index or sort a memo
column.

 

[mscertified]

One thing I have run into a few times is when you do a UNION join between 2
tables that contain a memo column, the memo column gets truncated. There are
ways around this but it can trip you up and may not be detected until the bug
has existed for a long time when someone notices the truncation.

 

[Jamie Collins]

Jamie, a single-column key that is 1,000 characters wide seems absurd
in my mind.

I
am still left wondering why I might want to index or sort a memo
column.

I would hope it doesn't need too much thought/imagination to find an
example within your own domain of something in excess of 255
characters that may need to be unique e.g. URL, SQL statements' text
(removing white space, comments, etc).

NVARCHAR and NCHAR are not recognized by most people as Access
datatypes.

I really don't know what to make of that sentence Do you mean 'Jet
data types' or do you really mean something pertaining to Access that
has no direct meaning in Jet such as the 'hyperlink data type' (if
that is indeed the correct phrase)? You corrected my misspelling of
'NVARCHAR' so are you saying that some people consider that fixed
width nature of NCHAR and/or WITH COMPRESSION should be disregarded
because they are not exposed in the Access user interface? Whatever, I
think there is an issue of awareness here: distinct varying- and fixed-
width text data types exist in Jet, NVARCHAR and NCHAR are the
commonly encountered keywords in the wider SQL world, these keywords
are supported in Jet's ANSI-92 Query Mode, and the more we talk about
them the more familiar they will become in the Access world.

Jamie.

--