"Record is too large" on append query attempt. Access 97

[Guest]

I'm attempting to run an append query to load a table with data, but I get
the message "Record is too large" if the query holds more than a couple of
records.
There are 23 fields in the query.
One field holds a lot of data (typically 20-24k) which is appended to a memo
field in the target table.
If I delete that field from the query, there is no error message.
The target table has 93 fields.
I'm using Access 97.
Any suggestions please?

 

[John W. Vinson]

I'm attempting to run an append query to load a table with data, but I get
the message "Record is too large" if the query holds more than a couple of
records.
There are 23 fields in the query.
One field holds a lot of data (typically 20-24k) which is appended to a memo
field in the target table.
If I delete that field from the query, there is no error message.
The target table has 93 fields.
I'm using Access 97.
Any suggestions please?

This error arises when a single record contains more than 2000 bytes,
*exclusive* of memo fields which don't count toward the total. For example, if
your target table has 93 Text fields, you can happily add thousands of
records... until you add one with 50 bytes in each field! I *believe* that a
memo field (regardless of contents) uses 16 bytes of this 2000-byte total, so
it may be that it's pushing the total over the limit.

93 fields is an *enormously* wide table; properly normalized tables rarely
exceed 30 or so fields. Could this "spreadsheet" perhaps be replaced by two
or more tables in a one to many relationship? What are some typical fields?

John W. Vinson [MVP]

 

[Guest]

John, thanks for your input, but it MUST be something to do with the size of
the one field I have containing lots of data.
Why? Because if I delete that field from the append query, it works, but if
I delete any other memo field instead, it fails.
(In this instance the offending field is 22,891 bytes)
Any suggestions anyone?

 

[Guest]

Just to add weight to that-
I tried appending that one field into a table with just one memo field.
Same result - field too large.
I'm going to start another thread to ask max size of memo field

 

[John W. Vinson]

Just to add weight to that-
I tried appending that one field into a table with just one memo field.
Same result - field too large.
I'm going to start another thread to ask max size of memo field

A memo can contain 2 billion bytes. <g>

I wonder if you're copying FROM a memo field into a Text field?

John W. Vinson [MVP]