maximum rows limitation in an access 97 table

[GM]

we have an access97 db which has 32405 rows, is there such
a limit of 32700 rows in access 97 and if there is, can
this be rosolved by upgrading to Access 2000 or higher.

 

[Cheryl Fischer]

is there such

a limit of 32700 rows in access 97

No, the limit is that the database cannot be larger than 1GB. Check out
other specifications in Access Help, using "specifications" as a search
criterin in the Search Wizard.

 

[John Nurick]

Hi GM,

No, there is no explicit limit to the number of rows in a table in
Access 97 or in later versions. Tables with millions of rows are
possible.

The actual number of rows achievable is limited by the maximum possible
size of an .mdb file (1 GB in Access 97, 2 GB in later version) and the
other objects in the file (indexes, other tables, queries, forms, etc.).
Other changes between versions mean that even though the maximum size of
the .mdb has doubled, the maximum amount of data that can be stored in
practice has not increased much if at all.

 

[Chris Mills]

Other changes between versions mean that even though the maximum size of

the .mdb has doubled, the maximum amount of data that can be stored in
practice has not increased much if at all.

I don't entirely agree? I presume you are referring to unicode, 16-bit
storage. Unicode Compression means that table storage requirements remain much
the same. So whilst 2gB can in no way be regarded as "double", it is nearly
so, or thereabouts. The worst pessimists would say at least 50% more?

I did some trials when A2k came out. As you would expect, I can no longer find
the hard data <whew!>
Chris

 

[John Nurick]

Hi Chris,

If you've done some proper trials you're ahead of me. But I get the
impression that typical English strings with Access's Unicode
compression average about 1.4 bytes per character; on that basis alone
doubling the file size implies about 1.4x the data, though the actual
amount would depend on the ratio between compressible and
non-compressible fields. Also, I guess that indexing 16-bit characters
(compressed or otherwise) takes more space than indexing 8-bit ones.

 

[Chris Mills]

I just got a 23% test-size increase (one table, 2 text indexes noting your
warning, and english). Of course the specific size overhead will vary on
contents or objects, and you were right to point that out.

I just felt that you were exaggerating a little bit (saying there was NO
advantage in 2000/XP), and I probably "inaggerated" if there is such a word.
Certainly "double" or "nearly double" is not true as I intimated (OK, said
<g>). Nor is "none".

The other Access limitations are always interesting, as Cheryl pointed out.
Not the nbr records which has no defined limit, but the contents of a "row"
certainly have some quite severe limitations, to the extent it's surprising
it's not asked more often.

(I got around field or row limits in my main product by using mostly memo
fields, which of course have their own limitations documented in Help. I read
somewhere that if you get anywhere near the row limits you must not be using
"normalisation" properly. "you" ha-ha <gulp>)

Cheers,
Chris

P.S. I am not sure if Unicode Compression is turned on by default in a
conversion. I imported to a new database and it was not. I note it is
field-by-field and not a global setting of course.

 

[John Nurick]

I just got a 23% test-size increase (one table, 2 text indexes noting your
warning, and english). Of course the specific size overhead will vary on
contents or objects, and you were right to point that out.

I just felt that you were exaggerating a little bit (saying there was NO
advantage in 2000/XP), and I probably "inaggerated" if there is such a word.
Certainly "double" or "nearly double" is not true as I intimated (OK, said
<g>). Nor is "none".

Thanks for the info, Chris. 23% is a lot more than nothing, but it's not
all that much - for instance, if an Access 97 mdb is nudging 1 GB,
converting to a later version isn't likely to be more than a short-term
solution.

The other Access limitations are always interesting, as Cheryl pointed out.
Not the nbr records which has no defined limit, but the contents of a "row"
certainly have some quite severe limitations, to the extent it's surprising
it's not asked more often.

The first time I really thought about the effects of Unicode compression
was when someone tried to convert an A2000 mdb to A97 and got a "record
too large" or similar message.

(I got around field or row limits in my main product by using mostly memo
fields, which of course have their own limitations documented in Help. I read
somewhere that if you get anywhere near the row limits you must not be using
"normalisation" properly. "you" ha-ha <gulp>)

There's normalisation, and there are the actual capabilities of a given
RDBMS. I can imagine (but not describe) an entity that has a zillion
orthogonal attributes and so ought perhaps to go in one zillion-field
table - but to implement it in Access I'd use a taller, narrower
structure.

Cheers,