"Record Too Large" Error

[Jenn]

I am working on an access database. I am receiving a “record to large†error
on a table, which contains 970 records. This error is affecting how that
information is pulled into the reports and forms. It is not allowing me to
change the data in the fields or rows due to the error. I have set the fields
to limit the actual amount of
data or spaces being pulled into those certain fields. This database is
three years old. The first year we had over 1500 rows and we did not recieve
the error.

How do I correct my table so that I do not get the error? Are there a
maximum number of rows a table can contain? Any assitance would be great.
Please advise thank you.

 

[John W. Vinson]

I am working on an access database. I am receiving a “record to large” error
on a table, which contains 970 records. This error is affecting how that
information is pulled into the reports and forms. It is not allowing me to
change the data in the fields or rows due to the error. I have set the fields
to limit the actual amount of
data or spaces being pulled into those certain fields. This database is
three years old. The first year we had over 1500 rows and we did not recieve
the error.

How do I correct my table so that I do not get the error? Are there a
maximum number of rows a table can contain? Any assitance would be great.
Please advise thank you.

The error has nothing to do with the number of *records* in your table, but
rather with the *size* of an individual record. You're limited to 2000 bytes
in any one record (exclusive of Memo and OLE fields, which can be huge).

What's the structure of your table? Do you have a lot of text fields? How many
fields in all? Perhaps you could post a few examples of the fieldnames,
datatypes, sizes, and (if it's not confidential) sample data.

 

[Douglas J. Steele]

The error has nothing to do with the number of *records* in your table,
but
rather with the *size* of an individual record. You're limited to 2000
bytes
in any one record (exclusive of Memo and OLE fields, which can be huge).

What's the structure of your table? Do you have a lot of text fields? How
many
fields in all? Perhaps you could post a few examples of the fieldnames,
datatypes, sizes, and (if it's not confidential) sample data.

Just to tag along on John's posting, Text fields in Access only take as much
space as is required for their content. In other words, you may have defined
a field as being Text 255, but if it contains "Something", only 9 bytes will
actually be used. That means it's possible to have, say, 10 fields each
defined as Text 255 and not have any issue until the contents of those 10
fields exceeds 2000 bytes.

 

[Jenn]

Thank you both for your assistance. I will have to look at the total number
of bytes I have for the table. I would say there are more than 30 fields or
so and i'm pretty sure it's over the 2000 limit. I have limited the size of
text in each of the fields. I may have to go about creating this table a
different way. Thanks again for everything.

 

[John W. Vinson]

Thank you both for your assistance. I will have to look at the total number
of bytes I have for the table. I would say there are more than 30 fields or
so and i'm pretty sure it's over the 2000 limit. I have limited the size of
text in each of the fields. I may have to go about creating this table a
different way. Thanks again for everything.

Check carefully to be sure you don't have some (perhaps non-obvious) one to
many relationships embedded in each record: repeating groups of fields (a dead
giveaway is fieldnames like Widget1, Widget2, Widget3 or January, March,
February, but the same issue can be much subtler). 30 fields is a pretty wide
table.

Also check to see if some of these text fields have repeating values in
multiple records - perhaps you could have a lookup table and store a 4-byte ID
in your big table rather than a 40 byte text string.

 

[David W. Fenton]

30 fields is a pretty wide table.

It is?

I'd consider 80 fields a wide table, but 30, not so much. I'd
certainly not expect a 30-field table to be bumping up against the
record-length limitation, unless, of course, there was a design
error (as you suggest).

 

[John W. Vinson]

It is?

I'd consider 80 fields a wide table, but 30, not so much. I'd
certainly not expect a 30-field table to be bumping up against the
record-length limitation, unless, of course, there was a design
error (as you suggest).

Well, a bit wide - not hugely so.

Of course 30 x 255 is way above the limit. It sounds like the OP has a number
of biggish text fields. I'd be interested to see the table design.

 

[Jenn]

Thank you all for your help. After looking at my database in more detail, now
that I have it in front of me, I have approximately 245 fields. What we are
doing is taking the old line (original line) and copying the information to
the new line and the changing that data with the current information within
the same table. We were told that the old information and the new
information has to be included within the same table.

I am unable to provide you with the actual table from the database as it is
sensitive information. I work for the State of Missouri. However I do have
permission to send you a copy of the structure if needed.

I have gone into the design view of the table and all of the fields, for the
old line of coding, have had their field size’s limited to the actual number
of bytes that will be used in that field. The total on the original “oldâ€
line of coding is 1,212 bytes. When I go to even change the field size on
the “New†Fields I receive the “Record Too Largeâ€. So essentially it is not
even letting me limit the number of bytes in those fields. If I were able to
limit the size I would be under the 2000 bytes max that access will allow.
Because there are not the same number of fields for the “new†line of coding
as there are for the “old†line of coding. I am unsure as to why it is not
allowing me to change the number of bytes in the fields of the new line of
coding.

Any suggestions would be great. I have a feeling I’m going to find a way to
recreate this table.

 

[John W. Vinson]

Thank you all for your help. After looking at my database in more detail, now
that I have it in front of me, I have approximately 245 fields.

Ummm... yes. A bit more than 30...

What we are
doing is taking the old line (original line) and copying the information to
the new line and the changing that data with the current information within
the same table. We were told that the old information and the new
information has to be included within the same table.

So it sounds like you have indeed a one (item) to many (lines) relationship
embedded within each record.

If you were told that the old and the new information have to be included in
the same TABLE that was almost certainly wrong (from a database design
perspective). If you were told that the old and the new information must be
stored in the same *record* then that is even worse!

I am unable to provide you with the actual table from the database as it is
sensitive information. I work for the State of Missouri. However I do have
permission to send you a copy of the structure if needed.

That probably won't be necessary, let's try to solve this here.

I have gone into the design view of the table and all of the fields, for the
old line of coding, have had their field size’s limited to the actual number
of bytes that will be used in that field. The total on the original “old”
line of coding is 1,212 bytes. When I go to even change the field size on
the “New” Fields I receive the “Record Too Large”. So essentially it is not
even letting me limit the number of bytes in those fields. If I were able to
limit the size I would be under the 2000 bytes max that access will allow.
Because there are not the same number of fields for the “new” line of coding
as there are for the “old” line of coding. I am unsure as to why it is not
allowing me to change the number of bytes in the fields of the new line of
coding.

Any suggestions would be great. I have a feeling I’m going to find a way to
recreate this table.

First off... explain what a "line" is and why you seem to be adding new fields
all the time. Changing the structure of your table when the data content
changes is NEVER a good idea. What's the reason for the insistance that the
old and new "lines" must be kept in the same table, or even in the same
record?

Access *is a relational database*. Its entire rationale is predicated on the
relational assumption that you will keep data in multiple tables, and use
Queries to link that data together. A bureaucratic insistance that "the data
must be in the same table" simply shows that the bureaucrat doesn't understand
how databases work!

 

[Jenn]

The information for this table (old information) is being uploaded from the
state’s financial system. We are taking that old information and updating it
on a yearly basis to the new information. The new and the old have one field
in common and that is a code that we give for each of our employees, such as
a ssn.

I’m calling it a new line and an old line because the new information and
the old information is contained within the same record. The reason it is set
up this way is because this information feeds into a form. In the form the
new information (line) is being changed the old information (line) is not.
We take this information, after everyone has changed/updated their
information, and put the table into a new database. The information is put
into a new form, which looks exactly like the form in our database. This new
database is sent to a different state division. The information is then used
to update the information in our state financial system. The other state
agency changes it to what our new information (line) is.

The information for this table is pulled out of the state financial system
on a yearly basis. This table is not a historical table so it is okay for the
information to change that is contained within it, of course only for the new
information contained on the records.

I tried to create two separate tables one with the old information and one
with the new information, and when I went to merge them together into one
table. Tied by the one common field (ssn) I still got the “record too largeâ€
error. Again when I tried to update the bytes in the fields I received the
same error. I just find it odd that this hasn’t happened the past couple of
years (so I’ve been told) and now I am receiving the errors. I’m not sure if
something is askew with the financial data we are pulling in from the state
system or not.

I’ll play around with the database as see what I can do. Any ideas or
suggestions would be great. Once again thank you for all of the advice you
have definitely steered me in the right direction. I’ll keep on trying to
troubleshoot this issue, worse comes to worse I’ll just recreate the table.

 

[David W. Fenton]

Well, a bit wide - not hugely so.

Of course 30 x 255 is way above the limit. It sounds like the OP
has a number of biggish text fields. I'd be interested to see the
table design.

If there are enough 255-character fields populated with data to
exceed the record limit, I'd call it a design error, even if,
technically, it's not a denormalized structure.

I'd have to see the specifics to know, but quite honestly, I've
never had that many 255-character fields in any table I've ever
created, and have never encountered the record-too-long error.

 

[John W. Vinson]

The information for this table (old information) is being uploaded from the
state’s financial system. We are taking that old information and updating it
on a yearly basis to the new information. The new and the old have one field
in common and that is a code that we give for each of our employees, such as
a ssn.

I’m calling it a new line and an old line because the new information and
the old information is contained within the same record. The reason it is set
up this way is because this information feeds into a form. In the form the
new information (line) is being changed the old information (line) is not.
We take this information, after everyone has changed/updated their
information, and put the table into a new database. The information is put
into a new form, which looks exactly like the form in our database. This new
database is sent to a different state division. The information is then used
to update the information in our state financial system. The other state
agency changes it to what our new information (line) is.

Ok. That's fine. If you are assuming, however, that you must have all the
fields in one record in order to see them together on a form your assumption
is simply WRONG.

Don't confuse data STORAGE - in normalized tables - with data PRESENTATION -
forms or reports! These are *different tasks* with different requirements and
implementation. It's perfectly routine to have data from multiple tables
displayed on one form, or printed on one report - that's what Access is
*designed to do*.

The information for this table is pulled out of the state financial system
on a yearly basis. This table is not a historical table so it is okay for the
information to change that is contained within it, of course only for the new
information contained on the records.

Not sure I understand...

I tried to create two separate tables one with the old information and one
with the new information, and when I went to merge them together into one
table. Tied by the one common field (ssn) I still got the “record too large”
error. Again when I tried to update the bytes in the fields I received the
same error. I just find it odd that this hasn’t happened the past couple of
years (so I’ve been told) and now I am receiving the errors. I’m not sure if
something is askew with the financial data we are pulling in from the state
system or not.

You may have just kept growing the size of the record until it can't be made
to fit, especially if the number of fields in the database has grown every
year.

I’ll play around with the database as see what I can do. Any ideas or
suggestions would be great. Once again thank you for all of the advice you
have definitely steered me in the right direction. I’ll keep on trying to
troubleshoot this issue, worse comes to worse I’ll just recreate the table.

I'm curious enough that if you'ld like, I'd be willing to have you send me the
table. To do so, create a new empty .mdb file, and import the table into it,
using the "design mode only" option to import just the structure with no data.
Compact the database, Zip it, and email it to me at jvinson <at> wysard of
info <dot> com (edit out the blanks and fix the punctuation). It might help if
you also sent a description of the nature of the fields, if that isn't obvious
from the fieldnames - I may not know all of the acronyms used in Missouri
state offices!

I could comment here or by email, whichever you prefer.

 

[John W. Vinson]

If there are enough 255-character fields populated with data to
exceed the record limit, I'd call it a design error, even if,
technically, it's not a denormalized structure.

well... true.

I'd have to see the specifics to know, but quite honestly, I've
never had that many 255-character fields in any table I've ever
created, and have never encountered the record-too-long error.

Further down the thread, Jenn indicates that there are 230+ fields, so it's
pushing hard against the 255 field limit AS WELL AS the 2000 byte limit.

 

[Jenn]

I was able to come up with a temporary fix. I exported the table into excel.
Copy and Paste the Old information into the new information and import it
back into the database. Everything is working fine so far.

I'm going to play around with it more tomorrow and see if I can create two
tables in access. There are some disign issue's that i've seen that I may be
able to tweak and get it to work.

I thank everyone for there assistance and advice. You forced me to look
outside of the box..... It is much appreciated.

 

[James A. Fortune]

If there are enough 255-character fields populated with data to
exceed the record limit, I'd call it a design error, even if,
technically, it's not a denormalized structure.

Two of the main tables I work with on a day to day basis have a lot of
fields. They are for tracking quotes and jobs. The job costing table
has about 109 essentially unrelated fields that are tracked for each
job. So far, I have been able to avoid using one-to-one relationships
with additional tables by carefully managing how many characters are
allowed into the text fields (about 45 fields). Right now the character
limits are only enforced at the form level (the only place users are
allowed to enter data), but eventually I will enforce the limits with
table constraints in addition to the form constraints just to be safe.

I didn't know originally that the main growth of the tables joined to
those tables would be through division of departments into more
departments, then into tracking the subdepartments under those
departments. Naturally both the quotes and jobs need to have the same
departments and subdepartments so that proper feedback concerning
profitability can be obtained for each subdepartment relative to the
original quote. Even time tickets are broken down by job and subdepartment.

Although seemingly innocuous at first, tying the profitability of
individual jobs to the people managing them and working on them has
completely revolutionized the criteria used for performance evaluations.
Also, the people who prepare new quotes can evaluate the performance
of similar quotes to see if their subdepartment estimates were accurate.

My point is that it is possible to have quite a few fields in a
normalized table and that the record level byte limit can still be
managed. I recognize that keeping the number of fields in a table to a
minimum is desirable for several reasons, including the record level
byte limit. Given the number of badly designed schemata out there I
think that the rule-of-thumb warnings about too many fields are
necessary, but they are not all encompassing.

Some of the reasons for the record level byte limit were discussed here:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/6566e7c66ff4f4c1

James A. Fortune
(e-mail address removed)

It's possible that the movie "Pearl Harbor," which came out in May of
2001, was partly to blame for 9/11. Besides showing examples of
kamikaze attacks, the movie ended with a patriotic statement about the
U.S. growing stronger and the enemy growing weaker. Naturally, that
statement, taken the opposite way, may have given encouragement to
someone watching the movie and thinking about carrying out such a plan.