Table Row Limit Size?

[Alan Z. Scharf]

Is there a maximum number of rows advised in an Access 2003 table?

Client says table with 160,000 rows is not allowed, and an MS support
engineer agreed with him.

I've never heard of this, and in fact the database has been operating with a
table that large for several years.

If there is no such actual or recommended limit, can someone please direct
me to an official source that says that?

Thanks very much.

Alan

 

[Jeff Boyce]

Alan

If you'll open an Access .mdb file (empty or otherwise), click on Help, type
in Specification, and look at Access Specifications, you'll see "tables"
among other choices.

There's nothing in the list of table specs that mentions a
number-of-rows-limitation.

There is a length-of-row limit of 2000 characters.

I've read here in the newsgroups of tables holding more than a million rows
(and built one once for testing purposes).

Perhaps the client & MS engineer were speaking of limits for Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[fredg]

Is there a maximum number of rows advised in an Access 2003 table?

Client says table with 160,000 rows is not allowed, and an MS support
engineer agreed with him.

I've never heard of this, and in fact the database has been operating with a
table that large for several years.

If there is no such actual or recommended limit, can someone please direct
me to an official source that says that?

Thanks very much.

Alan

There is no limit on the number of records in an Access database.
There is a limit on the total size of the database, (2 Gb's), which
includes all data, forms, reports, etc.

See Access Help + Specifications + Access Specifications

Maximum
Microsoft Access database (.mdb) file size 2 gigabytes minus the space
needed for system objects

 

[Alan Z. Scharf]

Jeff and Fred,

Thanks very much for your replies.

Client says he was told by MS engineer that this was an Access "Best
Practice"

Do you know of any such Best Practices document?

Alan

 

[Alan Z. Scharf]

Jeff and Fred,

Thanks again.

Client finally conceded that there is no row limit!

Alan

 

[Jeff Boyce]

Alan

Better get back to the engineer for his/her source. While there could be
such a document, I haven't seen it.

And it seems to fly in the face of fact...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Alan

That sounds like a dangerous situation! You've caught a client out lying,
what will s/he do now?! <g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mikeinohio]

i have a database that has 1.3 milion plus record and that is just in one
table, and the actual overall database size is at 250 megabytesif that gives
you any clue of what a table can hold and FYI Excel justs holds a little over
65,000 rows,hope that answers your question

 

[Jerry Whittle]

A few weeks ago we had a contest to see who could squeeze the most rows into
an Access table. The winner had over 133 million rows! Of course that table
had just one column containing just one character per record. In the real
world I've seen Access tables with over 2 million records and perform OK.
160K records shouldn't be a problem if the tables are normalized properly.

Rule #1. Clients, bosses, and users lie.
Rule #2. When they swear that they aren't lying, see Rule #1.

 

[fredg]

Alan

That sounds like a dangerous situation! You've caught a client out lying,
what will s/he do now?! <g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Let's not make a big row out of this. it was just a little lie. <g>

 

[John Vinson]

Let's not make a big row out of this. it was just a little lie. <g>

Just hope that some pun-tificator doesn't wash your mouth out with lie
soap!

John W. Vinson[MVP]

 

[Alan Z. Scharf]

Thanks to all who answered.

Your words were helpful in enhancing the client's view of the situation!!

Alan

 

[Jeff Boyce]

That response may not be suitable for a public newsgroup, as it is
particularly caustic...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

That response may not be suitable for a public newsgroup, as it is
particularly caustic...

We'll need to get one of the acid-tongued trolls to reply to
neutralize it.


John W. Vinson[MVP]

 

[Jeff Boyce]

pHpHpH-ttt

Jeff

We'll need to get one of the acid-tongued trolls to reply to
neutralize it.


John W. Vinson[MVP]