Record is Too Large error message

[Guest]

The table is 100 column and I believe the spec for Access2003 allows 255

I am now getting the error message "Record Is Too Large"

What other parameter should I review to resolve this?

 

[Joseph Meehan]

The table is 100 column and I believe the spec for Access2003 allows
255

I am now getting the error message "Record Is Too Large"

What other parameter should I review to resolve this?

Individual fields also have size restrictions.

What is the exact wording of the message? What version of Access are
your using? How many records do you have. How large are all those fields?

Is there a really good reason for having 100 fields? 99% of the time
when someone has 100 fields it means their table design is not normalized
because they are using it like a spread sheet (note you did refer to
columns, a spreadsheet term, not fields, the correct database term.)

I don't believe it is the answer to your question, but it still likely
should be corrected or you will be back with more questions.

 

[Guest]

One more thing, try and do a Compact & Repair on the database. If you have
had a field then deleted it Access is still allocating the space until you do
a Compact & Repair.

 

[John Vinson]

The table is 100 column and I believe the spec for Access2003 allows 255

I am now getting the error message "Record Is Too Large"

What other parameter should I review to resolve this?

There's a hard limit of 2000 (or perhaps 2048, I'm not certain) bytes
ACTUALLY USED in a table. If you have 100 text fields, each 255 bytes,
Access will cheerfully let you create the table - but you'll get this
error when you first fill up more than 2000 characters across the
record.

Memo fields do not count toward this limit, but other than that
there's no getaround.

As Joseph suggests, your best bet is probably going to be to normalize
your table design. 100 fields is ENORMOUSLY wide; I've needed as many
as 60 fields, and I've seen a few wider tables than that - but I
strongly suspect that you're "committing spreadsheet", a venial sin
punishible by being required to re-read Codd and Date. Do you have
fields with names like Contact1, Contact2, Contact3? or Jan, Feb, Mar?
If so - you're embedding a one to many relationship into each record,
instead of into a new related table.


John W. Vinson[MVP]

 

[Guest]

thanx both....

The MDB compacts on close; have done repair too just for fun. The overall
MDB property size is just 5M.

Off the 100 fields of this "Master"table; 60 are text fields sized limited
to 125; the rest are dates, integers, decimals or text set at 50 and there is
one memo field

As to the data normalization (or lack thereof); indeed it is an unusually
large table in terms of columns/fields - so I understand the accusation... it
could be improved in this regard it is true.

But nonetheless - I don't think I'm up against or near the max and so am
still perplexed.

I have done housecleaning and deleted trial queries, tables, etc that were
still hanging around....

I hit the 'record too large' error when filling all data in the form that
populates the MasterTable. In experimentation I found; as part of final
debug that if I populate every field of the form - which is sourced on the
Mastertable - at some point I get the error message: "Record is Too Large"
and I can not leave that text box I am in.

It is very low probability that every field of this form would ever be
used...but as part of my debug I go thru and populate everything to check
misc layout and such - - - and this is when I encountered this.

The form's 60 text boxes that align to the MasterTables 60 fields (which are
set at 125 size) are populated by the form pulldowns sourced off other static
reference tables that have up to 10,000 records. But these reference tables
have max record text size of 100. So overall I don't think I am seeing a
field size mismatch.....

 

[Tony Toews]

punishible by being required to re-read Codd and Date

Aaaarrrghhhhhh.

Tony <running screaming from the battlefield.)
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Pieter Wijnen]

Well then, you need to normalize I'm afraid
I don't remember the limits (has changed over the years), but 2k is a fair
guess as that is the "page" size (data allocation block size) for msaccess.
Memo & Blobs can be bigger offcourse, but they are referenced through
pointers
You might get away with it as a temporary solution by splitting your table
in two (one-to-one) , renaming to "table1" & "table2" , and combining them
under the "old" name, 'till you get around to fixing the design flaws

HTH

Pieter

 

[John Vinson]

I hit the 'record too large' error when filling all data in the form that
populates the MasterTable. In experimentation I found; as part of final
debug that if I populate every field of the form - which is sourced on the
Mastertable - at some point I get the error message: "Record is Too Large"
and I can not leave that text box I am in.

It is very low probability that every field of this form would ever be
used...but as part of my debug I go thru and populate everything to check
misc layout and such - - - and this is when I encountered this.

From the Specifications topic in the Help file:

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

If you have sixty 125-byte fields... you *can't* fill them; you can't
fill more than sixteen of them, in fact. If you want to try this, fill
20 fields with 100 characters each. You'll get the error when you add
any other data to the record.

Solution: normalize. Or move the data to SQL/Server (and don't try to
select all the fields in a Query because the limit applies there too!)

John W. Vinson[MVP]

 

[Allen Browne]

John's explanation is perfect, of course: 60 fields of 125 characters each =
a record 7500 characters wide if all fields are filled. There is no way
Access will handle that.

But I'm interested if anyone knows how to calculate the actual limits.

It used to be around 2K, but became a bit more complex in Access 2000 when
MS introduced Unicode Compression. The A2003 help file suggests the limit is
2K or 4K depending on Unicode Compression. But when I test it (using JET
tables), it seems to accept 4K regardless of the Unicode Compression
settings for the fields.

So, am I wrong?
Is help wrong?
Has MS expanded the capability in the JET service packs?
Does the Unicode Compression setting help relieve the record-width issue or
not?

Anyone else tested this stuff?

My interest is that the "Record too wide" problem is one of the issues we
test for in the Database Issue Checker utility at:
http://allenbrowne.com/AppIssueChecker.html
(Currently the utility only flags tables that have records wider than 4K.)

Thanks.

 

[James A. Fortune]

John's explanation is perfect, of course: 60 fields of 125 characters each =
a record 7500 characters wide if all fields are filled. There is no way
Access will handle that.

But I'm interested if anyone knows how to calculate the actual limits.

It used to be around 2K, but became a bit more complex in Access 2000 when
MS introduced Unicode Compression. The A2003 help file suggests the limit is
2K or 4K depending on Unicode Compression. But when I test it (using JET
tables), it seems to accept 4K regardless of the Unicode Compression
settings for the fields.

So, am I wrong?
Is help wrong?
Has MS expanded the capability in the JET service packs?
Does the Unicode Compression setting help relieve the record-width issue or
not?

Anyone else tested this stuff?

My interest is that the "Record too wide" problem is one of the issues we
test for in the Database Issue Checker utility at:
http://allenbrowne.com/AppIssueChecker.html
(Currently the utility only flags tables that have records wider than 4K.)

Thanks.

Allen,

I can't answer your questions. But, I'm posting anyway .

Albert Kallal posted the following in CDMA:

http://groups.google.com/group/comp.databases.ms-access/msg/ee830a006b9fb1d3

Perhaps it will add a new element (no pun intended) into the discussion.

James A. Fortune
(e-mail address removed)

 

[Guest]

thanks all;

A data normalization failure is surely the solution here. Although
Imagination only - - I can fore see a "thing" in the real world to have
60-100 separate parameters that would be correctly designed into a single
table. And thus one then must be very frugal with the field size when this
is the case or chop the table into a hierarchy of some sort.....

My only minor point of debate with MS is the wisdom of their info stating
table size max is 255 fields; which I'm fairly sure I saw on the web for
Access2003 specs area. While not incorrect it suffers from ommission as
y'all have educated me that it actually is a balancing act of field quantity
and field size. It is not unreasonable for the casual user to presume that
what is implied is the max field size(255) X the max field quantity (255) is
fully supported by the product.....and thus this dialog...

 

[Tony Toews]

The form's 60 text boxes that align to the MasterTables 60 fields (which are
set at 125 size) are populated by the form pulldowns sourced off other static
reference tables that have up to 10,000 records. But these reference tables
have max record text size of 100. So overall I don't think I am seeing a
field size mismatch.....

Ah, so maybe you can change those static reference tables to use an
autonumber primary key. Then use that field as the foreign key in
your table. Thus reducing 125 bytes each down to four bytes each.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[John Vinson]

My only minor point of debate with MS is the wisdom of their info stating
table size max is 255 fields; which I'm fairly sure I saw on the web for
Access2003 specs area. While not incorrect it suffers from ommission as
y'all have educated me that it actually is a balancing act of field quantity
and field size. It is not unreasonable for the casual user to presume that
what is implied is the max field size(255) X the max field quantity (255) is
fully supported by the product.....and thus this dialog...

A very good point - and the annoyance with Microsoft is doubled,
because as the program now works, it can lay a very nasty trap for the
developer. You can create such a 255x255 table with no errors, no
warnings, no apparent problem; it's only down the line when a
loquacious user actually USES more than the usual amount of space that
they get a (possibly quite confusing!) error message.

I really fault Microsoft for this one!

John W. Vinson[MVP]