"Record too large" error in Access 2000 when exporting data

Help please!

We have been sent the results of some market research in an Access file, 
that we need to export into dBase IV structures for distribution.

Problem no 1 is that the author (who completed the work and went on 
vacation) used Access field names longer than the 10 characters suitable 
for dBase.

We have renamed the 144 fields so that we have unique names for each.
We have reduced the length of the (2) fields that were 255 characters 
long to 254 so they are compatible with dBase.

When we export to a dBase structure we get the error message "Record too 
large" - we cannot find what it is that gives rise to this message. 
Please can someone point us into the right direction?

*****

As to work arounds, we managed to get the data into Excel, and thence 
into dBase, but have lost all the logical fields in the process. Since 
over half of the fields are logical this doesn't seem helpful!

Thanks for any help.

Mike
-- 
  Michael J Davis

<><
To earn the right to complain
ensure you are lavish with your praise.
<><
0
Michael
8/15/2005 8:47:51 AM
access.conversion 3038 articles. 0 followers. Follow

7 Replies
1252 Views

Similar Articles

[PageSpeed] 15

Michael J Davis <miked@trustsof.demon.co.uk> commented
>
>Help please!
>
>When we export to a dBase structure we get the error message "Record 
>too large" - we cannot find what it is that gives rise to this message. 
>Please can someone point us into the right direction?

Following up my own query....

It seems there is a limit on the number of characters in an Access 
database (2000 or 2k).

If that is the case, how is it that Access did not complain at the data 
entry stage, rather than at the export data stage?

After all we appear to be able to view the data in the original table!

Is that likely to be the case?

Thanks

Mike
-- 
  Michael J Davis

<><
To earn the right to complain
ensure you are lavish with your praise.
<><
0
Michael
8/15/2005 9:18:07 AM
Michael J Davis wrote:
> Michael J Davis <miked@trustsof.demon.co.uk> commented
> 
>>
>> Help please!
>>
>> When we export to a dBase structure we get the error message "Record 
>> too large" - we cannot find what it is that gives rise to this 
>> message. Please can someone point us into the right direction?
> 
> 
> Following up my own query....
> 
> It seems there is a limit on the number of characters in an Access 
> database (2000 or 2k).
> 
> If that is the case, how is it that Access did not complain at the data 
> entry stage, rather than at the export data stage?
> 
> After all we appear to be able to view the data in the original table!
> 
> Is that likely to be the case?
> 
> Thanks
> 
> Mike


The number of characters (2000) does not include memo and OLE Object
fields.

Not sure what is causing your problem, I'm sure others can help with
this.

Seems to remember something about the combination of "number of fields"
and the "total size of the table" can cause a problem, but that may
have been pre A97.  Not sure.

Try using a IIF statement in a query to export your logical data to
a text field for Excel.

Answer: IIF(tbl_LogicalField=True,".T.",".F.") or whatever works,
0 and 1 or 0 and -1

Also, there may be a datatype problem with your date and or DateTime
fields.  If this is the case, you will need to use a format statement
to format the date only for your DBase tables.

txtDate: Format(tbl_DateTime,"yyyymmdd")

HTH,
Ron

0
Ronald
8/15/2005 4:07:41 PM
Ronald Roberts <rwr@robcom.com> commented
>Michael J Davis wrote:
>> Michael J Davis <miked@trustsof.demon.co.uk> commented
>>
>>>
>>> Help please!
>>>
>>> When we export to a dBase structure we get the error message "Record 
>>>too large" - we cannot find what it is that gives rise to this 
>>>message. Please can someone point us into the right direction?
>>   Following up my own query....
>>  It seems there is a limit on the number of characters in an Access 
>>database (2000 or 2k).
>>  If that is the case, how is it that Access did not complain at the 
>>data  entry stage, rather than at the export data stage?
>>  After all we appear to be able to view the data in the original 
>>table!
>>  Is that likely to be the case?
>>  Thanks
>>  Mike
>
>
>The number of characters (2000) does not include memo and OLE Object
>fields.
>
>Not sure what is causing your problem, I'm sure others can help with
>this.
>
>Seems to remember something about the combination of "number of fields"
>and the "total size of the table" can cause a problem, but that may
>have been pre A97.  Not sure.
>
>Try using a IIF statement in a query to export your logical data to
>a text field for Excel.
>
>Answer: IIF(tbl_LogicalField=True,".T.",".F.") or whatever works,
>0 and 1 or 0 and -1
>
>Also, there may be a datatype problem with your date and or DateTime
>fields.  If this is the case, you will need to use a format statement
>to format the date only for your DBase tables.
>
>txtDate: Format(tbl_DateTime,"yyyymmdd")

Ron, thanks very much!

We have solved the problem - pro tem - by exporting the original 
database in parts. (But four man-days wasted trying to find the problem 
and then writing the routines to extract the data!) It seems that part 
of the problem was that the supplier had exported logical field as 
numeric ones!

