"Record is too large"?

**tekctrl** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

FYI...it doesn't matter what field I've added data to or which table is
affected, I get the same err msg whenever I appear to pass some threshold. I
can't find that error msg in any of the MSAccess documentation that I have
here, or anything that addresses restrictions to a records size. I'm in FORM
view when this happens.

"tekctrl" <tekctrl@earthl ink.netwrote in message
news:GvCdnZc1_e Sf6szVnZ2dnUVZ_ vGdnZ2d@earthli nk.com...

Anyone:
>
I have a simple MSAccess DB which was created from an old ASCII flatfile.
It works fine except for something that just started happening. I'll

enter

info in a record, save the record, and try to move to another record and

get

an Access error "Record is too large". The record is only half filled,

with

many empty fields. If I remove the added data or delete some older data,
then it saves ok and works fine again. Whenever I'm getting that error, I
can't move to any other records in the DB until the record has been
'trimmed' of data. Suggestions, anyone??
>
>
Jack
aka tekctrl@earthli nk.net
>
>

**Linq Adams via AccessMonster.com** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

What version of Access are you running and how big is the MDB file?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200806/1

**Salad** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

tekctrl wrote:

Anyone:
>
I have a simple MSAccess DB which was created from an old ASCII flatfile.
It works fine except for something that just started happening. I'll enter
info in a record, save the record, and try to move to another record and get
an Access error "Record is too large". The record is only half filled, with
many empty fields. If I remove the added data or delete some older data,
then it saves ok and works fine again. Whenever I'm getting that error, I
can't move to any other records in the DB until the record has been
'trimmed' of data. Suggestions, anyone??
>
>
Jack
aka tekctrl@earthli nk.net
>
>

It might help those that help you if you provide an error number as well
as the error message. Anyway...this is what it says in help.

"You defined or imported a table with records larger than 2K. This error
occurs when you enter data into the record, not when you define the
table structure. Redefine the table by making some fields shorter,
removing unneeded fields, or moving some fields to other tables."

You might have a text field like State that's set to 50 chars. Set it
to 2. See what else you can trim down in size by modifying your table
structure.

Jamaica Days

http://www.youtube.com/watch?v=q6CqtOJraBA

**tekctrl** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

MSAccess 2002, sp3
6.6Mb

all running on a Win2K PC with 1Gb of RAM

"Linq Adams via AccessMonster.c om" <u28780@uwewrot e in message
news:8593f08eb0 070@uwe...

What version of Access are you running and how big is the MDB file?
>
--
There's ALWAYS more than one way to skin a cat!
>
Answers/posts based on Access 2000/2003
>
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200806/1

>

**Linq Adams via AccessMonster.com** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

You need to take a look at this article:

Error - Office.com - Microsoft Support

http://support.microsoft.com/kb/111304

Part of what it says is that record size is limited to approximately 2000
characters, not counting memo fields (which are not actually held in the
record.)

Sometimes, when importing data, the field size, for all fields imported,
defaults to 255 characters, which can quickly add up. Unlike Access, many
flat files use a fixed length field format. You might want to go into the
Table Design View and check out the field sizes.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200806/1

**tekctrl** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

Jamaica ... there was no err #, just the message.

Linq ... I believe you're right about the field sizes defaulting to 255 on
creation and then adding up to a LOT of wasted 'reserved' space. I went
back into the Properties and found and corrected a number that were like
that, including several single-character fields with a reserved field size
of 255. Also changed one Text field to a Memo type.

We'll see if my changes corrected the issue. Good thing I backed up the DB
first! <G>

Thank you, both! This is a very important DB and it's proper function is
critical here.

Jack

"Salad" <oil@vinegar.co mwrote in message
news:M_idnZ8Zbv r448zVnZ2dnUVZ_ oHinZ2d@earthli nk.com...

tekctrl wrote:

Anyone:

I have a simple MSAccess DB which was created from an old ASCII

flatfile.

It works fine except for something that just started happening. I'll

enter

info in a record, save the record, and try to move to another record and

get

an Access error "Record is too large". The record is only half filled,

with

many empty fields. If I remove the added data or delete some older

data,

then it saves ok and works fine again. Whenever I'm getting that error,

I

can't move to any other records in the DB until the record has been
'trimmed' of data. Suggestions, anyone??

Jack
aka tekctrl@earthli nk.net

>
It might help those that help you if you provide an error number as well
as the error message. Anyway...this is what it says in help.
>
"You defined or imported a table with records larger than 2K. This error
occurs when you enter data into the record, not when you define the
table structure. Redefine the table by making some fields shorter,
removing unneeded fields, or moving some fields to other tables."
>
You might have a text field like State that's set to 50 chars. Set it
to 2. See what else you can trim down in size by modifying your table
structure.
>
Jamaica Days
http://www.youtube.com/watch?v=q6CqtOJraBA

