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

[Michael J Davis]

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

 

[Michael J Davis]

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

 

[Ronald Roberts]

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

 

[Michael J Davis]

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

 

[John Nurick]

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.

 

[AL FINK]

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]

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

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

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

 

[Michael J Davis]

Thanks John.

Comments interleaved below:-

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

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