When he gets back from vacation we shall see..... }:(

This is a very worrying aspect - that Access cannot handle a table with 
more than 2000 characters - I assume (can't tell from our Google 
searches so far) that this means 'actual entries' rather than data space 
in a field. (I'm sure that we'd have noticed it before if the latter!)

We were just going to go over to Access from Foxpro, because of all the 
complications relating to data transfer to & from suppliers and 
customers - but now I'm really doubtful.

Does this problem *only* occur with importing and exporting, and not 
when the data remains within Access?

Thanks

Mike

-- 
  Michael J Davis

<><
To earn the right to complain
ensure you are lavish with your praise.
<><
0
Michael
8/15/2005 5:28:13 PM
Hi Michael,

The "number of characters" in an Access record is not the sum of the
lengths of the fields. With Text fields, Access stores only the actual
characters (e.g. a 255-character field containing five characters only
counts for about 5 of the 2000 characters). To complicate things
further, recent versions of Access store text as Unicode, but by default
apply compression - with the result that the 2000 character limit is in
practice usually rather more. Memo, hyperlink and OLE fields are
different again, and their contents don't count in the 2000-character
limit. 

dBASE's dbf files are much simpler: if you specify a 254-character text
field, 254 characters are stored regardless of how many or how few are
used, and all 254 characters count towards the size of the record. 

AFAIK the maximum record size is 4000 bytes. Is it possible that the
field lengths add up to more than this? If so, you'll need to shorten
some of them, or restructure your data. 

On Mon, 15 Aug 2005 10:18:07 +0100, Michael J Davis
<miked@trustsof.demon.co.uk> wrote:

>Michael J Davis <miked@trustsof.demon.co.uk> commented
>>
>>Help please!
>>
>>When we export to a dBase structure we get the error message "Record 
>>too large" - we cannot find what it is that gives rise to this message. 
>>Please can someone point us into the right direction?
>
>Following up my own query....
>
>It seems there is a limit on the number of characters in an Access 
>database (2000 or 2k).
>
>If that is the case, how is it that Access did not complain at the data 
>entry stage, rather than at the export data stage?
>
>After all we appear to be able to view the data in the original table!
>
>Is that likely to be the case?
>
>Thanks
>
>Mike

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
0
John
8/15/2005 7:27:56 PM
You might try look9ng at the numeric size that your database is storing, If 
I remember your database is 16 bit and access can store 32 bit numbers so if 
you try to export your data directly to your db you may get errors.
try exporting your data to a text delimited file and see if it works if so 
then try importing that file and see were your errors are located


"Michael J Davis" <miked@trustsof.demon.co.uk> wrote in message 
news:8KaWtPD3aFADFwhJ@trustsof.demon.co.uk...
>
> Help please!
>
> We have been sent the results of some market research in an Access file, 
> that we need to export into dBase IV structures for distribution.
>
> Problem no 1 is that the author (who completed the work and went on 
> vacation) used Access field names longer than the 10 characters suitable 
> for dBase.
>
> We have renamed the 144 fields so that we have unique names for each.
> We have reduced the length of the (2) fields that were 255 characters long 
> to 254 so they are compatible with dBase.
>
> When we export to a dBase structure we get the error message "Record too 
> large" - we cannot find what it is that gives rise to this message. Please 
> can someone point us into the right direction?
>
> *****
>
> As to work arounds, we managed to get the data into Excel, and thence into 
> dBase, but have lost all the logical fields in the process. Since over 
> half of the fields are logical this doesn't seem helpful!
>
> Thanks for any help.
>
> Mike
> -- 
>  Michael J Davis
>
> <><
> To earn the right to complain
> ensure you are lavish with your praise.
> <>< 


0
AL
8/16/2005 4:33:08 AM
Thanks Al,

I have just realised that our supplier managed to return all our logical 
fields as numeric -1.000000000000 or 0; that makes a lot of difference 
(in this case) and explains our problems with Excel to which I referred 
below.

Actually Access gave us the same error message when we tried to export 
to csv, as you suggest.

However, my concern now is no longer this cock up, but the ramifications 
for future business.

Thanks for your comments!

Mike

AL FINK <excalibur_software@charter.net> opined
>You might try look9ng at the numeric size that your database is storing, If
>I remember your database is 16 bit and access can store 32 bit numbers so if
>you try to export your data directly to your db you may get errors.
>try exporting your data to a text delimited file and see if it works if so
>then try importing that file and see were your errors are located
>
>
>"Michael J Davis" <miked@trustsof.demon.co.uk> wrote in message
>news:8KaWtPD3aFADFwhJ@trustsof.demon.co.uk...
>>
>> Help please!
>>
>> We have been sent the results of some market research in an Access file,
>> that we need to export into dBase IV structures for distribution.
>>
>> Problem no 1 is that the author (who completed the work and went on
>> vacation) used Access field names longer than the 10 characters suitable
>> for dBase.
>>
>> We have renamed the 144 fields so that we have unique names for each.
>> We have reduced the length of the (2) fields that were 255 characters long
>> to 254 so they are compatible with dBase.
>>
>> When we export to a dBase structure we get the error message "Record too
>> large" - we cannot find what it is that gives rise to this message. Please
>> can someone point us into the right direction?
>>
>> *****
>>
>> As to work arounds, we managed to get the data into Excel, and thence into
>> dBase, but have lost all the logical fields in the process. Since over
>> half of the fields are logical this doesn't seem helpful!
>>
>> Thanks for any help.
>>
>> Mike
>> --
>>  Michael J Davis
>>
>> <><
>> To earn the right to complain
>> ensure you are lavish with your praise.
>> <><
>
>

[The reply-to address is valid for 30 days from this posting]
-- 
Michael J Davis
http://www.trustsof.demon.co.uk
<><
For this is what the Lord has said to me,
"Go and post a Watchman and let
him report what he sees." Isa 21:6
<><
0
Michael
8/16/2005 11:37:06 AM
Thanks John.

Comments interleaved below:-

John Nurick <j.mapSoN.nurick@dial.pipex.com> opined
>Hi Michael,
>
>The "number of characters" in an Access record is not the sum of the
>lengths of the fields. With Text fields, Access stores only the actual
>characters (e.g. a 255-character field containing five characters only
>counts for about 5 of the 2000 characters). To complicate things
>further, recent versions of Access store text as Unicode, but by default
>apply compression - with the result that the 2000 character limit is in
>practice usually rather more.

Yes, I'm learning a lot. Unfortunately, we are data suppliers and have 
to bear in mind what some of our less computer-literate customers want 
to do. (Which is why we ship in dBase III/IV .dbf files usually.)

>Memo, hyperlink and OLE fields are
>different again, and their contents don't count in the 2000-character
>limit.

Yes, I understand. Again, customers who want to examine the data in 
Excel are foiled by that!
>
>dBASE's dbf files are much simpler: if you specify a 254-character text
>field, 254 characters are stored regardless of how many or how few are
>used, and all 254 characters count towards the size of the record.

Indeed.
>
>AFAIK the maximum record size is 4000 bytes. Is it possible that the
>field lengths add up to more than this? If so, you'll need to shorten
>some of them, or restructure your data.

But we've never had a problem with that. The data we have had a problem 
with has been now (correctly) imported into a dBase table with a 
Recsize()=4515 so I think it may be nearer 8k. (Quick search of VPF Help 
doesn't reveal this!)

Thanks again,

Mike

>
>On Mon, 15 Aug 2005 10:18:07 +0100, Michael J Davis
><miked@trustsof.demon.co.uk> wrote:
>
>>Michael J Davis <miked@trustsof.demon.co.uk> commented
>>>
>>>Help please!
>>>
>>>When we export to a dBase structure we get the error message "Record
>>>too large" - we cannot find what it is that gives rise to this message.
>>>Please can someone point us into the right direction?
>>
>>Following up my own query....
>>
>>It seems there is a limit on the number of characters in an Access
>>database (2000 or 2k).
>>
>>If that is the case, how is it that Access did not complain at the data
>>entry stage, rather than at the export data stage?
>>
>>After all we appear to be able to view the data in the original table!
>>
>>Is that likely to be the case?
>>
>>Thanks
>>
>>Mike
>
>--
>John Nurick [Microsoft Access MVP]
>
>Please respond in the newgroup and not by email.

[The reply-to address is valid for 30 days from this posting]
-- 
Michael J Davis
http://www.trustsof.demon.co.uk
<><
For this is what the Lord has said to me,
"Go and post a Watchman and let
him report what he sees." Isa 21:6
<><
0
Michael
8/16/2005 11:50:25 AM
Reply:

Similar Artilces:

Record too Large
Thanks for responding Doug. Your suggestion works except now it says property value too large when I get close to the end of my table. There are 150 fields and most of them are set to number. Why would I be running out of room now? Should I be limiting the number of fields in each table? Is there somewhere to get immediate help vs. posting here. Does anyone know how to help? Thanks alot! On 19 Mar 2007 21:56:50 -0700, "Lisa" <dlhenne@charter.net> wrote: >Thanks for responding Doug. Your suggestion works except now it says >property value too large when I get clo...

Record too Large Message
I have read many of the other posts on here regarding the issue I am having. I still can't seem to figure this out. I have a rather large database I am creating. I have 4 separate tables that contain the fields to certain questions that are being asked. Most of these are text fields with 250 characters set and they use a lookup from a query to populate the possible responses. These tables all link together for one patient record. As I enter information for one particular patient it seems there is too much info selected on them depending upon the size of the answer/selection. Why is ...

"Record too large" error in Access 2000 when exporting data
Help please! We have been sent the results of some market research in an Access file, that we need to export into dBase IV structures for distribution. Problem no 1 is that the author (who completed the work and went on vacation) used Access field names longer than the 10 characters suitable for dBase. We have renamed the 144 fields so that we have unique names for each. We have reduced the length of the (2) fields that were 255 characters long to 254 so they are compatible with dBase. When we export to a dBase structure we get the error message "Record too large" - we cann...