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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael J Davis - 15 Aug 2005 09:47 GMT

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

Signature

Michael J Davis

<><
To earn the right to complain
ensure you are lavish with your praise.
<><

Reply to this Message

Michael J Davis - 15 Aug 2005 10:18 GMT

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

Signature

Michael J Davis

<><
To earn the right to complain
ensure you are lavish with your praise.
<><

Reply to this Message

Ronald Roberts - 15 Aug 2005 17:07 GMT

> Michael J Davis <miked@trustsof.demon.co.uk> commented
>
[quoted text clipped - 19 lines]
>
> 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

Reply to this Message

Michael J Davis - 15 Aug 2005 18:28 GMT

Ronald Roberts <rwr@robcom.com> commented

>> Michael J Davis <miked@trustsof.demon.co.uk> commented
>>
[quoted text clipped - 35 lines]
>
>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

Signature

Michael J Davis

<><
To earn the right to complain
ensure you are lavish with your praise.
<><

Reply to this Message

John Nurick - 15 Aug 2005 20:27 GMT

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.

>Michael J Davis <miked@trustsof.demon.co.uk> commented
>>
[quoted text clipped - 19 lines]
>
>Mike

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Michael J Davis - 16 Aug 2005 12:50 GMT

Thanks John.

Comments interleaved below:-

John Nurick <j.mapSoN.nurick@dial.pipex.com> opined

>Hi Michael,
>
[quoted text clipped - 5 lines]
>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

>>Michael J Davis <miked@trustsof.demon.co.uk> commented
>>>
[quoted text clipped - 24 lines]
>
>Please respond in the newgroup and not by email.

[The reply-to address is valid for 30 days from this posting]

Signature

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
<><

Reply to this Message

AL FINK - 16 Aug 2005 04:38 GMT

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

> Help please!
>
[quoted text clipped - 22 lines]
>
> Mike

Reply to this Message

Michael J Davis - 16 Aug 2005 12:37 GMT

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
[quoted text clipped - 35 lines]
>> ensure you are lavish with your praise.
>> <><

[The reply-to address is valid for 30 days from this posting]

Signature

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
<><

Reply to this Message