**tekctrl** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

Linq:

Shuckey Darn! I reviewed the article that you suggested and went back into
the Design view for each of the tables. Edited the tables to shrink the
reserved field sizes, changed some to Memo type, saved and closed. Went
back into MSAccess and still having the same issue. :( I can add data and
save without problem, but when I try to move to the next record I still get
the "record is too large" msg. No err #. So far it's only affecting a
single record, but it's the single largest record in the DB even if only
half of the fields are filled. The Text (comments) fields which contain the
most data have all been changed to Memo type, and are all in separate tables
anyway, so they shouldn't be counting against the 2Kb record size limit.
I'm open to suggestions...a nyone?

Jack

"Linq Adams via AccessMonster.c om" <u28780@uwewrot e in message
news:85940cb2d9 5ee@uwe...

You need to take a look at this article:
>

Error - Office.com - Microsoft Support

http://support.microsoft.com/kb/111304

>
Part of what it says is that record size is limited to approximately 2000
characters, not counting memo fields (which are not actually held in the
record.)
>
Sometimes, when importing data, the field size, for all fields imported,
defaults to 255 characters, which can quickly add up. Unlike Access, many
flat files use a fixed length field format. You might want to go into the
Table Design View and check out the field sizes.
>
--
There's ALWAYS more than one way to skin a cat!
>
Answers/posts based on Access 2000/2003
>
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200806/1

>

**Salad** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

tekctrl wrote:

Linq:
>
Shuckey Darn! I reviewed the article that you suggested and went back into
the Design view for each of the tables. Edited the tables to shrink the
reserved field sizes, changed some to Memo type, saved and closed. Went
back into MSAccess and still having the same issue. :( I can add data and
save without problem, but when I try to move to the next record I still get
the "record is too large" msg. No err #.

You might have an error routine in the sub to save the record. It might be
Msgbox Err.Description .

Guess what? There's an Err.Number. You can even do this
Msgbox Err.Number & "-" & Err.Description
But I told you what the err number was so you don't need to bother with
it. I even provided the info regarding that error.

single record, but it's the single largest record in the DB even if only
half of the fields are filled. The Text (comments) fields which contain the
most data have all been changed to Memo type, and are all in separate tables
anyway, so they shouldn't be counting against the 2Kb record size limit.
I'm open to suggestions...a nyone?

In help look for "Data Type Summary". This will list the storage
requirements for each data type.

Also, look for "Specifications " in help for your education.

Now, open up your table in design mode. Add up the storage space for
each field based on the data type and for Text the number of characters
you set for it. Don't add Memo or OLE Object fields. You will have
more than 2000 bytes. Fix it or continue to be stumped.

**Salad** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

Salad wrote:

tekctrl wrote:
>

>Linq:
>>
>the "record is too large" msg. No err #.

>
>

I didn't supply the error number. The error number is 3047.

**tekctrl** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

Salad:

In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text to
Memo or date/time to conserve space.

Yes, I did review the error msg article that you cited and it does appear to
be relevant. However, the DB here doesn't have the size that should trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.

Jack

"Salad" <oil@vinegar.co mwrote in message
news:G46dnVNNxf YzVMzVnZ2dnUVZ_ oHinZ2d@earthli nk.com...

tekctrl wrote:

Linq:

Shuckey Darn! I reviewed the article that you suggested and went back

into

the Design view for each of the tables. Edited the tables to shrink the
reserved field sizes, changed some to Memo type, saved and closed. Went
back into MSAccess and still having the same issue. :( I can add data

and

save without problem, but when I try to move to the next record I still

get

the "record is too large" msg. No err #.

>
You might have an error routine in the sub to save the record. It might

be

Msgbox Err.Description .
>
Guess what? There's an Err.Number. You can even do this
Msgbox Err.Number & "-" & Err.Description
But I told you what the err number was so you don't need to bother with
it. I even provided the info regarding that error.
>

single record, but it's the single largest record in the DB even if only
half of the fields are filled. The Text (comments) fields which contain

the

most data have all been changed to Memo type, and are all in separate

tables

anyway, so they shouldn't be counting against the 2Kb record size limit.
I'm open to suggestions...a nyone?

>
In help look for "Data Type Summary". This will list the storage
requirements for each data type.
>
Also, look for "Specifications " in help for your education.
>
Now, open up your table in design mode. Add up the storage space for
each field based on the data type and for Text the number of characters
you set for it. Don't add Memo or OLE Object fields. You will have
more than 2000 bytes. Fix it or continue to be stumped.
>

**Salad** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

tekctrl wrote:

Salad:
>
In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text to
Memo or date/time to conserve space.
>
Yes, I did review the error msg article that you cited and it does appear to
be relevant. However, the DB here doesn't have the size that should trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.
>
>
Jack
>

At this point I'd make a copy of your database (Fe/Be) and use it for
testing.

Regarding the recordsource of the form...is it something like
Select * From Table1

Or do you use specific field names?
Select Id, CustName, Address From Table1

If the first, try the second method.

If that doesn't work, perhaps try splitting the table. Copy Table1 to
Table2. Open up Table1 in design. Delete 1/2 of the fields from it.
Open Table2. Delete the rows (besides the key) you didn't delete in
Table1. If key is autonumber in Table2, change in to Number/Long.

Now create a query that links Table1 to Table2. Set that query as the
recordsource to the form. Does that help?

If that doesn't work...hmmm...d elete all code from the form (in the MDB
fe copy). Then start deleting fields, open, run, and by trial and error
see if there's a specific field that causes a problem. If you have code
behind the form, prior to deleting fields but after deleting the code,
see if you can use the form correctly. Maybe there's some code that's
the culprit.

**Allen Browne** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

Try creating a new table with the memo fields you need.
Then use an Append query to populate it with the data from the old table.
Don't type in really long field names or descriptions.

The page size is now actually 4k in size, so you might even get around 4k
characters in a record before it's "too wide." MS says this depends on
Unicode Compression and language settings.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tekctrl" <tekctrl@earthl ink.netwrote in message
news:d96dnT0A04 qNGs_VnZ2dnUVZ_ judnZ2d@earthli nk.com...

Salad:
>
In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text
to
Memo or date/time to conserve space.
>
Yes, I did review the error msg article that you cited and it does appear
to
be relevant. However, the DB here doesn't have the size that should
trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data
restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several
are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records
are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do
all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.

**JvC** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

I note that in one of your early messages, you mention that you are editing
using a form. Does this happen if you go in to the table and directly edit
the table, rather than going in through the form? Just a thought...

John

"tekctrl" <tekctrl@earthl ink.netwrote in message
news:d96dnT0A04 qNGs_VnZ2dnUVZ_ judnZ2d@earthli nk.com...

Salad:
>
In point of fact, the sum of all of the fields (excluding Memo and
Date/Time...there are no OLE fields) is 1559. I long ago combed thru the
different tables, reducing the reservations and changing fields from Text
to
Memo or date/time to conserve space.
>
Yes, I did review the error msg article that you cited and it does appear
to
be relevant. However, the DB here doesn't have the size that should
trigger
it. Further, I'd expect to see that err when doing a Save. It saves just
fine. However, once the edit is saved the Form refuses to advance to the
next record but displays the err instead. Removing the add'l data
restores
the DB to functionality and it works ok again. I find that it doesn't
matter where I add the add'l data in the Form or which table of several
are
edited, it behaves the same. That leads me to believe that it's something
in the Form rather than the tables. Since the individual table records
are
all Way below the 2Kb limit, and the composite size in the Form is only
1559, I remain puzzled. What record is the err msg referring to? I do
all
of my data editing in Form mode and (so far) only a single record is
affected...but it's the largest of the lot.
>
>
Jack
>
>
"Salad" <oil@vinegar.co mwrote in message
news:G46dnVNNxf YzVMzVnZ2dnUVZ_ oHinZ2d@earthli nk.com...

>tekctrl wrote:

Linq:
>
Shuckey Darn! I reviewed the article that you suggested and went back

into

the Design view for each of the tables. Edited the tables to shrink
the
reserved field sizes, changed some to Memo type, saved and closed.
Went
back into MSAccess and still having the same issue. :( I can add data

and

save without problem, but when I try to move to the next record I still

get

the "record is too large" msg. No err #.

>>
>You might have an error routine in the sub to save the record. It might

be

>Msgbox Err.Description .
>>
>Guess what? There's an Err.Number. You can even do this
>Msgbox Err.Number & "-" & Err.Description
>But I told you what the err number was so you don't need to bother with
>it. I even provided the info regarding that error.
>>

single record, but it's the single largest record in the DB even if
only
half of the fields are filled. The Text (comments) fields which
contain

the

most data have all been changed to Memo type, and are all in separate

tables

anyway, so they shouldn't be counting against the 2Kb record size
limit.
I'm open to suggestions...a nyone?

>>
>In help look for "Data Type Summary". This will list the storage
>requirements for each data type.
>>
>Also, look for "Specifications " in help for your education.
>>
>Now, open up your table in design mode. Add up the storage space for
>each field based on the data type and for Text the number of characters
>you set for it. Don't add Memo or OLE Object fields. You will have
>more than 2000 bytes. Fix it or continue to be stumped.
>>

>
>

**Linq Adams via AccessMonster.com** · Jun 27 '08, 07:26 PM

Re: "Record is too large"?

Hope it works out for you! And backing up is the absolute, single most
intelligent thing you can ever do!

Let us know how you make out!

Linq

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200806/1

"Record is too large"?

"Record is too large"?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment