"Record too large" - what??

  • Thread starter Thread starter Pendragon
  • Start date Start date
P

Pendragon

The simplest things can be the most confusing sometimes.....

Trying to run what I thought was a basic update query.

UPDATE zTempCB INNER JOIN [Chargeback Users] ON zTempCB.APPROVER_ID =
[Chargeback Users].APPROVER_ID SET [Chargeback Users].Role =
[ztempcb].[rolename];

zTempCB was a table created because there were multiple Roles for an
Approver_ID, and I only needed a Max value.

When I run this query, I get "Record Too Large." I don't understand what
this means. [Role] is a text field set to 255 characters by default. There
are no null values in [Rolename] and the max char length of [Rolename] for
any record is 35. The complete recordset of zTempCB is 170 records and the
matched recordset (changing the update query to a select query) is 184
records. Yes, there are multiple instances of Approver_ID in the destination
table and for each instance of an Approver_ID the role is updated to the same
value. No big deal, correct?

What am I missing?

Thanks in advance.
 
Update (no pun intended)

For most records I can update the field. I have come across one record in
particular where I can manually type in 18 characters, but trying to add the
19th character gives the "Record Is Too Large" error. How is it possible
that a field accepts data for most records but for at least one record in
particular, there is a limitation? The data is only alphanumeric.
 
Perhaps the limit pertains to the sum of all the fields' lengths, not that
one field?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Another update -

The only 3 records where this is occuring is where data exists in 82 of 103
fields.

Approver_ID - max 8 char; field size is 255
Approver_Name - individual's name; field size is 255
Role - Null for the moment; field size is 255
Locations - Null for the moment; field size is 255

Then there are fields named 01 through 99, each with a field size of 255 but
each field with data has no more than 35 characters. The 3 offending records
have data in 80 of the 99 fields, and then also have Approver_ID and
Approver_Name.

Is there a maximum number of fields to a table, and cumulatively a size
limit for a single record?
 
There's a limit of 255 fields in a table and 2,000 characters total in a
record (not including things like Memo fields and a few others).

Seems that you have exceeded the 2,000 character limit. You could try
changing some of the text fields to Memo data type, but there are problems
with using Memo fields like table corruption, sorting, searching for data,
exporting, and other things.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Pendragon said:
Another update -

The only 3 records where this is occuring is where data exists in 82 of 103
fields.

Approver_ID - max 8 char; field size is 255
Approver_Name - individual's name; field size is 255
Role - Null for the moment; field size is 255
Locations - Null for the moment; field size is 255

Then there are fields named 01 through 99, each with a field size of 255 but
each field with data has no more than 35 characters. The 3 offending records
have data in 80 of the 99 fields, and then also have Approver_ID and
Approver_Name.

Is there a maximum number of fields to a table, and cumulatively a size
limit for a single record?

Pendragon said:
The simplest things can be the most confusing sometimes.....

Trying to run what I thought was a basic update query.

UPDATE zTempCB INNER JOIN [Chargeback Users] ON zTempCB.APPROVER_ID =
[Chargeback Users].APPROVER_ID SET [Chargeback Users].Role =
[ztempcb].[rolename];

zTempCB was a table created because there were multiple Roles for an
Approver_ID, and I only needed a Max value.

When I run this query, I get "Record Too Large." I don't understand what
this means. [Role] is a text field set to 255 characters by default. There
are no null values in [Rolename] and the max char length of [Rolename] for
any record is 35. The complete recordset of zTempCB is 170 records and the
matched recordset (changing the update query to a select query) is 184
records. Yes, there are multiple instances of Approver_ID in the destination
table and for each instance of an Approver_ID the role is updated to the same
value. No big deal, correct?

What am I missing?

Thanks in advance.
Click to expand...
Click to expand...
 
Thanks for the info!!

Jerry Whittle said:
There's a limit of 255 fields in a table and 2,000 characters total in a
record (not including things like Memo fields and a few others).

Seems that you have exceeded the 2,000 character limit. You could try
changing some of the text fields to Memo data type, but there are problems
with using Memo fields like table corruption, sorting, searching for data,
exporting, and other things.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Pendragon said:
Another update -

The only 3 records where this is occuring is where data exists in 82 of 103
fields.

Approver_ID - max 8 char; field size is 255
Approver_Name - individual's name; field size is 255
Role - Null for the moment; field size is 255
Locations - Null for the moment; field size is 255

Then there are fields named 01 through 99, each with a field size of 255 but
each field with data has no more than 35 characters. The 3 offending records
have data in 80 of the 99 fields, and then also have Approver_ID and
Approver_Name.

Is there a maximum number of fields to a table, and cumulatively a size
limit for a single record?

Pendragon said:
The simplest things can be the most confusing sometimes.....

Trying to run what I thought was a basic update query.

UPDATE zTempCB INNER JOIN [Chargeback Users] ON zTempCB.APPROVER_ID =
[Chargeback Users].APPROVER_ID SET [Chargeback Users].Role =
[ztempcb].[rolename];

zTempCB was a table created because there were multiple Roles for an
Approver_ID, and I only needed a Max value.

When I run this query, I get "Record Too Large." I don't understand what
this means. [Role] is a text field set to 255 characters by default. There
are no null values in [Rolename] and the max char length of [Rolename] for
any record is 35. The complete recordset of zTempCB is 170 records and the
matched recordset (changing the update query to a select query) is 184
records. Yes, there are multiple instances of Approver_ID in the destination
table and for each instance of an Approver_ID the role is updated to the same
value. No big deal, correct?

What am I missing?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Pendragon said:
The simplest things can be the most confusing sometimes.....

Trying to run what I thought was a basic update query.

UPDATE zTempCB INNER JOIN [Chargeback Users] ON zTempCB.APPROVER_ID =
[Chargeback Users].APPROVER_ID SET [Chargeback Users].Role =
[ztempcb].[rolename];

zTempCB was a table created because there were multiple Roles for an
Approver_ID, and I only needed a Max value.

When I run this query, I get "Record Too Large." I don't understand
what this means. [Role] is a text field set to 255 characters by
default. There are no null values in [Rolename] and the max char
length of [Rolename] for any record is 35. The complete recordset of
zTempCB is 170 records and the matched recordset (changing the update
query to a select query) is 184 records. Yes, there are multiple
instances of Approver_ID in the destination table and for each
instance of an Approver_ID the role is updated to the same value. No
big deal, correct?

What am I missing?
Click to expand...
The number of records is not relevant ... the error said that THE record was
too large. So that seems to mean that the result of the update would create
a record that is too large. Access XP imposes a limit of 4000 characters in
a record. Earlier versions limited one to 2000 characters. This means adding
up all the characters in all the fields in that record.

How many fields are in [Chargeback Users]? What are the datatypes?

Is the Role field empty before this update? If so, that likely means at
least one record in this table is near the limit imposed by whatever version
of Access you are using, and that the update would put you over that limit.
 
All of the fields were text fields with char limits of 255. After closer
examination of all the data, I realized that the data in the fields were
consistent from field to field and record to record. So I created a
reference table of the consistent values, set a numeric primary key, and then
updated the problematic table with the numeric primary key ids in place of
the text. No problems now.

Bob Barrows said:
Pendragon said:
The simplest things can be the most confusing sometimes.....

Trying to run what I thought was a basic update query.

UPDATE zTempCB INNER JOIN [Chargeback Users] ON zTempCB.APPROVER_ID =
[Chargeback Users].APPROVER_ID SET [Chargeback Users].Role =
[ztempcb].[rolename];

zTempCB was a table created because there were multiple Roles for an
Approver_ID, and I only needed a Max value.

When I run this query, I get "Record Too Large." I don't understand
what this means. [Role] is a text field set to 255 characters by
default. There are no null values in [Rolename] and the max char
length of [Rolename] for any record is 35. The complete recordset of
zTempCB is 170 records and the matched recordset (changing the update
query to a select query) is 184 records. Yes, there are multiple
instances of Approver_ID in the destination table and for each
instance of an Approver_ID the role is updated to the same value. No
big deal, correct?

What am I missing?
Click to expand...
The number of records is not relevant ... the error said that THE record was
too large. So that seems to mean that the result of the update would create
a record that is too large. Access XP imposes a limit of 4000 characters in
a record. Earlier versions limited one to 2000 characters. This means adding
up all the characters in all the fields in that record.

How many fields are in [Chargeback Users]? What are the datatypes?

Is the Role field empty before this update? If so, that likely means at
least one record in this table is near the limit imposed by whatever version
of Access you are using, and that the update would put you over that limit.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.
Click to expand...
 
Back
Top