Converting to "prior version" from Access 2000

[Laurel]

I have an Access 2000 database that contains only tables (no forms, reports,
code, etc.) When I try to convert it to the prior version, I get "Record
Size too Large." There is no other information telling me which table, and
even if I knew the table, I wouldn't know what the size problem was. Can
someone help here?

I found
"About converting an Access 2000 database to Access 97"
in HELP, but it didn't seem to address the problem.

Also, how do I find out what the recordsize of a table is? One of my
tables has a number of memo fields. Does that make the record size
variable?

 

[Alastair MacFarlane]

Laurel

It sounds like you have a table with so many fields and
that the default size for the text fields is set at the
maximum number of characters. Each record has a a maximum
size that it can store and you have reached its maximum.

I have never found a way to identify the table concerned.
It need not necessarily be the table with the largest
amount of data that is presenting this error message.

I hope this helps.

Alastair MacFarlane

 

[John Nurick]

Hi Laurel,

One possible cause results from Access 2000 and later being
Unicode-compatible and Access 97 not. The specifications for both show
that the maximum size of a record is "2,000 characters" excluding the
contents of memo and OLE fields, but storing Unicode text potentially
requires two bytes per character. Hence the maximum size of an Access
2000 record is approximately 4,000 bytes compared with 2,000 bytes in
Access 97.

But most Unicode text - at least if it's in western languages - can be
compressed to less than two bytes per character, and Access 2000 and
later have a "Unicode compression" option in table design. The result is
that if a table consists mostly of text fields containing text in
western languages, in Access 2000 and later it's possible in practice to
fit more than 2000 characters into a record, while in Access 97 you
can't.

So the first thing to do is look for tables with several large text
fields, or tables with many fields including some text fields. You can
use a query like this (where f1, f2 f3... are text fields)
SELECT ID, Len([f1])+Len([f2])+Len([f3] AS L
FROM tblT
ORDER BY Len([f1])+Len([f2])+Len([f3] AS L;
to identify the records with the longest text content. (Number, yes/no
and date fields count towards the 2,000 character total too, but are
only a few bytes each).

If you find records that look as if they might contain more than 2,000
characters, one way to shorten them is to convert one or more text
fields into memo fields. Another is to normalise the table into two or
more narrower ones.


to tot up the lengths of the contents

 

[Laurel]

Thanks to both, but especially for this clever method of finding the actual
width of a record. You'd think you would have to go to such lenghts...

But it's great to be reassured that I'm not missing some simple thing.

Thanks again.

Hi Laurel,

One possible cause results from Access 2000 and later being
Unicode-compatible and Access 97 not. The specifications for both show
that the maximum size of a record is "2,000 characters" excluding the
contents of memo and OLE fields, but storing Unicode text potentially
requires two bytes per character. Hence the maximum size of an Access
2000 record is approximately 4,000 bytes compared with 2,000 bytes in
Access 97.

But most Unicode text - at least if it's in western languages - can be
compressed to less than two bytes per character, and Access 2000 and
later have a "Unicode compression" option in table design. The result is
that if a table consists mostly of text fields containing text in
western languages, in Access 2000 and later it's possible in practice to
fit more than 2000 characters into a record, while in Access 97 you
can't.

So the first thing to do is look for tables with several large text
fields, or tables with many fields including some text fields. You can
use a query like this (where f1, f2 f3... are text fields)
SELECT ID, Len([f1])+Len([f2])+Len([f3] AS L
FROM tblT
ORDER BY Len([f1])+Len([f2])+Len([f3] AS L;
to identify the records with the longest text content. (Number, yes/no
and date fields count towards the 2,000 character total too, but are
only a few bytes each).

If you find records that look as if they might contain more than 2,000
characters, one way to shorten them is to convert one or more text
fields into memo fields. Another is to normalise the table into two or
more narrower ones.


to tot up the lengths of the contents

I have an Access 2000 database that contains only tables (no forms, reports,
code, etc.) When I try to convert it to the prior version, I get "Record
Size too Large." There is no other information telling me which table, and
even if I knew the table, I wouldn't know what the size problem was. Can
someone help here?

I found
"About converting an Access 2000 database to Access 97"
in HELP, but it didn't seem to address the problem.

Also, how do I find out what the recordsize of a table is? One of my
tables has a number of memo fields. Does that make the record size
variable?